PHP MySQL - INSERT INTO

Php-mysql Course

After a database and its table(s) have been created, you can start adding data in them.
Some syntax rules:

• In PHP, the SQL query must be quoted.
• String values, inside the SQL query, must always be quoted.
• Numeric values shouldn't be quoted.
• The word NULL must not be quoted.

Insert data into a MySQL table

To add new records to a MySQL table, use the INSERT INTO statement.
There are two ways that an INSERT query can be written:
1) The first method doesn't specify the column names where the data will be inserted, only their values.
  - Syntax:

INSERT INTO table_name
VALUES (value1, value2, value3, ...)

In this method, you must specify a value, even if it's NULL, for every column. If there are five columns in the table, you must list five values.

2) The second form specifies both the column names and the values to be inserted:
  - Syntax:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

This method is generally preferable, you can add data only in the columns that matter. Any columns not given a value will be treated as NULL (or given a default value, if one was defined).
Note that if a column cannot have a NULL value (it was defined as NOT NULL) and does not have a default value, not specifying a value will cause an error.
If a column is AUTO_INCREMENT or TIMESTAMP, it's no need to be specified in the SQL query becouse MySQL will automatically sets and adds the value.
To get PHP to execute the SQL statements, you must use the mysqli query() method.

- The following example adds one new record to the "users" table. This example uses the "users" table created in the previous lesson, with five columns (id , name , pass , email and reg_date).
The "id" field is AUTO_INCREMENT, so it's no need to be specified in the SQL query becouse MySQL will automatically adds the value for it. The same for "reg_date", which is set TIMESTAMP.

<?php
// connect to the "tests" database
$conn = new mysqli('localhost', 'root', 'pass', 'tests');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// sql query for INSERT INTO users
$sql = "INSERT INTO `users` (`name`, `pass`, `email`)
VALUES ('Marius', 'faith', 'name@domain.net')"; 

// Performs the $sql query on the server to insert the values
if ($conn->query($sql) === TRUE) {
  echo 'users entry saved successfully';
}
else {
 echo 'Error: '. $conn->error;
}

$conn->close();

?>

- First, we create the mysqli object with the variable identifier $conn. Next, we build the SQL command string and save it to a variable called $sql. Then we call the query() method, and at the same time check its return value to determine if it was successful (true).
The example above adds a row into the "users" table, with value "Marius" in the "name" column, value "faith" in the "pass" field, and "name@domain.net" for "email". The values for the others columns ("id" and "reg_date") are automatically added by MySQL server.

MySQL also allows you to insert multiple rows at one time, separating each record by a comma.
  - Syntax:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (valueA1, valueA2, valueA3,...),
(valueB1, valueB2, valueB3,...),
(valueC1, valueC2, valueC3,...)

The following example adds two new rows to the "users" table, with one query.

<?php
// connect to the "tests" database
$conn = new mysqli('localhost', 'root', '', 'tests');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// sql query for INSERT INTO users (two rows)
$sql = "INSERT INTO `users` (`name`, `pass`, `email`)
 VALUES ('MarPlo', 'peace', 'user@domain.net'),
 ('I_AM', 'love', 'address@domain.net')"; 

// Performs the $sql query on the server to insert the values
if ($conn->query($sql) === TRUE) {
  echo 'users entry saved successfully';
}
else {
 echo 'Error: '. $conn->error;
}

$conn->close();
?>

Get the auto ID generated by INSERT

If you have a table with an AUTO_INCREMENT column that stores a numeric id, you can use the insert_id() method of the mysqli object to get the ID generated in the last INSERT operation.
  - Example:

<?php
// connect to the "tests" database
$conn = new mysqli('localhost', 'root', 'pass', 'tests');

// check connection
if (mysqli_connect_errno()) {
  exit('Connect failed: '. mysqli_connect_error());
}

// sql query for INSERT INTO users
$sql = "INSERT INTO `users` (`name`, `pass`, `email`)
 VALUES ('PloMar ', 'love_light', 'a_name@domain.net')"; 

