- PHP Basics
- Learn PHP
- PHP Comments
- PHP Data Types
- PHP Variables
- PHP Operators
- PHP echo
- PHP print
- PHP echo vs. print
- PHP if else
- PHP switch
- PHP for Loop
- PHP while Loop
- PHP do...while Loop
- PHP foreach Loop
- PHP break and continue
- PHP exit()
- PHP exit() vs. break
- PHP isset()
- PHP Arrays
- PHP print_r()
- PHP unset()
- PHP Strings
- PHP Functions
- PHP File Handling
- PHP File Handling
- PHP Open File
- PHP Create a File
- PHP Write to File
- PHP Read File
- PHP feof()
- PHP fgetc()
- PHP fgets()
- PHP Close File
- PHP Delete File
- PHP Append to File
- PHP Copy File
- PHP file_get_contents()
- PHP file_put_contents()
- PHP file_exists()
- PHP filesize()
- PHP Rename File
- PHP fseek()
- PHP ftell()
- PHP rewind()
- PHP disk_free_space()
- PHP disk_total_space()
- PHP Create Directory
- PHP Remove Directory
- PHP Get Files/Directories
- PHP Get filename
- PHP Get Path
- PHP filemtime()
- PHP file()
- PHP include()
- PHP require()
- PHP include() vs. require()
- PHP mysqli Tutorial
- PHP mysqli Tutorial
- PHP and MySQL Setup
- PHP mysqli: Create Database
- PHP mysqli: Create Table
- PHP mysqli: Insert Record
- PHP mysqli: Update Record
- PHP mysqli: Fetch Record
- PHP mysqli: Delete Record
- PHP mysqli: SignUp Page
- PHP mysqli: LogIn Page
- PHP mysqli: Store User Data
- PHP mysqli Functions
- PHP mysqli_connect()
- PHP mysqli_close()
- PHP mysqli_connect_errno()
- PHP mysqli_connect_error()
- PHP mysqli_query()
- PHP mysqli_fetch_row()
- PHP mysqli_fetch_assoc()
- PHP mysqli_fetch_array()
- PHP mysqli_free_result()
- PHP mysqli_error()
- PHP mysqli_prepare()
- PHP mysqli_stmt_bind_param()
- PHP mysqli_stmt_execute()
- PHP mysqli_stmt_fetch()
- PHP mysqli_stmt_store_result()
- PHP mysqli_stmt_num_rows()
- PHP mysqli_stmt_bind_result()
- PHP mysqli_stmt_get_result()
- PHP mysqli_result class
- PHP mysqli_report()
- PHP error_reporting()
- PHP mysqli_real_escape_string()
- PHP htmlspecialchars()
- PHP Misc Topics
- PHP Object Oriented
- PHP new Keyword
- PHP header()
- PHP getallheaders()
- PHP Cookies
- PHP Sessions
- PHP Date and Time
- PHP GET vs. POST
- PHP File Upload
- PHP Image Processing
PHP mysqli code to insert data into the MySQL database
This article is created to describe the way to insert data into a MySQL database using PHP mysqli code (script). I have approached the following two ways to insert data into the MySQL database:
- Using PHP mysqli object-oriented script
- Using PHP mysqli procedural script
Whatever the approach we choose to insert data into the database using the PHP mysqli script, the main steps we need to follow are:
- Open a connection to the database.
- Write an SQL statement regarding the insertion of data.
- Initialize the SQL statement to a variable.
- Use the variable to perform a query against the MySQL database regarding the data insertion.
- Close the database connection.
For example, in this article, to insert the data, I am going to use the following SQL statement or code:
INSERT INTO `customer`(`name`, `age`, `email`) VALUES ('Olivia', '28', 'codescracker.com@gmail.com')
This SQL query indicates that the data (Olivia, 28, and codescracker.com@gmail.com) will be inserted into the table named customer at their corresponding columns (name, age, and email).
Note: A database consists of one or more tables. A table consists of one or more rows (records), where each row consists of one or more columns (fields).
Insert data using a PHP mysqli object-oriented script
To insert data into the MySQL database using a PHP mysqli object-oriented script, use the following example:
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "codescracker";
$conn = new mysqli($servername, $username, $password, $dbname);
if($conn->connect_errno)
{
echo "Connection to the database failed!<BR>";
echo "Reason: ", $conn->connect_error;
exit();
}
$sql = "INSERT INTO `customer`(`name`, `age`, `email`)
VALUES ('Olivia', '28', 'codescracker.com@gmail.com')";
$qry = $conn->query($sql);
if($qry == true)
{
echo "Data inserted successfully.";
// block of code to process further...
}
else
{
echo "Data has not been inserted!<BR>";
echo "Reason: ", $conn->error;
}
$conn->close();
?>
The output produced by the above PHP example of inserting data into the database is shown in the snapshot given below:
That is, the data has been inserted into the table "customer" of a database named "codescracker." Here is the snapshot after executing the above PHP mysqli script:
Note: Since the column named ID was defined with an AUTO_INCREMENT constraint, I have not used this column in the above example to feed the data into it manually. A column with the AUTO_INCREMENT constraint is used to allow a unique number to be generated automatically every time a new record or row has been inserted into the table.
Note: The mysqli() function is used to open a connection to the MySQL database server in object-oriented style.
Note: The new keyword is used to create a new object.
Note: The connect_errno is used to get or return the error code (if any) from the last connect call in object-oriented style.
Note: The connect_error is used to get the error description (if any) from the last connection in object-oriented style.
Note: The exit() function is used to terminate the execution of the current PHP script.
Note: The query() function is used to perform queries on the MySQL database in object-oriented style.
Note: The error function is used to return the description of the error (if any) from the most recent function call in object-oriented style.
Note: The close() function is used to close an opened connection in object-oriented style.
The above example can also be written in this way:
<?php
$driver = new mysqli_driver();
$driver -> report_mode = MYSQLI_REPORT_OFF;
$conn = @new mysqli("localhost", "root", "", "codescracker");
if($conn->connect_errno)
{
echo "Connection to the database failed!<BR>";
echo "Reason: ", $conn->connect_error;
exit();
}
$sql = "INSERT INTO `customer`(`name`, `age`, `email`)
VALUES ('Olivia', '28', 'codescracker.com@gmail.com')";
if($conn->query($sql))
{
echo "Data inserted successfully.";
// block of code to process further...
}
else
{
echo "Data has not been inserted!<BR>";
echo "Reason: ", $conn->error;
}
$conn->close();
?>
Note: The mysqli_driver() function is used to modify the error reporting mode in object-oriented style.
The first two statements are used to turn off the error reporting mode. And @ before new is used to suppress or hide the error (if any) when creating an object of type mysqli().
Here is another way to insert the data into the database using PHP mysqli object-oriented script:
<?php
$conn = new mysqli("localhost", "root", "", "codescracker");
if(!$conn->connect_errno)
{
$sql = "INSERT INTO `customer`(`name`, `age`, `email`)
VALUES ('Olivia', '28', 'codescracker.com@gmail.com')";
if($conn->query($sql))
{
// block of code to process
// after the insertion of data
}
}
$conn->close();
?>
There are several approaches you can take to insert data into the database. But these three are the most famous ways to perform the data insertion.
Insert data using PHP mysqli procedural script
To insert data into the MySQL database using a PHP mysqli procedural script, use the following example:
<?php
mysqli_report(MYSQLI_REPORT_OFF);
$conn = @mysqli_connect("localhost", "root", "", "codescracker");
if(mysqli_connect_errno())
{
echo "Connection to the database failed!<BR>";
echo "Reason: ", mysqli_connect_error();
exit();
}
$sql = "INSERT INTO `customer`(`name`, `age`, `email`)
VALUES ('Olivia', '28', 'codescracker.com@gmail.com')";
if(mysqli_query($conn, $sql))
{
echo "Data inserted successfully.";
// block of code to process further
}
else
{
echo "Data has not been inserted!<BR>";
echo "Reason: ", mysqli_error($conn);
}
mysqli_close($conn);
?>
Note: The mysqli_report() function is used to modify the error reporting mode in procedural style.
Note: The mysqli_connect() function is used to open a connection to the MySQL database server in procedural style.
Note: The mysqli_connect_errno() function is used to get or return the error code (if any) from the last connect call in procedural style.
Note: The mysqli_connect_error() function is used to return the error description (if any) from the last connection in procedural style.
Note: The mysqli_query() function is used to perform queries on the MySQL database in procedural style.
Note: The mysqli_error() function is used to return the description of the error (if any) from the most recent function call in object-oriented style.
Note: The mysqli_close() function is used to close an opened connection to the MySQL database in procedural style.
Insert Multiple Rows (Records) using PHP mysqli Script
To insert multiple records or rows into a specified table in a specified database, follow the same approach as given above for inserting a single record or row. The only change we need to make is in the SQL statement, regarding the data insertion.
The syntax to insert multiple rows using a PHP mysqli script is:
$variable = "INSERT INTO tableName(columnList) VALUES(valueList1), (valueList2), (valueList3), . . . (valueListN)";
For example:
$sql = "INSERT INTO `customer`(`name`, `age`, `email`) VALUES('Charlotte', '24', 'charloette@xyz.com'), ('Jackson', '38', 'jackson@xyz.com'), ('Sophia', '29', 'sophia@xyz.com'), ('Benjamin', '31', 'benjamin@xyz.com')";
Now let me use it practically to insert these multiple data sets into the MySQL database using, of course, the PHP mysqli object-oriented script:
<?php
$conn = new mysqli("localhost", "root", "", "codescracker");
if(!$conn->connect_errno)
{
$sql = "INSERT INTO `customer`(`name`, `age`, `email`)
VALUES('Charlotte', '24', 'charloette@xyz.com'),
('Jackson', '38', 'jackson@xyz.com'),
('Sophia', '29', 'sophia@xyz.com'),
('Benjamin', '31', 'benjamin@xyz.com')";
if($conn->query($sql))
{
echo "All Data Inserted Successfully.";
// block of code, to process
// after the insertion of data
}
}
$conn->close();
?>
After running the PHP script above to add several rows to the database, the output should be:
All Data Inserted Successfully.
And all four rows have been inserted into the table named customer, available in the database named codescracker.
Here is the snapshot of the table customer after executing the above PHP mysqli object-oriented script to insert multiple rows at once:
The first row was inserted at the start of this article, when I was describing the insertion of data (a single row) into the database.
« Previous Tutorial Next Tutorial »