- PHP Basics
- PHP Home
- PHP Environment Setup
- PHP Getting Started
- PHP Basic Syntax
- PHP echo
- PHP print
- PHP echo Vs print
- PHP Comments
- PHP Data Types
- PHP Variables
- PHP Variable Scope
- PHP gettype()
- PHP Constants
- PHP Operators
- PHP Program Control
- PHP Decision Making
- PHP if-elseif-else
- PHP switch
- PHP Loops
- PHP for Loop
- PHP while Loop
- PHP do-while Loop
- PHP foreach Loop
- PHP break & continue
- PHP Popular Topics
- PHP Arrays
- PHP print_r()
- PHP Strings
- PHP Functions
- PHP References
- PHP Object Oriented
- PHP Object Oriented
- PHP Classes & Objects
- PHP Member Variable
- PHP Member Function
- PHP Encapsulation
- PHP Data Abstraction
- PHP Inheritance
- PHP Constructor Destructor
- PHP Polymorphism
- PHP Web Developments
- PHP Web Developments
- PHP GET & POST
- PHP Read Requested Data
- PHP File Handling (I/O)
- PHP File Handling (I/O)
- PHP fopen() | Open File
- PHP Create a File
- PHP fwrite() | Write to File
- PHP fread() | Read File
- PHP feof()
- PHP fgetc()
- PHP fgets()
- PHP fclose() | Close File
- PHP unlink() | Delete File
- PHP Append to File
- PHP copy() | Copy File
- PHP file_get_contents()
- PHP file_put_contents()
- PHP file_exists()
- PHP filesize()
- PHP rename() | Rename File
- PHP fseek()
- PHP ftell()
- PHP rewind()
- PHP disk_free_space()
- PHP disk_total_space()
- PHP mkdir() | Create Directory
- PHP rmdir() | Remove Directory
- PHP glob() | Get Files/Directories
- PHP basename() | Get filename
- PHP dirname() | Get Path
- PHP filemtime()
- PHP file()
- PHP Advanced
- PHP Cookies
- PHP Sessions
- PHP Send Emails
- PHP Serialization
- PHP Namespaces
- PHP File Upload
- PHP Date and Time
- PHP Image Processing
- PHP Regular Expression
- PHP Predefined Variables
- PHP Error Handling
- PHP Debugging
- PHP and MySQLi Tutorial
- PHP and MySQLi Home
- PHP MySQLi Setup
- PHP MySQLi Create DB
- PHP MySQLi Create Table
- PHP MySQLi Connect to DB
- PHP MySQLi Insert Record
- PHP MySQLi Fetch Record
- PHP MySQLi Update Record
- PHP MySQLi Delete Record
- PHP MySQLi SignUp Page
- PHP MySQLi LogIn Page
- PHP MySQLi Store User Data
- PHP MySQLi Close Connection
- PHP connect_errno
- PHP connect_error
- PHP query()
- PHP fetch_row()
- PHP fetch_assoc()
- PHP fetch_array()
- PHP free_result()
- PHP error
- PHP prepare()
- PHP bind_param()
- PHP execute()
- PHP fetch()
- PHP store_result()
- PHP num_rows
- PHP bind_result()
- PHP get_result()
- PHP mysqli_result Class
- PHP Error Constants
- PHP mysqli_driver()
- PHP Misc
- PHP error_reporting()
- PHP Escape Special Characters
- PHP htmlspecialchars()
- PHP new
- PHP header()
- PHP getallheaders()
- PHP empty()
- PHP isset()
- PHP unset()
- PHP exit()
- PHP exit Vs break
- PHP include()
- PHP require()
- PHP include() Vs require()
- PHP AJAX & XML
- PHP AJAX
- PHP XML
- PHP File Handling Functions
- PHP abs()
- PHP Test
- PHP Online Test
- Give Online Test
- All Test List
PHP MySQLi Code to Insert Data into 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 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 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 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 following SQL statement/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 its 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 PHP MySQLi Object-Oriented Script
To insert data into the MySQL database using PHP MySQLi object-oriented script, use 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 above PHP example on inserting data into the database is shown in the snapshot given below:
That is, the data has been inserted into the table customer of database named codescracker. Here is the snapshot, after executing the above PHP MySQLi script:
Note - Since the column named ID was defined with AUTO_INCREMENT constraint, therefore I have not used this column in above example to feed the data into it manually. As column with AUTO_INCREMENT constraint, is used to allow a unique number to generate automatically every time when a new record or row has been inserted into the table.
Note - The mysqli() 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/return the error code (if any) from last connect call, in object-oriented style.
Note - The connect_error is used to get the error description (if any) from last connection, in object-oriented style.
Note - The exit() is used to terminate the execution of the current PHP script.
Note - The query() is used to perform query on the MySQL database, in object-oriented style.
Note - The error is used to return the description of error (if any), by the most recent function call, in object-oriented style.
Note - The close() 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() 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/hide the error (if any) when creating an object of type mysqli() class.
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(); ?>
Multiple ways, you can approach to insert the 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 PHP MySQLi procedural script, use 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() is used to modify the error reporting mode, in procedural style.
Note - The mysqli_connect() is used to open a connection to the MySQL database server, in procedural style.
Note - The mysqli_connect_errno() is used to get/return the error code (if any) from last connect call, in procedural style.
Note - The mysqli_connect_error() is used to return the error description (if any) from the last connection, in procedural style.
Note - The mysqli_query() is used to perform query on the MySQL database, in procedural style.
Note - The mysqli_error() is used to return the description of error (if any), by the most recent function call, in object-oriented style.
Note - The mysqli_close() 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 specified table of specified database, follow the same approach as given above, for inserting single data or row. The only change, we need to do, is in the SQL statement, regarding the data insertion.
The syntax to insert multiple rows using 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 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 executing the above PHP script to insert multiple rows into the database, the output should be:
All Data Inserted Successfully.
And all the four rows has been inserted into the table named customer, available in 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 start of this article, when I was describing, about the insertion of data (single row) into the database.
« Previous Tutorial Next Tutorial »