PHP prepared statements with MySQLi, MySQLi OOP and PDO

Hi, welcome to the PHP prepared statements tutorial.

A prepared statement is a feature that is used to execute the same (or similar) SQL statements repeatedly with high efficiency.

If you want to avoid SQL injection attacks to secure your PHP code or application then I highly recommended you use the prepared statement when you interact with a Database.

In this tutorial, we will be inserting data into a MySQL database using prepared statements.


How Many ways we perform PHP
prepared statements

We can perform PHP prepared statements with MySQLi, MySQLi OOP and PDO.

  • 1 – Prepared statements with MySQLi
  • 2 – Prepared statements with MySQLi OOP
  • 3 – Prepared statements with PHP PDO

Before we start to insert data into MySQL database using Prepared statements, first we will create our database.

MySQL Database and table creation

  • Database Name: As you wish
  • Table Name: users

After creating the Database use the below SQL code to create users table and the structure of the users table.

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`);

ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT;

If you are using the phpMyAdmin, then select the newly created Database and then go to the SQL tab above.

After that, paste the above SQL code in the Run SQL query box and then click on the go button.


1 – PHP prepared statements with MySQLi

<?php
// Database connection
$database_connection= mysqli_connect("localhost","root","","database_name");

// Check database connection
if (mysqli_connect_errno()){
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$stmt = mysqli_prepare($database_connection, "INSERT INTO `users` (first_name,last_name,email) VALUES (?,?,?)");

// Data binding
// "sss" is data type (Read note)
mysqli_stmt_bind_param($stmt, "sss", $first_name, $last_name, $email);

// Put data into the variables
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';

// execute and if execute prepared statement
if(mysqli_stmt_execute($stmt)){
    echo "Data inserted successfully.";
}
// else data not inserted
else{
    echo "Something error occurred";
}
?>

In the above PHP Code, we added three data $first_name, $last_name and $email.

The type of all the three data is string, for that the reason we added “sss”.

If the $first_name data type would be an integer, and the $last_name and $email data type would be string, then our syntax should be “iss”.

Type specification chars

CharacterDescription
iIf the corresponding variable type is an integer(Whole number)
dIf the corresponding variable type is a double(decimal number)
sstring(text)
bblob(Binary Large Object)

2 – With MySQLi OOP(Object Oriented Programming)

<?php
// Database connection
$database_connection = new mysqli("localhost", "root", "", "database_name");

// Check database connection
if ($database_connection === false){
  echo "Failed to connect to MySQL: " . $database_connection->connect_error;
}

// Insert query (Insert data into users table)
$stmt = $database_connection->prepare("INSERT INTO `users` (first_name, last_name, email) VALUES (?,?,?)");

$stmt->bind_param("sss", $first_name, $last_name, $email);

// Put data into the variables
$first_name = 'John';
$last_name = 'Doe';
$email = '[email protected]';

if($stmt->execute()){
    echo "Data inserted successfully."; 
}else{
    echo "Something error occurred.";
}
?>

3 – With PDO(PHP Data Objects)

<?php
$servername = "localhost";
$username = "root";
$password = "";
$database_name = "database_name";

// CREATE DATABASE CONNECTION USING PDO METHOD
try {
    $database_connection = new PDO("mysql:host=$servername;dbname=$database_name", $username, $password);
    // Set the PDO error mode to exception
    $database_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
catch(PDOException $e)
    {
    echo "Connection Failed" . $e->getMessage();
    }


// INSERT DATA INTO THE DATABASE
try {
    
    
    $sql = "INSERT INTO `users` (first_name, last_name, email) VALUES (?,?,?)";
    $stmt = $database_connection->prepare($sql);
    
    $stmt->bindParam(1, $first_name, PDO::PARAM_STR);
    $stmt->bindParam(2, $last_name, PDO::PARAM_STR);
    $stmt->bindParam(3, $email, PDO::PARAM_STR);
    
    // PUT DATA INTO THE VARIABLES
    $first_name = 'John';
    $last_name = 'Doe';
    $email = '[email protected]';
    $stmt->execute();
    
    echo "Data inserted successfully";
    
    }
catch(PDOException $e)
    {
    echo "data not inserted";
    }
?>

Use the colon(:) before text to replace the question marks.

VALUES (:first_name, :last_name, :email)
$stmt->bindParam(':first_name', $first_name, PDO::PARAM_STR);
$stmt->bindParam(':last_name', $last_name, PDO::PARAM_STR);
$stmt->bindParam(':email', $email, PDO::PARAM_STR);

This tutorials is completed.

Leave a Reply

Your email address will not be published. Required fields are marked *