MySQLi to PDO Prepared Statements in PHP Tutorial

If you are using mysqli_connect for creating a database connection in PHP then it’s time to move to PDO stands for PHP Data Objects, this is a more secure and appropriate way to connect to a database in PHP. The reason you should use PDO is that it’s more reliable than the formal method of using mysqli_connect and it prevents SQL injections. It will secure your queries as well as make it more efficient. In this tutorial, I’ll give you the simple example of mysqli connection and then we’ll convert that into PDO.

php-pdo-connection

Database using MySQLi_connect

If you are using mysqli_connect in PHP for establishing a connection to the database then you’ll always be using something like this:

<?php

$con = mysqli_connect(“localhost”,”root”,”password”,”database_name”);

?>

The above is the simple mysqli connection saved in a local variable ($con), you just need to pass four parameters to the database which can be clearly seen above, and you might have been using this method for quite a long time.

After establishing the connection, we always run queries for inserting/deleting/selecting/updating data in a mysql table. For that we use the following syntax:

<?php

//Selecting query

$query = “SELECT * FROM table_name”;

$run = mysqli_query($con,$query);

?>

And for inserting query we use similar code like this:

<?php

//inserting query

$query = “INSERT INTO table_name (data1, data2, data3) VALUES (‘$var1′,’$var2′,’$var3’)”;

$run = mysqli_query($con,$query);

?>

Similar queries are also for deleting data and updating data like this:

<?php

//deleting query

$query = “DELETE from table_name where data1=’$var1′”;

$run = mysqli_query($con,$query);

?>

<?php

//updating query

$query = “UPDATE table_name SET data1=’$var1””;

$run = mysqli_query($con,$query);

?>

Now the above is a traditional way for establishing a connection to the database using mysqli_connect API, now below we’ll learn how to convert this connection to PDO (PHP Data Objects) the same way we did in mysqli.

PDO MySQL Connection

Now the basic PDO connection is also very easy same like the mysqli_connect but with a little bit different approach. However, PDO is a strong API which you can use for database connections not only for MySQL but for many other databases such as Oracle and SQL Lite. Below is the simple connection syntax for MySQL in PDO:

<?php
$host = “localhost”;
$user = “root”;
$pass = “password”;
$db_name = “db_name”;try {
$con = new PDO(“mysql:host=$host;dbname=$db_name”, $user, $pass);
// set the PDO error mode to exception
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);}catch(PDOException $e)
{
echo “Error: ” . $e->getMessage();
}?>

PDO connection is always created within the try{} and catch{} blocks, this is because the try{} block establishes the connection and run any prepared statement (query) which the developer wants to run. And the catch{} block checks for an error if there is any error in the connection or in the whole process. Now, please note below line by line explanation of the code above.

$con = new PDO(“mysql:host=$host;dbname=$db_name”, $user, $pass);

The above line actually creates a PDO connection to the database, we save this connection in a local variable $con and equal it to new PDO and then inside two () parenthesis, we add the parameters such as the host, db_name etc.

$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

The above line is just an attribute for error handling, and the actual error mode is ERRMODE_EXCEPTION, and we set this for $con variable which holds the whole database connection. And everything inside the catch() block is by default added here which will display an error if there is any.

PDO Prepared Statements

Now when you’ve established a connection using PDO, it’s time to know how we can run MySQL queries such as selecting data, inserting data and updating data. Please note that the query will also be inside the catch{} block, we can have one query or many queries but within the catch{} block, below is an example of selecting data from table:

<?php
$host = “localhost”;
$user = “user”;
$pass = “password”;
$db_name = “db_name”;try {$con = new PDO(“mysql:host=$host;dbname=$db_name”, $user, $pass);// set the PDO error mode to exception$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sth= $con->prepare(‘SELECT data1, data2, data3 from table_name’);
// setting the fetch mode
$sth->setFetchMode(PDO::FETCH_ASSOC);
$sth->execute();
while($row= $sth->fetch()) {
echo$row[‘data1’] . “<br>”;
echo$row[‘data2’] . “<br>”;
echo$row[‘data3’] . “<br>”;
}
}

catch(PDOException $e)
{
echo “Error: ” . $e->getMessage();
}
?>

The above code is the query through which we can select data from a table using PDO method, first we have created a simple local variable called $sth stands for statement handler, and then we are referencing the $con variable which holds the database, and next we are using the prepare keyword for preparing the query, and then we are just selecting data from the table like data1, data2 etc, the columns names may be different in your case.
Now on the next line, we are setting up a mode to fetch the data from the database, and we used PDO::FETCH_ASSOC mode in above case, this is same like you use mysqli_fetch_assoc, but here in PDO, you’ll also have to use another fetch() function when retrieving data using a while loop or something like that as you can see in above example. The above example should fetch and display all the records on the page which are in the table_name.
Now see the insert query example below, which similarly like the select one:
<?php
$host = “localhost”;
$user = “user”;
$pass = “password”;
$db_name = “db_name”;try {
$con = new PDO(“mysql:host=$host;dbname=$db_name”, $user, $pass);
// set the PDO error mode to exception
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);// prepare sql and bind parameters
    $sth = $con->prepare(“INSERT INTO table_name (data1, data2, data3)
    VALUES (:var1, :var2, :var3)”);
    $sth->bindParam(‘:var1’, $var1);
    $sth->bindParam(‘:var2’, $var2);
    $sth->bindParam(‘:var3’, $var3);}catch(PDOException $e)
{
echo “Error: ” . $e->getMessage();
}

?>

In the above example, we are actually inserting data into the table, the table_name can be any table. The next variable again is $sth (statement handler) and then $con variable for database connection and the we again use prepare() to write the query. But the great part here is the VALUES clause which has the values like :var1, var2, var3. These values we’ve bound using the bindParam() function which we use to bind the values before inserting into the database for a safe and secure insertion. We can also specify the type of the value which is going to be inserted to the table, so that MySQL engine knows which type of data to be expected.

Deleting a record from the table using PDO is very easy to do thing and very similar to the mysqli_connect API, see the below example for complete understanding:

<?php
$host = “localhost”;
$user = “user”;
$pass = “password”;
$db_name = “db_name”;try {
$con = new PDO(“mysql:host=$host;dbname=$db_name”, $user, $pass);
// set the PDO error mode to exception
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);// delete query
$query =(“DELETE FROM table_name where data1=5”);$con->exec($query);}

catch(PDOException $e)
{
echo “Error: ” . $e->getMessage();
}

?>

The code above is just for deleting a record from the table table_name, where data1=5, that’s mean the data from the column data1 which is equal to 5 will be deleted. And please note that we are not using prepare statement here because that’s not necessary in delete query, and we use exec() function to execute this query because no result will be returned to the page.

The last example of PDO will be the update query, which is similar to the delete query, see below example:

$update = “UPDATE table_name SET data1=’New Data’ WHERE id=2”;
// Prepare statement
$sth = $con->prepare($sql);
// execute the query
$sth->execute();

The above example will simply update the record in the table for the field name data1 with New Data and where id will be equal to 2.

And finally, this was the PDO tutorial, which you can use in your future applications if you want to move from mysqli to PDO, and this is very important because this protects your PHP applications from SQL injections. Let me know if you have any questions.

About Abdul Wali

Abdul Wali is a professional blogger, marketer and web developer working online for last 5 years, he's been deeply involved in PHP, Web Development, SEO and computer video tutorials in different languages i.e English, Urdu and Pashto. You can catch him on and add him on Facebook.

Check Also

create-video-sharing-site1

How to Create a Video Sharing Website Like YouTube?

If you are thinking to create a video sharing website like YouTube then keep in ...