Download the PHP package tommyknocker/pdo-database-class without Composer

PDO-database-class

PHP PDO Wrapper which utilizes PDO and prepared statements

Table of Contents

• Initialization
• Insert Query
• Update Query
• Select Query
• Delete Query
• Pagination
• Running raw SQL queries
• Query Keywords
• Where Conditions
• Order Conditions
• Group Conditions
• Properties Sharing
• Joining Tables
• Subqueries
• EXISTS / NOT EXISTS condition
• Has method
• Helper Methods
• Transaction Helpers
• Error Helpers

Support Me

This software is developed during my free time and I will be glad if somebody will support me.

Everyone's time should be valuable, so please consider donating.

Donate with paypal

Installation

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

Installation with composer

It is also possible to install library via composer

Initialization

Simple initialization with utf8 charset set by default:

Advanced initialization:

table prefix, port and database charset params are optional. If no charset should be set charset, set it to null

Also it is possible to reuse already connected pdo object:

If no table prefix were set during object creation its possible to set it later with a separate call:

If you need to get already created pdo object from another class or function use

Insert Query

Simple example

Insert with functions use

Insert with on duplicate key update

Replace Query

Replace() method implements same API as insert();

Update Query

update() also support limit parameter:

Select Query

After any select/get function calls amount or returned rows is stored in $count variable

or select with custom columns set. Functions also could be used

or select just one row

or select one column value or function result

select one column value or function result from multiple rows:

You may use php 5.5+ generator feature with PDODb get(), rawQuery() methods just call useGenerator(true) method

Example:

Pagination

Use paginate() instead of get() to fetch paginated result

Defining a return type

To select a return type use setReturnType() method.

Running raw SQL queries

To avoid long if checks there are couple helper functions to work with raw query select results:

Get 1 row of results:

Get 1 column value as a string:

Get 1 column value from multiple rows:

More advanced examples:

Where / Having Methods

where(), orWhere(), having() and orHaving() methods allows you to specify where and having conditions of the query. All conditions supported by where() are supported by having() as well.

WARNING: In order to use column to column comparisons only raw where conditions should be used as column name or functions cant be passed as a bind variable.

Regular == operator with variables:

Regular == operator with column to column comparison:

BETWEEN / NOT BETWEEN:

IN / NOT IN:

OR CASE

NULL comparison:

Also you can use raw where conditions:

Or raw condition with variables:

Find the total number of rows matched. Simple pagination example:

Query Keywords

To add LOW PRIORITY | DELAYED | HIGH PRIORITY | IGNORE and the rest of the mysql keywords to INSERT (), REPLACE (), GET (), UPDATE (), DELETE() method or FOR UPDATE | LOCK IN SHARE MODE into SELECT ():

Also you can use an array of keywords:

Same way keywords could be used in SELECT queries as well:

Optionally you can use method chaining to call where multiple times without referencing your object over an over:

Delete Query

Ordering method

Order by values example:

If you are using setPrefix () functionality and need to use table names in orderBy() method make sure that table names are escaped with ``.

Grouping method

Join table products with table users with LEFT JOIN by tenantID

JOIN method

Properties sharing

Its is also possible to copy properties

Subqueries

Subquery init

Subquery init without an alias to use in inserts/updates/where Eg. (select * from users)

A subquery with an alias specified to use in JOINs . Eg. (select * from users) sq

Subquery in selects:

Subquery in inserts:

Subquery in joins:

EXISTS / NOT EXISTS condition

Has method

A convenient function that returns TRUE if exists at least an element that satisfy the where condition specified calling the "where" method before this one.

Helper methods

Get last executed SQL query: Please note that function returns SQL query only for debugging purposes as its execution most likely will fail due missing quotes around char variables.

Check if table exists:

pdo::quote() wrapper:

Transaction helpers

Please keep in mind that transactions are working on innoDB tables. Rollback transaction if insert fails:

Error helpers

After you executed a query you have options to check if there was an error. You can get the MySQL error string or the error code for the last executed query.