Download the PHP package hiper/hp-database without Composer

hp-database

MysqliDb -- Simple MySQLi wrapper and object mapper with prepared statements

Table of Contents

Initialization
Objects mapping
Insert Query
Update Query
Select Query
Delete Query
Insert Data
Insert XML 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
Table Locking

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 mysqli object:

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

If connection to mysql will be dropped Mysqlidb will try to automatically reconnect to the database once. To disable this behavoir use

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

Multiple database connection

If you need to connect to multiple databases use following method:

To select database use connection() method

Objects mapping

dbObject.php is an object mapping library built on top of mysqliDb to provide model representation functionality. See dbObject manual for more information

Insert Query

Simple example

Insert with functions use

Insert with on duplicate key update

Insert multiple datasets at once

If all datasets only have the same keys, it can be simplified

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:

Insert Data

You can also load .CSV or .XML data into a specific table. To insert .csv data, use the following syntax:

This will load a .csv file called file.csv in the folder /home/john/ (john's home directory.) You can also attach an optional array of options. Valid options are:

Attach them using

You can specify to use LOCAL DATA instead of DATA:

Insert XML

To load XML data into a table, you can use the method loadXML. The syntax is smillar to the loadData syntax.

You can also add optional parameters. Valid parameters:

Usage:

Pagination

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

Result transformation / map

Instead of getting an pure array of results its possible to get result in an associative array with a needed key. If only 2 fields to fetch will be set in get(), method will return result in array($k => $v) and array ($k => array ($v, $v)) in rest of the cases.

Defining a return type

MysqliDb can return result in 3 different formats: Array of Array, Array of Objects and a Json string. To select a return type use ArrayBuilder(), ObjectBuilder() and JsonBuilder() methods. Note that ArrayBuilder() is a default return type

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:

LIKE 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 and 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

Join Conditions

Add AND condition to join statement

Add OR condition to join statement

Properties sharing

It 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

Disconnect from the database:

Reconnect in case mysql connection died:

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:

mysqli_real_escape_string() 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.

Query execution time benchmarking

To track query execution time setTrace() function should be called.

Table Locking

To lock tables, you can use the lock method together with setLockMethod. The following example will lock the table users for write access.

Calling another ->lock() will remove the first lock. You can also use

to unlock the previous locked tables. To lock multiple tables, you can use an array. Example:

This will lock the tables users and log for READ access only. Make sure you use *unlock() afterwards or your tables will remain locked!