MySQL Insert, Multiple Insert, and Last Inserted Query in PHP

Welcome to Part 3 of MySQL Tutorial for Beginners in 7 Days. This is a continuation of our previous tutorial about MySQL Create Table in Multiple Ways. In this MySQL tutorial will focus more on MySQL Insert, Multiple Insert, and Last Inserted Query.

MySQL Insert

MySQL Insert Query is used to insert a record in MySQL database the statement used is INSERT INTO command.

Syntax:

INSERT INTO name_of_table (column1, column2, column3,…)
VALUES (value1, value2, value3,…)

Rules in performing the INSERT INTO Command:

• A Numeric Values and NULL word should not be quoted
• String Values inside SQL query should be quoted
• Using PHP all SQL query should be quoted
• No Need to specify a column that is SET to AUTO_INCREMENT and TIMESTAMP

In the last tutorial called MySQL Create Table, we created a blank table named ‘tblpeople’. This time, we’re going to populate this table.

Here are the different examples of populating the ‘tblpeople’:

Example using MySQLi (Object-Orient):

die("Connection failed: " . $conn->connect_error);
}

// sql to Insert record table
$sql = "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Villanueva', 'Joken', 'Kabankalan City');";

if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();
?>

Example using MySQLi (Procedural):

$server = "localhost";
$username = "root";
$password = "";
$database = "mysqltutorial";

// Create connection
$conn = mysqli_connect($server, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// sql to Insert record table
$sql = "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Villanueva', 'Joken', 'Kabankalan City');";

if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "
" . mysqli_error($conn);
}

mysqli_close($conn);
?>

Example using PDO:

$sql = "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Villanueva', 'Joken', 'Kabankalan City');";

// use exec() because no results are returned
$conn->exec($sql);
echo "New record created successfully";
}
catch(PDOException $e)
{
echo $sql . "
" . $e->getMessage();
}

$conn = null;
?>

MySQL Multiple Insert

MySQL Multiple Insert Performs multiple queries in MySQL, you need to use the function called “mysqli_multi_query”. This function allows you to perform one or more queries against the database, you only have to separate the query using the semicolon.

Example using MySQLi(Object-Oriented):

die("Connection failed: " . $conn->connect_error);
}

// sql to Insert record table
$sql = "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Villanueva', 'Joken', 'Kabankalan City');";
$sql .= "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Batuto', 'Erick Jason', 'Cebu City');";
$sql .= "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Drapite', 'Bryan', 'Kabankalan City');";

if ($conn->multi_query($sql) === TRUE) {
echo "New records created successfully";
} else {
echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();
?>

Example using MySQLi(Procedural):

$username = "root";
$password = "";
$database = "mysqltutorial";

// Create connection
$conn = mysqli_connect($server, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// sql to Insert record table
$sql = "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Villanueva', 'Joken', 'Kabankalan City');";
$sql .= "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Batuto', 'Erick Jason', 'Cebu City');";
$sql .= "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Drapite', 'Bryan', 'Kabankalan City');";

if (mysqli_multi_query($conn, $sql)) {

echo "New record created successfully!";
} else {
echo "Error: " . $sql . "
" . mysqli_error($conn);
}

mysqli_close($conn);
?>

Example using PDO:

$conn->beginTransaction();

// sql to Insert record table

$conn->exec("INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Villanueva', 'Joken', 'Kabankalan City');");
$conn->exec ("INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Batuto', 'Erick Jason', 'Cebu City');");
$conn->exec ("INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Drapite', 'Bryan', 'Kabankalan City');");

// commit the changes for transaction
$conn->commit();
echo "New record created successfully!";
}
catch(PDOException $e)
{
// do roll back if something went wrong
$conn->rollback();
echo $sql . "
" . $e->getMessage();
}

$conn = null;
?>

In the above lesson, we tackle how to insert a record in a MySQL database table. This time we’re going discuss MySQL Last Inserted to get the last Inserted ID from our table “tblpeople”. Using the AUTO_INCREMENT field, we are able to get the ID of the last inserted or updated record immediately.

Since we will be using the same code that we use from our previous topic called MySQL Insert we only need to add a single line of code for retrieving and echo the Last Inserted ID.

MySQL Last Inserted

Example Using MySQLi(Object-Oriented):

die("Connection failed: " . $conn->connect_error);
}

// sql to Insert record table
$sql = "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Villanueva', 'Joken', 'Kabankalan City');";

if ($conn->query($sql) === TRUE) {
$lastinsertedid = $conn->insert_id;
echo "New record created successfully! And the last record inserted is:" . $lastinsertedid;
} else {
echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();

?>

Example Using MySQLi(Procedural):

$username = "root";
$password = "";
$database = "mysqltutorial";

// Create connection
$conn = mysqli_connect($server, $username, $password, $database);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

// sql to Insert record table
$sql = "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Villanueva', 'Joken', 'Kabankalan City');";

if (mysqli_query($conn, $sql)) {
$lastinserteid = mysqli_insert_id($conn);
echo "New record created successfully! And the last Inserted ID is : ". $lastinserteid;
} else {
echo "Error: " . $sql . "
" . mysqli_error($conn);
}

mysqli_close($conn);
?>

Example Using PDO:

$sql = "INSERT INTO `tblpeople` (`LNAME`, `FNAME`, `ADDRESS`)
VALUES ('Villanueva', 'Joken', 'Kabankalan City');";

// use exec() because no results are returned
$conn->exec($sql);
$lastinsertedid = $conn->lastInsertId();
echo "New record created successfully! And the last Inserted ID is :" . $lastinsertedid;
}
catch(PDOException $e)
{
echo $sql . "
" . $e->getMessage();
}

$conn = null;
?>

Summary

In this tutorial, we tackled different ways on how to perform MySQL Insert, Multiple Insert, and Last Inserted query using PHP in different coding styles. In the next lesson, we will be practicing how Load records from MySQL Database to tables using MySQL Select Statement command.

Questions from the author

Which is better to use PHP/MySQLi or PHP/PDO for MySQL Insert query? Leave a Comment below for your answer.