• PDO for beginner (part 2)
  • Using PDO Prepared Statements
  • The End

#PDO for beginner (part 2)

In Part 1 of the PDO tutorial, we saved data in our database directly using PDO. This is not ideal for security, however, as it can get affected by SQL injections. To save our data from SQL injections we use prepared statements in MySQLi and PDO. In this part, we are going to learn how to make PDO prepared statements.

For this tutorial, we are going to use the same database and PDO connection class that we created in part 1. Let’s get started.

#Using PDO Prepared Statements

There are two ways you can use PDO prepared statements to execute your queries. Let’s learn both these methods.

Using Named Parameters:

In named parameters, we gave a name to the prepared value from which the value will be replaced. Let’s take a look at the example below:

Replace the code in index.php with this one:

include 'connection.php';
 
try {
 
    $conec = new Connection();
    $con   = $conec->Open();
 
    $sql = "INSERT INTO `user`(`name`, `email`) VALUES (:name,:email)";
    $pre  = $con->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
 
    if($pre->execute(array(':name' => 'ahmed', ':email' => 'ahmed.khan@cloudways.com'))){
        echo "Successful";
    }
} catch (PDOException $ex) {
    echo $ex->getMessage();
}

Let’s decipher this code now:

First, we have created a prepared statement in which we have replaced our insert values with a prepared name i.e :name and :email. After that we prepared it with our PDO connection.

$sql = "INSERT INTO `user`(`name`, `email`) VALUES (:name,:email)";
$con->prepare($sql);

Then we have binded our value using key => value array and executed it.

$pre->execute(array(':name' => 'ahmed', ':email' => 'ahmed.khan@cloudways.com'))

Using Question Mark Parameters:

In this method, we use question mark instead of a prepared name and bind our value in an array at the time of execution. For example:

Replace the code of index.php with the code given below.

include 'connection.php';
 
try {
 
    $conec = new Connection();
    $con   = $conec->Open();
 
    $sql = "INSERT INTO `user`(`name`, `email`) VALUES (?,?)";
    $pre  = $con->prepare($sql);
    $param = array(1=>"Ahmed Khan",
        2=>"ahmed.khan@cloudways.com");
    foreach ($param as $key => &$value) {
        $pre->bindParam($key,$value);
    }
 
    if($pre->execute()){
        echo 'Successful';
    }
} catch (PDOException $ex) {
    echo $ex->getMessage();
}

The code is similar to the one in the previous example, but with some differences. In this code we have used PDO::bindParam to bind the data with our query using foreach. Let’s understand the array which we have binded the values with.

$param = array(1=>"Ahmed Khan", 2=>"ahmed.khan@cloudways.com");

Integer 1 as a key represents the first ? and then we have added the value for that question mark and so on. Once we have created the array, we binded it to our query using foreach.

$pre->bindParam($key,$value);

We have used foreach to bind our value to bindParam because we can’t pass direct array to it. Once the values are binded, we execute the code.

#The End

This is the second and final part of the PDO series. I hope you have understood all the PDO from my article. If you haven’t, feel free to contact me or comment in the section below. For more references on PDO, see the official document on the PHP site.

Hopefully this simple tutorial helped you with your development. If you like our post, please follow us on Twitter and help spread the word. We need your support to continue. If you have questions or find our mistakes in above tutorial, do leave a comment below to let us know.