What is the PHP PDO Database class?

How to Use this Class?

How it Works?

Data Definition Language

Conclusion

What is the PHP PDO Database class?

The PHP PDO Database class was created to simplify performing common database queries using PDO extension.

The idea of the class is to make it easy to use, shorten the time of development of database applicatuons, provides means to easily perform data management queries (DML) as well database definition queries (DDL), establishes only one connection per database using the singleton design pattern.

How to Use this Class?

Declare connection constants

The class uses some constants to define connection parameters.

<?php
   define(HOST,     'localhost');
   define(DBNAME,   'your_database_name');
   define(USERNAME, 'database_username');
   define(PASSWORD, 'database_password');
   define(CHARSET,  'character_set'); // utf8 RECOMMENDED

Include the class in your project

<?php
    include_once('DB.class.php');

Create the instance of the class

<?php
   //  Get The Instance
   $db = DB::get();

How it Works?

Performing SELECT queries

// get all -> return object
$db->query("SELECT * FROM table_name")->results();

// get first row -> return object
$db->query("SELECT * FROM table_name WHERE field = value")->first();

Inserting table records

Lets suppose you want to insert a record with the fields id, name, username, email in the users table.

<?php
   $userInformation = [
      'id'       => 1,
       'name'     => 'Mohammad',
       'username' => 'Anzawi',
       'email'    => 'email@example.com',
   ];

   $db->table('users')->insert( $userInformation );

Update table records

Lets suppose you want to update name for Mohammad in the users table. You can update the record specifying an id, username or any field.

<?php
    $newValues = [
        'name' => 'Ahmed',
    ];
    $db->->table('users')->update($newValues, ['id', '=', 1]);

or

<?php
    $newValues = [
        'name' => 'Ahmed',
    ];
    $db->table('users')->update($newValues, ['username', '=', 'Mohammad']);

Note that you can update more than one field in the same array

$newValues = [
    'name' => 'Ahmed',
    'username' => 'plapla',
    'email' => 'pla@plalpa.com',
    // ...
];

$db->table('users')->update($newValues, ['username', '=', 'Mohammad']);

The update method looks like this update->table('tablename')->update($newVaules = [], $whereCondition = []);

$newVaules = [field name , new value]
$whereCondition = [field name , operator , value]

Delete table records

<?php
   $db->table( 'users' )->delete( ['id', '>=', 1] );

You can set the where condition like for the update queries.

delete('users', [field name , operator , value]);

<?php
   $db->table( 'users' )->delete(['name', 'LIKE', 'mohammad)]);

Note that you can insert, update and delete users query method.

<?php
   $db->query("INSERT INTO users (name) VALUES (?)", ['mohammad']);

   $db->query("UPDATE  users SET (name =?) WHERE id=1", ['AIi']);

Get First X Number of Rows of a Table

The method getFirst accepts 2 parameters. The first is the number of rows which by default is 10 and the second is the where condition.

<?php

    $db->table('table_name')->getFirst(5, $where);

Get Last X Number Rows of a Table

The method getLast accepts 2 parameters. The first is the number of rows by default 10 and the second is the where condition.

<?php

    $db->table('table_name')->getLast(5, $where);

How to use the returned data

$allUsers = $db->query("SELECT * FROM table_name")->results();

foreach($allUsers as $singleUser) {
    echo $singleUser->name;
    echo "<br>";
    echo $singleUser->username;
}

// name and username in example are fields from table

If you want to fetch the results as arrays, go to line 102 replace PDO::FETCH_OBJ -> to -> PDO::FETCH_ASSOC

Get Count of Result Rows

$count = $db->count(); echo $count;

Show if there were any errors

$error = $db->error();
if(!$error) {
    echo "No Errors";
} else {
    echo "There is error";
}

Note all methods return false if any error happens and true if all things went alright, except for queries when no error happens it returns an array so you can do something like this:

<?php 
// delete
if($db->table('users')->delete(['id', '>=', 1])) {
    echo "Deleted Successfully";
} else {
    echo "error Delete";
}

// insert
if($db->table('users')->insert(['name' => 'pla pla'])) {
    echo "Inserted Successfully";
} else {
    echo "error Insert";
}

// update 
if($db->table('users')->update(['name' => 'pla pla'])) {
    echo "Updated Successfully";
} else {
    echo "error Update";
}

// get data
if($users = $db->table('users')->query("select * from users")->results()) {
    print_r($users);
} else {
    echo "error Select From table";

Data Definition Language

Create Table

$db = DB::get();

$db->table('my_new_table_name')->schema('schema as array')->create();

For example:

$db = DB::get();

$db->table('students')->schema([
    'id' => 'increments',
    'name' => 'string:255 | not_null',
    'number' => 'int|unsigned';
])->create();

The SQL statement generated for this is:

CREATE TABLE students ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL, name VARCHAR(255) NOT NULL, number INT UNSIGNED )

Note that setting 'id' => 'increments' means the id column will be an integer, primary key, auto increment not null and unsigned.

ADD Constraints

'number' => 'int|my_constraint|other_constraint|more_constraint';

So the first one is a column type and the others are other constraints.

Default Value

Set default value type:

'number' => 'int|unsigned|default:222';
'name' => 'int|unsigned|default:hello-this-a-default-value';

// note : the character (-) replaced with white space

Full Example

$db = DB::get();

$schema = [
    'id' => 'increments',
    'username' => 'string:100|not_null',
    'full_name' => 'string:255|defualt:no-name',
    'joind' => 'timestamps',
    'user_email' => 'string:100|not_null',
];

$db->table('users')->schema($schema)->create();

Add a Column

$db->table('target_table')->alterSchema('condetions is array')->alter();
$db->table('table')->alterSchema(['add', 'column_name', 'type'])->alter();

For example:

$db->table('users')->alterSchema(['add', 'last_login', 'date'])->alter();

Rename a Column

$db->table('target_table')->alterSchema('condetions is array')->alter();
$db->table('table')->alterSchema(['rename', 'column_name', 'new_column_name' ,'type'])->alter();

For example:

$db->table('users')->alterSchema(['rename', 'last_login', 'last_session', 'date'])->alter();

Change Column Type

$db->table('table')->alterSchema(['modify', 'column_name', 'new_type'])->alter();

For example:

$db->table('users')->alterSchema(['modify', 'full_name', 'text'])->alter();

Drop a Column

$db->table('table')->alterSchema(['drop', 'column_name'])->alter();

For example:

$db->table('users')->alterSchema(['drop', 'full_name'])->alter();

Conclusion

The PHP PDO Database class is a simple class to simplify the work of those that want to access database table records and well create or modify the schema of database tables. Despite the examples are set to work on MySQL, it can work with other types of databases.

If you liked this article, share it with your PHP developer friends. If you have questions post a comment below.

	Facebook		Gmail
	Hotmail		StackOverflow
	GitHub		Yahoo

Simple Tutorial on using MySQL with PDO that works with PHP 7 - PHP PDO database class package blog

Contents