Insert record into table using mysql , mysqli and pdo

Insert query is used to insert a record into table using  mysql, mysqli and pdo.   These are basically  APIs of PHP that is used to access the MySQL databases and tables. Using these queries you can create, read, update and delete (CRUD) records of a table in the  database. But in this article we learn how to insert a record into table using mysql query with all different methods like mysql, mysqli and PDO. Before we start first you have choose which one method is better for our project.

There are important key points written below, which helps you to decide which method you should use.

1. MySQL has been deprecated and removed as of PHP 7 and its newer versions.
2. MySQLi is a replacement for the mysql functions, with object-oriented and procedural versions and it supports only mySQL. 
3. The main advantage of uisng pdo is it supports and provide a uniform method of access to 11 difference databases like CUBRID, MS SQL Server, Oracle, ODBC and DB2, PostgreSQL, Firebird/Interbase, IBM, SQLite, 4D, Informix and MySQL. 
4. MySQLi and PDO both use prepared statements with escaping which is more secure as compare to mysql .

Step 1. Create database with name myproject_db

Step 2. Run following SQL query/queries on server to create customers table under myproject_db database.

CREATE TABLE `customers` (
    `id` int(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `first_name` varchar(30) NOT NULL,
    `last_name` varchar(40) NOT NULL,
    `email` varchar(40) NOT NULL,
    `street_address` varchar(40) NOT NULL,
    `city` varchar(20) NOT NULL,
    `state` varchar(20) NOT NULL,
    `zip_code` varchar(20) NOT NULL,
    `phone` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 3. Make database connection.

3.1. Make database connection using mySQLi  with object-oriented approach.

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myproject_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

3.2. Database connection using mySQLi with procedural approach.

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myproject_db";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
  die("Connection failed: " . mysqli_connect_error());
}

3.3. Make database connection using PDO.

$host='localhost';
$db = 'myproject_db';
$username = 'root';
$password = '';

$dsn= "mysql:host=$host;dbname=$db";
 
try{
    //make pdo connection
    $conn = new PDO($dsn, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}catch (PDOException $e){
    // report error message
    echo $e->getMessage();
}

Note : You can choose one of the above method to make database connection and change your servername , username, password variable value according to your server details. 

Step 4.  Database connection has been established. Now you can use insert query to create a new record in table.

4.1 The following query add a new record to the "customers" table using mySQLi object oriented approach.

$sql =  "INSERT INTO `customers` ( `first_name`, `last_name`, `email`, `street_address`, `city`, `state`, `zip_code`, `phone`) 
VALUES ( 'John', 'doe', 'john@hotmail.com', '#2534', 'New York', 'NY', '14020', '(212) 123-1234')";

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

4.2 The following query add a new record to the "customers" table using mySQLi procedural approach.

$sql = "INSERT INTO `customers` ( `first_name`, `last_name`, `email`, `street_address`, `city`, `state`, `zip_code`, `phone`) 
VALUES ( 'John', 'doe', 'john@hotmail.com', '#2534', 'New York', 'NY', '14020', '(212) 123-1234')";

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

mysqli_close($conn);

4.3 insert new record  in mySQLi with prepared statements.

$stmt = $conn->prepare("INSERT INTO customers (`first_name`, `last_name`, `email`, `street_address`, `city`, `state`, `zip_code`, `phone`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bind_param("ssssssss", $firstname, $lastname, $email, $street_address, $city, $state, $zip_code, $phone);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@hotmail.com";
$street_address = "#2534";
$city = "New York";
$state = "NY";
$zip_code = "14020";
$phone = "(212) 123-1234";

$stmt->execute();
echo "New records created successfully";
$stmt->close();
$conn->close();

4.4  Insert new record using PDO.

$sql = "INSERT INTO `customers`(  `first_name`, `last_name`, `email`, `street_address`, `city`, `state`, `zip_code`, `phone`) 
VALUES(:first_name,:last_name,:email, :street_address,:city,:state,:zip_code,:phone )";

$query = $conn->prepare($sql);

$first_name = "John";
$last_name = "Doe";
$email = "john@hotmail.com";
$street_address= "#2534";
$city = "New York";
$state = "NY";
$zip_code = "14020";
$phone = "(212) 123-1234";

try{
     $query->execute(array( ':first_name'=>$first_name,':last_name'=>$last_name,':email'=>$email, ':street_address'=>$street_address, ':city'=>$city, ':state'=>$state, ':zip_code'=>$zip_code, ':phone'=>$phone));
    echo "New record created successfully";
}
catch(PDOException $e){
    echo $e->getMessage();
}