PHP PDO Database Class

SunDB is a PHP PDO database class that utilizes PDO and prepared statements (MySQL, MariaDB, MSSQL, SQLite, etc).

Technical Document: https://deepwiki.com/msbatal/PHP-PDO-Database-Class

Initialization
Security
Insert Query
Update Query
Delete Query
Transactions
Select Query
Join Method
Where Method
Ordering Method
Limiting Method
Pagination Method
Count Method
Grouping Method
Having Method
Raw SQL Queries
Backup Database
Maintaining Database
Helper Methods

Installation

To utilize this class, first import SunDB.php into your project, and require it. SunDB requires PHP 5.5+ to work.

require_once ('SunDB.php');

Initialization

Simple initialization with utf8 charset set by default:

$db = new SunDB(null, 'host', 'username', 'password', 'dbName');

Advanced initialization:

$db = new SunDB(['driver' => 'mysql',
                 'host' => 'host',
                 'port' => 3306,
                 'dbname'=> 'dbName',
                 'username' => 'username', 
                 'password' => 'password',
                 'charset' => 'utf8'
                ]);

Url, Port and Charset parameters are optional.

You can use mysql, mssql, and sqlite driver types for Driver parameter.

If you want use MSSQL:

$db = new SunDB(['driver' => 'mssql',
                 'host' => 'serverName',
                 'dbname' => 'dbName',
                 'username' => 'username',
                 'password' => 'password'
                ]);

If you want use SQLite:

$db = new SunDB(['driver' => 'sqlite',
                 'url' => 'fileName.sqlite'
                ]);

Also it's possible to use already connected PDO object:

$pdo = new PDO('mysql:dbname=sample;host=localhost', 'username', 'password');
$db = new SunDB($pdo);

Security

SunDB always uses native (non-emulated) prepared statements (PDO::ATTR_EMULATE_PREPARES = false) on every driver. This closes a known SQL injection technique that only affects PDO's emulated mode (details).
Table and column names can never be sent as bound parameters, so SunDB validates every identifier it receives (table names, column names, insert/update keys, join columns) before using it — an invalid identifier throws an exception instead of reaching the database.
The comparison operator passed to where(), orWhere() and join() is checked against an allowlist.
A few parameters are intentionally NOT validated, because they're meant to carry raw SQL fragments: the $columns parameter of select(), the $value parameter of having(), and whereRaw(). Never pass user input into these directly.

Insert Query

Simple example with keys and values

$data = [
    'column1' => 'Value 1',
    'column2' => 'Value 2',
    'column3' => 'Value 3'
];
$insert = $db->insert('tableName', $data)->run();
//Gives: INSERT INTO tableName (column1, column2, column3) VALUES ('Value 1', 'Value 2', 'Value3');

if ($insert) {
    echo 'Record inserted successfully! ID: '.$db->lastInsertId();
}

Simple example only with values

$insert = $db->insert('tableName', [NULL, 'Value 2', 'Value 3'])->run();
//Don't forget to send parameters in same order created in the table
//Gives: INSERT INTO tableName VALUES (NULL, 'Value 2', 'Value3');

if ($insert) {
    echo 'Record inserted successfully! ID: '.$db->lastInsertId();
}

Insert with functions use

$data = [
    'column1' => 'Value 1',
    'column2' => true, //or false
    'column3' => 'Value 3',
    'column4' => $db->func('sha1', 'stringText'),
    //Supported functions date, sha1, md5, base64, ceil, floor, round, etc.
    //'columnX' => $db->func('date', 'now'),
    //'columnX' => $db->func('date', 'Y-m-d'),
    //'columnX' => $db->func('date', 'H:i:s'),
    //Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
];

$insert = $db->insert('tableName', $data)->run();
//Gives: INSERT INTO tableName (...) VALUES (...);

//if ($insert) {
if ($db->rowCount() > 0) {
    echo 'Record inserted successfully! ID: '.$db->lastInsertId();
} else {
    echo 'Insert failed!';
}

