Insert data into MySQL using MySQLi and PDO
After creating the database and tables, we can add data to the tables.
Here are some grammar rules:
SQL query statements in PHP must use quotes
String values in SQL queries must be quoted.
Numeric values do not require quotes
NULL values do not require quotes
The INSERT INTO statement is typically used to add new records to a MySQL table:
INSERT INTO table_name (column1, column2, column3,...)VALUES (value1, value2, value3,...)
To learn more about SQL, check out our SQL tutorials.
In the previous chapters we have created the table "MyGuests" with the following fields: "id", "firstname", "lastname", "email" and "reg_date". Now, let's start filling the table with data.
 | Note: If the column is set to AUTO_INCREMENT (such as the "id" column) or TIMESTAMP (such as the "reg_date" column), we do not need to specify the value in the SQL query statement; MySQL will automatically add a value to the column. |
|---|
The following example adds a new record to the "MyGuests" table:
Example (MySQLi - Object Oriented)
<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; // Create a connection $conn = new mysqli ( $servername , $username , $password , $dbname ) ; // Detect connection if ( $conn -> connect_error ) { die ( " Connection failed: " . $conn -> connect_error ) ; } $sql = " INSERT INTO MyGuests (firstname, lastname, email)VALUES ('John', 'Doe', '[email protected]') " ; if ( $conn -> query ( $sql ) === TRUE ) { echo " New record inserted successfully " ; } else { echo " Error: " . $sql . " <br> " . $conn -> error ; } $conn -> close ( ) ; ?> Example (MySQLi - Procedure Oriented)
<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDB " ; // Create a connection $conn = mysqli_connect ( $servername , $username , $password , $dbname ) ; // Detect connection if ( ! $conn ) { die ( " Connection failed: " . mysqli_connect_error ( ) ) ; } $sql = " INSERT INTO MyGuests (firstname, lastname, email)VALUES ('John', 'Doe', '[email protected]') " ; if ( mysqli_query ( $conn , $sql ) ) { echo " New record inserted successfully " ; } else { echo " Error: " . $sql . " <br> " . mysqli_error ( $conn ) ; } mysqli_close ( $conn ) ; ?> Example(PDO)
<?php $servername = " localhost " ; $username = " username " ; $password = " password " ; $dbname = " myDBPDO " ; try { $conn = new PDO ( " mysql:host= $servername ;dbname= $dbname " , $username , $password ) ; // Set PDO error mode for throwing exceptions $conn -> setAttribute ( PDO :: ATTR_ERRMODE , PDO :: ERRMODE_EXCEPTION ) ; $sql = " INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', '[email protected]') " ; // Use exec(), no results are returned $conn -> exec ( $sql ) ; echo " New record inserted successfully " ; } catch ( PDOException $e ) { echo $sql . " <br> " . $e -> getMessage ( ) ; } $conn = null ; ?>
