PHP Classes
PHP Classes
elePHPant
Icontem

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

Recommend this page to a friend!
  All package blogs All package blogs   PHP PDO database class PHP PDO database class   Blog PHP PDO database class package blog   RSS 1.0 feed RSS 2.0 feed   Blog Simple Tutorial on us...  
  Post a comment Post a comment   See comments See comments (2)   Trackbacks (0)  

Author:

Posted on:

Package: PHP PDO database class

Most PHP applications use MySQL databases but since PHP 7 was introduced the old MySQL extension was discontinued. Developers need to migrate either to using MySQLi or PDO.

Using PDO makes your code usable also with other types of databases besides MySQL.

Read this tutorial to learn how to perform simple common database queries as well perform table schema management operations using the PHP PDO database class.




Contents

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.




You need to be a registered user or login to post a comment

1,434,723 PHP developers registered to the PHP Classes site.
Be One of Us!

Login Immediately with your account on:

FacebookGmail
HotmailStackOverflow
GitHubYahoo


Comments:

1. PHP 5 Compatibility - MT Lars (2017-01-18 19:13)
Which versions?... - 1 reply
Read the whole comment and replies




  Post a comment Post a comment   See comments See comments (2)   Trackbacks (0)  
  All package blogs All package blogs   PHP PDO database class PHP PDO database class   Blog PHP PDO database class package blog   RSS 1.0 feed RSS 2.0 feed   Blog Simple Tutorial on us...