// Performs the $sql query and get the auto ID
if ($conn->query($sql) === TRUE) {
  echo 'The auto ID is: '. $conn->insert_id;
}
else {
 echo 'Error: '. $conn->error;
}

$conn->close();
?>

Output:

If the table does't have a column with the AUTO_INCREMENT attribute or if the query wasn't an INSERT or UPDATE statement, the insert_id() will return zero.

Insert data from a form into a database

HTML forms are very often used to send data to a server script. These data ca also be added into a database.
To insert data from a form into a MySQL database we need a HTML form and a PHP script that receives data from form.

Let's create an HTML form and a PHP script that can be used to add new records to the "users" table.

The HTML form

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
 <title>HTML form for insert users</title>
</head>
<body>

<form action="insert.php" method="post">
 Name: <input type="text" name="name" /><br />
 Password: <input type="text" name="pass" /><br />
 E-mail: <input type="text" name="email" /><br />
 <input type="submit" value="Send" />
</form>

</body>
</html>

When a user clicks on the "Send" button, the form data is sent to "insert.php" file.
The "insert.php" file:
  - retrieves the values from the form with the PHP $_POST variables,
  - filters these values with strip_tags() and trim() functions (to remove tags and whitespace from the beginning and end),
  - uses a RegExp pattern and preg_match() function to check the "email" value to have a correct email address,
  - if all data are correct, connects to a database with the mysqli object and store the values in an associative Array ($adds), escaping them with mysqli_real_escape_string() method,
  - set the INSERT INTO statement, then, the query() method sends the SQL statement to MySQL and a new record will be added to the "users" table.

PHP code in the "insert.php" page

<?php
$erors = array();                      // set an empty array that will contains the errors
$regexp_mail = '/^([a-zA-Z0-9]+[a-zA-Z0-9._%-]*@([a-zA-Z0-9-]+\.)+[a-zA-Z]{2,4})$/';         // an e-mail address pattern

// Check for form submission
if (isset($_POST['name']) && isset($_POST['pass']) && isset($_POST['email'])) {
  // remove tags and whitespace from the beginning and end of form data
  $_POST = array_map("strip_tags", $_POST);
  $_POST = array_map("trim", $_POST);

  // chech if all form fields are filled in correctly
  // (email address and the minimum number of characters in "name" and "pass")
  if (!preg_match($regexp_mail, $_POST['email'])) $erors[] = 'Invalid e-mail address';
  if (strlen($_POST['name'])<3) $erors[] = 'Name must contain minimum 3 characters';
  if (strlen($_POST['pass'])<6) $erors[] = 'Password must contain minimum 6 characters';

  // if no errors ($error array empty)
  if(count($erors)<1) {
    // connect to the "tests" database
    $conn = new mysqli('localhost', 'root', '', 'tests');

    // check connection
    if (mysqli_connect_errno()) {
      exit('Connect failed: '. mysqli_connect_error());
    }

    // store the values in an Array, escaping special characters for use in the SQL statement
    $adds['name'] = $conn->real_escape_string($_POST['name']);
    $adds['pass'] = $conn->real_escape_string($_POST['pass']);
    $adds['email'] = $conn->real_escape_string($_POST['email']);

    // sql query for INSERT INTO users
    $sql = "INSERT INTO `users` (`name`, `pass`, `email`) VALUES ('". $adds['name']. "', '". $adds['pass']. "', '". $adds['email']. "')"; 

    // Performs the $sql query on the server to insert the values
    if ($conn->query($sql) === TRUE) {
      echo 'users entry saved successfully';
    }
    else {
      echo 'Error: '. $conn->error;
    }

    $conn->close();
  }
  else {
    // else, if errors, it adds them in string format and print it
    echo implode('<br />', $erors);
  }
}
else {
  echo 'No data from form';
}
?>

<img src="image.jpg" usemap="#map1">
<map name="map1">
  <area shape="rect" coords="9, 120, 56, 149" href="#">
  <area shape="rect" coords="100, 200, 156, 249" href="#">
</map>

#id {
  overflow: auto;
}

document.getElementById("id").onmouseover = function(){
  document.write("Have Good Life");
}

if(isset($_GET["id"])) {
  echo $_GET["id"];
}