Insert query returns true or false result. Also, you can get the affected (inserted) rows by using rowCount() method. If you need last inserted record's id, you may use lastInsertId() method.

Insert multiple rows in a single query

$rows = [
    ['column1' => 'Value 1', 'column2' => 'Value 2'],
    ['column1' => 'Value 3', 'column2' => 'Value 4']
];
$insert = $db->insertMany('tableName', $rows)->run();
//Gives: INSERT INTO tableName (column1, column2) VALUES ('Value 1', 'Value 2'), ('Value 3', 'Value 4');

Update Query

$data = [
    'column1' => 'Value 1',
    'column2' => 'Value 2',
    'column3' => 'Value 3'
];

$update = $db->update('tableName', $data)
             ->where('column', 'value', '=')
             ->run();
//Gives: UPDATE tableName SET column1='Value 1', column2='Value 2', column3='Value 3' WHERE column=value;

//if ($update) {
if ($db->rowCount() > 0) {
    echo $db->rowCount().' records updated successfully!';
} else {
    echo 'Update failed!';
}

update() also support order by and limit parameters:

$update = $db->update('tableName', $data)
             ->where('column', 'value', '>')
             ->orderBy('column', 'desc')
             ->limit(2)
             ->run();
//Gives: UPDATE tableName SET ... WHERE column > value ORDER BY column DESC LIMIT 2;

Update query returns true or false result. Also, you can get the affected (updated) rows by using rowCount() method. If you don't make changes in column contents (sending same values to db), rowCount() will return a Zero (0) value. So, please think twice about controlling the results; true/false or affected row count?

Delete Query

$delete = $db->delete('tableName')
             ->where('column', 'value', '>')
             ->run();
//Gives: DELETE FROM tableName WHERE column > value;

//if ($delete) {
if ($db->rowCount() > 0) {
    echo $db->rowCount().' records deleted successfully!';
} else {
    echo 'Delete failed!';
}

Delete query returns true or false result. Also, you can get the affected (deleted) rows by using rowCount() method.

Transactions

Wrap multiple queries so they either all succeed or all get rolled back:

$db->beginTransaction();
try {
    $db->insert('orders', $orderData)->run();
    $db->insert('orderItems', $itemData)->run();
    $db->commit();
} catch (Exception $e) {
    $db->rollback();
    echo 'Failed: ' . $e->getMessage();
}

Select Query

After any select function calls returned rows is stored in an array/object

$select = $db->select('tableName')->run(); //contains an array/object of all records
//Gives: SELECT * FROM tableName;

$select = $db->select('tableName')->limit(4)->run(); //contains an array/object of X records
//Gives: SELECT * FROM tableName LIMIT 4;

or select with custom columns set

$cols = ['column1', 'column2', 'column3'];
$select = $db->select('tableName', $cols)->run();
//Gives: SELECT column1,column2,column3 FROM tableName;

if ($select) {
    foreach ($select as $rows) { 
        print_r($rows);
    }
}

or select just one row

$select = $db->select('tableName')->where('column', 'value', '=')->first()->run();
//Gives: SELECT * FROM tableName WHERE column='value';

echo $select['column'];

or select one column value or function result

$select = $db->select('tableName', ['column'])->limit(1)->first()->run();
//Gives: SELECT column FROM tableName LIMIT 1;

echo $select['column'];

$select = $db->select('tableName', ['count(*) as total'])->first()->run();
//Gives: SELECT count(*) as total FROM tableName;

echo $select['total'];

or you may use these two alternatives to select a single row

$select = $db->select('tableName')->run();
echo $select[0]['column']; //alternative 1 (use '0' key between query variable and column key)

$select = $db->select('tableName')->run();
foreach ($select as $row) { 
    echo $row['column']; //alternative 2 (use 'foreach' like a multiple selection)
}

If you need a single row and don't want to use a loop function, don't forget to use first(), last() or random() methods before run() method.

Join Method

join() method allows you to specify join condition of the query, with inner, left, right or full as the join type. Column names accept a qualified table.column format. This method is supported by only select query.

