Back to blog home

PHP PDO API Database Class

In my first blog I would like to share with you a very useful piece of code that will save you a lot of time while developing your website. Writing SQL queries is very simple task, but sometimes it wastes a lot of time. That's why people started to write their own PHP classes to manage their queries and save that wasted time and effort. As a web developer, I wrote my own classes that saved my time and let me write queries efficiently and quickly.

First of all I use PDO API instead of the popular MySQLi API. Although MySQLi performs insignificantly faster for non-prepared statements and for prepared ones, I chose PDO API because it supports more Database Drivers. So, I can freely migrate my database to any driver without the hassle of updating my queries.

You will notice that I represent All the quires as arrays so you can edit them easily later. Also the return of the select query is an array of the requested rows.

Download the class from GitHub PODDb.class.php
 

1. Installation

To utilize this class, first import db.class.php into your project, and require it.

require_once('PDODb.class.php');                   
 

2. Initialization

Simple initialization with utf8 charset set by default:

$db = new PDODb('host', 'username', 'password', 'databaseName']);          
 

3. Insert Query

Simple example

$bind = array('fname' => 'Tarek', 'lname' => 'Abdel Aziz', 
		'email' => 'tarek@aucegypt.edu');
$query_array = array(
    'table' => '`messages`',
    'field' => array_keys($bind),
    'bind' => $bind
);
$lastInsertID = $db->insert($query_array);

Note that the method $db->insert() returns the Id of the last inserted query and all queries are represented as arrays.

Insert multiple datasets at once

$bind = array();
foreach($_POST['country'] as $countryId){
    $bind += array("cityId".$countryId => $cityId);
    $bind += array("countryId".$countryId => $countryId);
    $i++;
}
$query_array = array(
    'table' => '`city_country`',
    'field' => array('cityId', 'countryId'),
    'bind' => $bind
);
$lastInsertID = $db->insert($query_array);
 

4. Update Query

Update multiple columns.

$bind = array('id' => 4, 'fname' => 'Tarek', 'lname' => 'Nabil', 
		'email' => 'tarek@aucegypt.edu');
$query_array = array(
    'table' => '`user`',
    'field' => array_keys($bind),
    'where' => '`id` = :id',
    'bind' => $bind
);
$db->update($query_array);
 

5. Select Query

Select all from table blog by id

$query_array = array(
    'field' => '*',
    'from' => '`blog`',
    'where' => '`id` = :id',
    'bind' => array("id" => $blog_id)
);
$data = $db->select($query_array);

Full properties of select query.

$query_array = array(
    'field' => '*',
    'from' => '`movie` INNER JOIN `movie_genre`',
    'on' => '`movie`.`id` = `movie_genre`.`movieId`',
    'where' => '`genreID` = :genreId',
    'group' => '`Id`'
    'order' => '`Id` DESC',
    'limit' => '10',
    'bind' => array("genreId" => $genreId)
);
$data = $db->select($query_array);

You don't need to write the whole query again.

$query_array = array(
    'field' => '*',
    'from' => '`movie`',
    'where' => '`id` = :movieId',
    'bind' => array("movieId" => 1)
);
$data = $db->select($query_array);

// just edit bind value

$query_array['bind'] = array("movieId" => 3);

$data = $db->select($query_array);

The results of select statement will be returned as array with fields' names as array keys and values as array values.

$query_array = array(
    'field' => 'id, title',
    'from' => '`song`',
    'limit' => 5,
    'order' => '`id` ASC'
);
$songs = $db->select($query_array);
print_r($songs);
	
/*---------- OUTPUT ----------*/

Array ( [0] => Array ( [id] => 1 [title] => Shape Of You )
	[1] => Array ( [id] => 2 [title] => Castle On The Hill ) 
        [2] => Array ( [id] => 3 [title] => Touch ) 
        [3] => Array ( [id] => 4 [title] => September Song ) 
        [4] => Array ( [id] => 5 [title] => Chained To The Rhythm ) 
      )
 

6. Delete Query

All queries are represented as arrays.

$query_array = array(
    'from' => '`user`',
    'where' => '`userId` = :id',
    'bind' => array('id' => 15)
);
$db->delete($query_array);
 

7. Exist Query

All queries are represented as arrays.

$query_array = array(
    'from' => '`cast`',
    'where' => 'name = :name',
    'bind' => array('name' => 'Matt Damon')
);
if($db->exists($query_array)){
	// record exists
}else{
	// record dosn't exist
}
 

8. Print Query

You can print query to test it against errors by setting $db->setPrintQuery(true) to true.

$query_array = array(
    'field' => '*',
    'from' => '`album`',
    'where' => '`genre` = :genre',
    'order' => '`id` DESC',
    'bind' => array('genre' => 'Jazz')
);
$db->setPrintQuery(true);
$data = $db->select($query_array);

/*------------ OUTPUT ------------*/

Query:
SELECT * From `album` WHERE `genre` = :genre ORDER BY `id` DESC 

Bind Array:
Array ( [genre] => Jazz ) 
 

9. Close Connection

$db->destruct();
 

If you still want to stick with MySQLi API, don't worry, here is a great link for PHP-MySQLi-Database-Class that I recommend developed by Josh Campbell on Git Hub. Josh, thankfully, wrapped a PHP MySQL class to utilize MySQLi and prepared statements.

Tarek Abdel Aziz

Software Developer & UX/UI Designer

Senior Web Developer specializing in back end development. Experienced with all stages of the development cycle for dynamic web projects. Well-versed in numerous programming languages. Strong background in project management and customer relations.

0 Comments

Important Notice:

I'm not responsible for the comments written by any user which has his/her initials in a circle.
I'm only responsible for the comments I wrote which has my logo in a circle.

Leave a comment