Download the PHP package koolob/mysqli-database-class without Composer
On this page you can find all versions of the php package koolob/mysqli-database-class. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download koolob/mysqli-database-class
More information about koolob/mysqli-database-class
Files in koolob/mysqli-database-class
Package mysqli-database-class
Short Description PHP MySQL Wrapper and object mapper which utilizes MySQLi and prepared statements
License GPL-3.0-or-later
Informations about the package mysqli-database-class
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
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.
Installation
To utilize this class, first import MysqliDb.php into your project, and require it.
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!