$select = $db->select('tableName', ['tableName.column1', 'otherTable.column2'])
             ->join('otherTable', 'tableName.id', '=', 'otherTable.tableName_id', 'inner')
             ->run();
//Gives: SELECT tableName.column1,otherTable.column2 FROM tableName INNER JOIN otherTable ON tableName.id = otherTable.tableName_id;

Shortcuts for the join type (type parameter defaults to inner on join() itself):

$select = $db->select('tableName')->leftJoin('otherTable', 'tableName.id', '=', 'otherTable.tableName_id')->run();
//Gives: ... LEFT JOIN otherTable ON tableName.id = otherTable.tableName_id;

$select = $db->select('tableName')->rightJoin('otherTable', 'tableName.id', '=', 'otherTable.tableName_id')->run();
//Gives: ... RIGHT JOIN otherTable ON tableName.id = otherTable.tableName_id;

$select = $db->select('tableName')->innerJoin('otherTable', 'tableName.id', '=', 'otherTable.tableName_id')->run();
//Gives: ... INNER JOIN otherTable ON tableName.id = otherTable.tableName_id;

The table name and both columns are always identifier-validated, and the comparison operator is restricted to =, !=, <>, <, >, <=, >= — this method never accepts raw/unvalidated SQL, so it's safe to build even with user-influenced pieces (e.g. a whitelisted dropdown).

You can chain where() on a joined query using the same qualified column format:

$select = $db->select('tableName')
             ->leftJoin('otherTable', 'tableName.id', '=', 'otherTable.tableName_id')
             ->where('tableName.column', 'value', '=')
             ->run();

Where Method

where() or orWhere() methods allow you to specify where condition of the query. This method is supported by select, update and delete queries, and uses prepared statements (also bind parameters).

$select = $db->select('tableName')
             ->where('column', 'value', '=')
             ->run();
//Gives: SELECT * FROM tableName WHERE column='value';

$select = $db->select('tableName')
             ->where('column1', 'value1', '>')
             ->where('column2', 'value2', '<')
             ->run();
//Gives: SELECT * FROM tableName WHERE column1 > value1 AND column2 < value2;

LIKE / NOT LIKE:

$select = $db->select('tableName')
             ->where('column', 'value', 'like'); //or with wildcard (%value, value%, %value%)
             ->run();
//Gives: SELECT * FROM tableName WHERE column LIKE 'value';

$select = $db->select('tableName')
             ->where('column', 'value', 'not like'); //or with wildcard (%value, value%, %value%)
             ->run();
//Gives: SELECT * FROM tableName WHERE column NOT LIKE 'value';

BETWEEN / NOT BETWEEN:

$select = $db->select('tableName')
             ->where('column', [date("Y-m-d"), date("Y-m-d")], 'between');
             ->run();
//Gives: SELECT * FROM tableName WHERE column BETWEEN 'YYYY-mm-dd' AND 'YYYY-mm-dd';

$select = $db->select('tableName')
             ->where('column', [date("Y-m-d"), date("Y-m-d")], 'not between');
             ->run();
//Gives: SELECT * FROM tableName WHERE column NOT BETWEEN 'YYYY-mm-dd' AND 'YYYY-mm-dd';

IN / NOT IN:

$select = $db->select('tableName')
             ->where('column', [1, 2, 3, 'a', 'b'], 'in');
             ->run();
//Gives: SELECT * FROM tableName WHERE column IN (1, 2, 3, 'a', 'b');

$select = $db->select('tableName')
             ->where('column', [1, 2, 3, 'a', 'b'], 'not in');
             ->run();
//Gives: SELECT * FROM tableName WHERE column NOT IN (1, 2, 3, 'a', 'b');

OR CASE

$select = $db->select('tableName')
             ->where('column1', 'value1', '=')
             ->orWhere('column2', 'value2', '=')
             ->run();
//Gives: SELECT * FROM tableName WHERE column1='value1' OR column2='value2';

AND and OR CASE

