From MySql to PDO - CRUD with PDO

By zooboole

Hi, everyone. Once again this is zooboole, ready to take you in a new programming adventure. Today I would like to show you how you can change you MySQL practices into their equivalent in PDO.

Why?

I am certain there are many people who still use MySQL in their PHP application to persist information, and that is not advised. It's not good because the PHP extension is deprecated since PHP version 5.5.0, and has been totally removed in PHP 7.

That means if you are still having functions starting from mysql_* such as mysql_connect(), mysql_fetch(), mysql_query(), etc, in your code know that the code is outdated and very soon you might have to review it and keep it up to date.

Since you are used to MySQL, it may be a bit complicated to migrate to PDO. I am writing this guide in order to help you do the common tasks you are used to with MySQL such as dDatabase connection, Create database entries, read from the database, update data, or delete data, also known as CRUD(Create, Read, Update, Delete).

What's PDO?

PDO stands for PHP Data Objects. Every time you need to persist data in your application, you need a database. Naturally, PHP provides you some drivers that could help you do that depending on the type of database you have, for example, mysqli, pgsql, mssql. If your application basically uses one these databases, you can just go with the appropriate driver. For example, you can use Mysqli if your application uses MySQL database.

In the other hand, it may become complex when you work with more databases or you need to connect to an Oracle Database, in that case, you might need something more: PDO.

PDO is a database connection abstraction library. It has been built into PHP since the version 5.1.0. With PDO you have more flexibility to interact with many different types of databases management systems. Imagine it has been possible since PHP 5.1, and we carried on using MySQL until PHP 5.5, shock!

For the purpose of this tutorial, I will be working with only MySQL database. So let's not waste more time, let's see how we do things with PDO.

Database connection

<?php

// Database connection settings
$hostname       = 'localhost';
$username       = 'user'; 
$userpwd        = 'somepassword'; 
$databasename   = 'mydatabase';

// WITH MySQL

  // db handler. Remember this variable, it will be used further in this tutorial.
  $dbh = null;

  $dbh = mysql_connect($hostname, $username, $userpwd);
  if( !$dbh )
  {
    die('Connection failed!' . mysql_error());
  }
  echo 'Connected successfully';
  // mysql_close($dbh);
  mysql_select_db($databasename, $dbh) or die('Could not select database.');

// WITH PDO

  try {
    $dsn = 'mysql:host=localhost';
    $dbh = new PDO($dsn; dbname=$databasename', $username, $userpwd);
  } catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
  }

C of the CRUD

<?php

// WITH MYSQL

$query =  mysql_query(' INSERT INTO table ( column1, culumn2, culumnN ) VALUES($value1, $value2, $valueN) ');

// WITH PDO

  $query = $dbh->prepare(' INSERT INTO table ( column1, culumn2, culumnN ) VALUES(?,?,?) ');
  $query->execute( Array($value1, $value2, $valueN) );

R of the CRUD

<?php

// WITH MYSQL

  $query = mysql_query(' SELECT * FROM table ');

  // This is one option amongst many
  while( $data = mysql_fetch_array() ){
    // do something with data
  }

// WITH PDO

  $query = $dbh->prepare(' SELECT * FROM table ');
  $statement = $query->execute();
  // This is one option among many
  $data = fetchAll(PDO::FETCH_OBJ); //PDO::FETCH_OBJ to ask PDO that I want my data to be formated as Objects or you could do $statement->setFetchMode(PDO::FETCH_OBJ);

U of the CRUD

<?php

// WITH MYSQL

  $query = mysql_query(' UPDATE table SET culumn1 = ''.$value1.'', culumn2 = ''.$value2.''  WHERE id = ''.$id.'' ');

// WITH PDO

  $query = $dbh->prepare(' UPDATE table SET culumn1 = ?, culumn2 = ? WHERE id = ? ');
  $query->execute(Array($culumn1, $culumn2, $id));

D of the CRUD

// WITH MYSQL

  $query = mysql_query(' DELETE FROM table WHERE id = ''.$id.'' ');

// WITH PDO

  $query = $dbh->prepare('DELETE FROM table WHERE id = ? ');
  $query->execute(Array($id));

Some cool stuff you can do with PDO

Close the connection
```
$dbh = null;
```

Get the last insert ID

// notice the use of $dbh
$dbh->lastInsertId();

Connect to SQLite database

$pdo = new PDO('sqlite:/path/to/db/users.db');

Bottom line

There are so many ways of handling your databases with PDO. This tutorial should be seen as a transition guide from MySQL to PDO. It's not about MySQLi, though MySQLi is the improved version of MySQL. So, instead of using PDO you can also use MySQLi even though it's advised to use PDO.

Also, remember with PDO you can write procedural or Object Oriented. I used the second option in this tutorial.

To have a deep look at how to use PDO in a full project have a look at this tutorial: How to add Sign Up, Sign In and User Dashboard to a web site? or this one.

Thank you for taking a bit of your time reading, if you like it please share or comment.

Last updated 2024-01-11 UTC