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:

Column Name Datatype Extra
id int auto_increment
name text
city text

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:

ID Name City
1 Suresh Paris

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;
?>
You can also search for these topics, php mysql insert data, php mysql insert data syntax, php mysql insert data example, rules to insert data in mysql using php, php mysql insert data mysqli object-oriented example, mysql insert data mysqli procedural using php example, php mysql insert data pdo example.

Get ID of The Last Inserted Record

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:-

New record created successfully
Last inserted ID :- 2

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

ID Name City
1 Suresh Paris
2 Babu London

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;
You can also search for these topics, php mysql get last inserted id, php mysql get last inserted id example, php mysql get last inserted id autoincrement, php mysql get last inserted id mysqli object-oriented, php mysql get last inserted id mysqli procedural, php mysql get last inserted id pdo.

Insert Multiple Records Into MySQL Using MySQLi

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:

ID Name City
1 Suresh Paris
2 Babu London
3 Kumar New York
4 Siva Paris
5 Rishi London

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

You can also search for these topics, php mysql insert multiple records, php mysql insert multiple records example, insert multiple records into mysql with a single query php, insert multiple records into mysql pdo, insert multiple records into mysqli object-oriented, insert multiple records into mysqli procedural.