MysqliDb – Simple MySQLi wrapper and object mapper with prepared statements
To utilize this class, first import MysqliDb.php into your project, and require it.
It is also possible to install library via composer
Simple initialization with utf8 charset set by default:
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 you need to get already created mysqliDb object from another class or function use
dbObject.php is an object mapping library built on top of mysqliDb to provide model representation functionality. See dbObject manual for more information
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() method implements same API as insert();
update() also support limit parameter:
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
###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:
###Pagination Use paginate() instead of get() to fetch paginated result
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.
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
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:
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:
Also you can use raw where conditions:
Or raw condition with variables:
Find the total number of rows matched. Simple pagination example:
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:
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
Join table products with table users with LEFT JOIN by tenantID
Add AND condition to join statement
Add OR condition to join statement
Its is also possible to copy properties
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
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.
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:
Please keep in mind that transactions are working on innoDB tables. Rollback transaction if insert fails:
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.
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!