Author: mohammad anzawi
Viewers: 2,622
Last month viewers: 329
Package: PHP PDO database class
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 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
<?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:
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
Login Immediately with your account on:
Comments:
3. Easier to understand - Anjilik Walson (2019-08-01 07:20)
Development of database applications... - 0 replies
Read the whole comment and replies
2. gj - Kamran Karimi (2018-05-31 07:07)
ghj... - 0 replies
Read the whole comment and replies
1. PHP 5 Compatibility - MT Lars (2017-01-18 19:13)
Which versions?... - 1 reply
Read the whole comment and replies