$select = $db->select('tableName')
             ->where('column1', 'value1', '=')
             ->where('column2', 'value2', '=')
             ->orWhere('column3', 'value3', '=')
             ->run();
//Gives: SELECT * FROM tableName WHERE column1='value1' AND column2='value2' OR column3='value3';

$select = $db->select('tableName')
             ->orWhere('column1', 'value1', '=')
             ->where('column2', 'value2', '=')
             ->where('column3', 'value3', '=')
             ->run();
//Gives: SELECT * FROM tableName WHERE column1='value1' AND column2='value2' AND column3='value3';

Also you can use raw where conditions, or the explicit whereRaw() method (same thing, but the name makes it obvious it's unvalidated):

$select = $db->select('tableName')
             ->where('column1 >= value1 AND column2 < value2');
             ->run();
//Gives: SELECT * FROM tableName WHERE column1 >= value1 AND column2 < value2;

$select = $db->select('tableName')
             ->whereRaw('column1 >= value1 AND column2 < value2')
             ->run();
//Gives: SELECT * FROM tableName WHERE column1 >= value1 AND column2 < value2;

IS NULL / IS NOT NULL:

$select = $db->select('tableName')->whereNull('column')->run();
//Gives: SELECT * FROM tableName WHERE column IS NULL;

$select = $db->select('tableName')->whereNotNull('column')->run();
//Gives: SELECT * FROM tableName WHERE column IS NOT NULL;

Column names passed to where()/orWhere()/whereNull()/whereNotNull() support the qualified table.column format (for use after a join()). The comparison operator passed as the 3rd parameter of where()/orWhere() is validated against an allowlist (=, !=, <>, <, >, <=, >=, like, not like, between, not between, in, not in) — anything else throws an exception.

Check if at least one row matches, without fetching the result set:

if ($db->select('tableName')->where('column', 'value', '=')->exists()) {
    echo 'Found!';
}

Find the total number of rows affected:

$total = $db->rowCount();
echo "{$total} rows affected.";

Find the total number of rows in table:

$total = $db->tableCount('tableName');
echo "{$total} rows found.";

Ordering Method

orderBy() method allows you to specify order by condition of the query. This method is supported by select, update and delete queries.

$select = $db->select('tableName', ['column1', 'column2'])
             ->orderBy('column1', 'asc')
             ->orderBy('column2', 'desc')
             ->orderBy("RAND ()")
             ->run();
//Gives: SELECT column1,column2 FROM tableName ORDER BY column1 ASC, column2 DESC, RAND ();

Column names accept the qualified table.column format too (for use after a join()).

Limiting Method

limit() method allows you to specify limit condition of the query. This method is supported by select, update and delete queries.

$select = $db->select('tableName', ['column'])->limit(1)->run();
//Gives: SELECT column FROM tableName LIMIT 1;

echo $select['column'];

$select = $db->select('tableName', ['column'])->limit(5, 4)->run();
//Gives: SELECT column FROM tableName LIMIT 5,4;

echo $select['column'];

$delete = $db->delete('tableName')
             ->where('column', 'value', '>')
             ->orderBy('column', 'desc')
             ->limit(5)
             ->run();
//Gives: DELETE FROM tableName WHERE column > value ORDER BY column DESC limit 5;

//if ($delete) {
if ($db->rowCount() > 0) {
    echo $db->rowCount().' records deleted successfully!';
} else {
    echo 'Delete failed!';
}

Pagination Method

paginate() is a convenience wrapper over limit() that also counts the total number of matching rows (ignoring the page limit) up front, available via totalCount(). This method is supported by only select query.

$select = $db->select('tableName')
             ->where('column', 'value', '=')
             ->orderBy('id', 'asc')
             ->paginate(1, 20) //page 1, 20 rows per page
             ->run();

echo $db->totalCount().' total matching rows.';
foreach ($select as $row) {
    print_r($row);
}

paginate() uses count() internally (see below) — the two share the same total.

Count Method

count() runs the same filtered COUNT(*) query paginate() uses internally, but standalone — for when you just need "how many rows match" without fetching any rows or paginating. This method is supported by only select query.

$total = $db->select('tableName')->where('column', 'value', '=')->count();
echo $total.' matching rows.';

It respects whatever where()/join() conditions were built on the query so far (orderBy()/limit() are ignored, since they don't affect a count). It also updates totalCount(), so $db->select(...)->where(...)->count() and $db->totalCount() afterward return the same number.

Grouping Method

groupBy() method allows you to specify group by condition of the query. This method is supported by only select query.

$select = $db->select('tableName')
             ->groupBy('column')
             ->run();
//Gives: SELECT * FROM tableName GROUP BY column;

Column names accept the qualified table.column format too (for use after a join()).

Also you can use functions with groupBy() method:

$select = $db->select('tableName', 'count(column) as count')
             ->groupBy('column', 'DAYNAME')
             ->orderby('count(column)', 'desc')
             ->run();
//Gives: SELECT count(column) as count FROM tableName GROUP BY DAYNAME(column) ORDER BY count(column) DESC;

Having Method

having() method allows you to specify having condition of the query. This method is supported by only select query.

$select = $db->select('tableName')
             ->groupBy('column')
             ->having('column >= value')
             ->run();
//Gives: SELECT * FROM tableName GROUP BY column HAVING column >= value;

Raw SQL Queries

You can use raw queries for more complex SQL statements, or not specified functions and methods in this class.

Execute raw SQL queries:

$select = $db->rawQuery("select column1,column2 from tableName where (column1='value1' && column2='value2')")
             ->limit(2)
             ->run();
//Gives: SELECT column1,column2 FROM tableName WHERE (column1='value1' && column2='value2') LIMIT 2;

foreach ($select as $rows) {
    print_r($rows);
}

or use prepared statements (bind parameters):

$select = $db->rawQuery("select column1,column2 from tableName where (column1=? && column2=?)", ['value1', 'value2'])
             ->limit(2)
             ->run();
//Gives: SELECT column1,column2 FROM tableName WHERE (column1='value1' && column2='value2') LIMIT 2;

foreach ($select as $rows) {
    print_r($rows);
}

If you use Insert, Update or Delete commands, Raw SQL query returns true or false result. If you use Select command, it will return an array/object containing the selected row(s).

Backup Database

Download the whole database (tables and records) as an SQL file:

$db->backup('fileName', 'save');
//File: fileName.sql

Don't forget to use this code on an empty page. Otherwise, you can see an HTML page into an SQL file.

Show the whole database (tables and records) as an SQL query:

$db->backup(null, 'show');

Download/Show the whole database (with exclude some tables) as an SQL query:

$db->backup(null, 'save', ['table1', 'table2']); //or 'show' action

Maintaining Database

Analyze, check, optimize and repair the whole database (tables and records):

$db->maintenance();

or

$maintenance = $db->maintenance();
if ($maintenance) {
    echo 'Maintenance successfully!';
} else {
    echo 'Maintenance failed!';
}

Helper Methods

Get last executed SQL query:

$db->showQuery();

This code will print the last executed query to the screen.

Name		Name	Last commit message	Last commit date
Latest commit History 80 Commits
test		test
LICENSE		LICENSE
README.md		README.md
SunDB.php		SunDB.php

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Repository files navigation

PHP PDO Database Class

Table of Contents

Installation

Initialization

Security

Insert Query

Update Query

Delete Query

Transactions

Select Query

Join Method

Where Method

Ordering Method

Limiting Method

Pagination Method

Count Method

Grouping Method

Having Method

Raw SQL Queries

Backup Database

Maintaining Database

Helper Methods

About

Releases

Packages

Contributors

Languages

Folders and files

Latest commit

History

Repository files navigation

PHP PDO Database Class

Table of Contents

Installation

Initialization

Security

Insert Query

Update Query

Delete Query

Transactions

Select Query

Join Method

Where Method

Ordering Method

Limiting Method

Pagination Method

Count Method

Grouping Method

Having Method

Raw SQL Queries

Backup Database

Maintaining Database

Helper Methods

About

Topics

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages