PHP MySQL Insert Data

Insert Data Into MySQL Using MySQLi and PDO

After creating a database and a table, we can begin adding information.

The following are some rules to follow in terms of syntax :

• In PHP the SQL query has to be quoted.
• You need to quotes string values inside the SQL query.
• Numerical values are not quoted.
• The name NULL cannot be quoted.

In the INSERT INTO declaration new records are added to a MySQL table :

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

We established an empty table called "Employee" with three columns : "id", "name", and "city" in the previous chapter.

Look at the table structure:

Let's start filling in the blanks in the table.

Note :- If there's AUTO INCREMENT column (like the column ID), the SQL query shouldn't have to specify it; MySQL adds the value automatically.

Example (MySQLi Object-oriented)

The examples below insert a new record into "Employee" table using Mysqli Object Oriented Style:

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Build sql insert query 
$sql = "INSERT INTO Employee (name, city) VALUES ('Suresh', 'Paris')";
if ($conn->query($sql) === TRUE) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br />" . $conn->error;
}
// Close connection
$conn->close();
?>

After executing the above program the "Employee" table look like:

Example (MySQLi Procedural)

The examples below add a new record into "Employee" table using Mysqli Procedural Style:

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error);
}
// Build sql insert query 
$sql = "INSERT INTO Employee (name, city) VALUES ('Suresh', 'Paris')";
if (mysqli_query($conn, $sql)) {
  echo "New record created successfully";
} else {
  echo "Error: " . $sql . "<br />" . mysqli_error($conn);
}
// Close connection
mysqli_close($conn);
?>

Example (PDO)

The examples below insert a new row into "Employee" table using PDO Style:

<?php
try {
  $conn = new PDO("mysql:host=localhost;dbname=myDB", "username", "password");
  $sql = "INSERT INTO Employee (name, city) VALUES ('Suresh', 'Paris')";
  // use exec() because no results are returned
  $conn->exec($sql);
  echo "New record created successfully";
} catch(PDOException $e) {
  echo $sql . "<br />" . $e->getMessage();
}
$conn = null;
?>

If the INSERT or the UPDATE of an AUTO INCREMENT table is carried out, the last inserted or modified record ID may be received instantly. This information is not available.

The "id" column of the table "Employee" is an AUTO INCREMENT field.

The example below is the same as on the above one but one single line of code has been added in order to get the ID of the last entry inserted. The latest inserted ID is also echoed :

Example (MySQLi Object-oriented)

The insert_id property of mysqli class is used to get the ID of last inserted records.

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Build sql insert query 
$sql = "INSERT INTO Employee (name, city) VALUES ('Babu', 'London')";
if ($conn->query($sql) === TRUE) {
  echo "New record created successfully<br />";
  $last_id = $conn->insert_id;
  echo "Last inserted ID :- " . $last_id;
} else {
  echo "Error: " . $sql . "<br />" . $conn->error;
}
// Close connection
$conn->close();
?>

Output:-

After executing the above program the "Employee" table look like:

Example (MySQLi Procedural)

The mysqli_insert_id() method is used to get the ID of last inserted records.

$last_id = mysqli_insert_id($conn);
echo "Last inserted ID :- " . $last_id;

Example (PDO)

The lastInsertId() function is used to get the ID of last inserted records.

$last_id = $conn->lastInsertId();
echo "Last inserted ID :- " . $last_id;

We can insert multiple statements at a time into Mysql using PHP Mysqli extension.

Note:- PDO will not support to execute multiple statement at once.

The examples below insert three new records at once into "Employee" table:

Example (MySQLi Object-oriented)

The multi_query() method is used to insert multiple records at a time.

<?php
// Create connection
$conn = new mysqli("localhost", "username", "password", "myDB");
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// Build sql insert query 
$sql = "INSERT INTO Employee (name, city) VALUES ('Kumar', 'New York');
    INSERT INTO Employee (name, city) VALUES ('Siva', 'Paris');
    INSERT INTO Employee (name, city) VALUES ('Rishi', 'London');";
if ($conn->multi_query($sql) === TRUE) {
  echo "New record created successfully<br />";
} else {
  echo "Error: " . $sql . "<br />" . $conn->error;
}
// Close connection
$conn->close();
?>

Note:- A semicolon must be used to divide each SQL statement.

After executing the above program the "Employee" table look like:

Example (MySQLi Procedural)

The mysqli_multi_query() method is used to insert multiple records at a time.

if (mysqli_multi_query($conn, $sql)) {
  echo "New record created successfully<br />";

For complete program, please refer procedural style program in the top of the page