Install command:

composer require easyswoole/mysqli

Configuration parameter

/*################ MYSQL CONFIG ##################*/

'MYSQL' => [
//Database Configuration 
   'host'                 => '',//Database connection IP
   'user'                 => '',//Database username
   'password'             => '',//Database password
   'database'             => '',//Database
   'port'                 => '',//port
   'timeout'              => '30',//timeout
   'connect_timeout'      => '5',//Connection timeout
   'charset'              => 'utf8',//Character encoding
   'strict_type'          => false, //Open strict mode, returning fields will automatically be converted to digital types
   'fetch_mode'           => false,//Open fetch mode and use fetch/fetchAll line by line or get all result sets (version 4.0 or more) as PDO does.
   'alias'                => '',//Subquery aliases
   'isSubQuery'           => false,//Is it a subquery?
   'max_reconnect_times ' => '3',//Maximum number of reconnections

Adding or introducing in configuration files


$conf = new \EasySwoole\Mysqli\Config(\EasySwoole\EasySwoole\Config::getInstance()->getConf('MYSQL'));
$db = new Mysqli($conf);
$data = $db->get('test');//Get data from a table

Cooperative connection pool

Because it is a cooperative state, different instances must be used for each request. If a request comes in, it will be new, the request logic will be destroyed, the connection will be created each time, and then destroyed. This will cost a lot, so we can use connection pool to reuse the connection. Cooperative Connection Pool Tutorial; Concurrent connection pool demo;

Usage List


$data = Array ("login" => "admin",
               "firstName" => "John",
               "lastName" => 'Doe'
$id = $db->insert ('users', $data);
    echo 'user was created. Id=' . $id;

//Insert with functions use
$data = Array (
    'login' => 'admin',
    'active' => true,
    'firstName' => 'John',
    'lastName' => 'Doe',
    'password' => $db->func('SHA1(?)',Array ("secretpassword+salt")),
    // password = SHA1('secretpassword+salt')
    'createdAt' => $db->now(),
    // createdAt = NOW()
    'expires' => $db->now('+1Y')
    // expires = NOW() + interval 1 year
    // Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear

$id = $db->insert ('users', $data);
if ($id)
    echo 'user was created. Id=' . $id;
    echo 'insert failed: ' . $db->getLastError();

//Insert with on duplicate key update
$data = Array ("login" => "admin",
               "firstName" => "John",
               "lastName" => 'Doe',
               "createdAt" => $db->now(),
               "updatedAt" => $db->now(),
$updateColumns = Array ("updatedAt");
$lastInsertId = "id";
$db->onDuplicate($updateColumns, $lastInsertId);
$id = $db->insert ('users', $data);

Update Query

$data = Array (
    'firstName' => 'Bobby',
    'lastName' => 'Tables',
    'editCount' => $db->inc(2),
    // editCount = editCount + 2;
    'active' => $db->not()
    // active = !active;
$db->where ('id', 1);
if ($db->update ('users', $data))
    echo $db->count . ' records were updated';
    echo 'update failed: ' . $db->getLastError();

update() also support limit parameter:

$db->update ('users', $data, 10);
// Gives: UPDATE users SET ... LIMIT 10

Select Query

$users = $db->get('users'); //contains an Array of all users 
$users = $db->get('users', 10); //contains an Array 10 users

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

$cols = Array ("id", "name", "email");
$users = $db->get ("users", null, $cols);
if ($db->count > 0)
    foreach ($users as $user) { 
        print_r ($user);

or select just one row

$db->where ("id", 1);
$user = $db->getOne ("users");
echo $user['id'];

$stats = $db->getOne ("users", "sum(id), count(*) as cnt");
echo "total ".$stats['cnt']. "users found";

or select one column value or function result

$count = $db->getValue ("users", "count(*)");
echo "{$count} users found";

select one column value or function result from multiple rows:

$logins = $db->getValue ("users", "login", null);
// select login from users
$logins = $db->getValue ("users", "login", 5);
// select login from users limit 5
foreach ($logins as $login)
    echo $login;

Running raw SQL queries

$users = $db->rawQuery('SELECT * from users where id >= 100');
foreach ($users as $user) {
    print_r ($user);

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:

$db->where ('id', 1);
$db->where ('login', 'admin');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id=1 AND login='admin';
$db->where ('id', 1);
$db->having ('login', 'admin');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id=1 HAVING login='admin';

Regular == operator with column to column comparison:

$db->where ('lastLogin', 'createdAt');
$db->where ('lastLogin = createdAt');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE lastLogin = createdAt;
$db->where ('id', 50, ">=");
// or $db->where ('id', Array ('>=' => 50));
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE id >= 50;


$db->where('id', Array (4, 20), 'BETWEEN');
// or $db->where ('id', Array ('BETWEEN' => Array(4, 20)));

$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id BETWEEN 4 AND 20


$db->where('id', Array(1, 5, 27, -1, 'd'), 'IN');
// or $db->where('id', Array( 'IN' => Array(1, 5, 27, -1, 'd') ) );

$results = $db->get('users');
// Gives: SELECT * FROM users WHERE id IN (1, 5, 27, -1, 'd');


$db->where ('firstName', 'John');
$db->orWhere ('firstName', 'Peter');
$results = $db->get ('users');
// Gives: SELECT * FROM users WHERE firstName='John' OR firstName='peter'

NULL comparison:

$db->where ("lastName", NULL, 'IS NOT');
$results = $db->get("users");
// Gives: SELECT * FROM users where lastName IS NOT NULL

LIKE comparison:

$db->where ("fullName", 'John%', 'like');
$results = $db->get("users");
// Gives: SELECT * FROM users where fullName like 'John%'

Also you can use raw where conditions:

$db->where ("id != companyId");
$db->where ("DATE(createdAt) = DATE(lastLogin)");
$results = $db->get("users");

Or raw condition with variables:

$db->where ("(id = ? or id = ?)", Array(6,2));
$db->where ("login","mike")
$res = $db->get ("users");
// Gives: SELECT * FROM users WHERE (id = 6 or id = 2) and login='mike';

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

$offset = 10;
$count = 15;
$users = $db->withTotalCount()->get('users', Array ($offset, $count));
echo "Showing {$count} from {$db->totalCount}";

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 ():

$db->setQueryOption ('LOW_PRIORITY')->insert ($table, $param);
$db->setQueryOption ('FOR UPDATE')->get ('users');

Also you can use an array of keywords:

$db->setQueryOption (Array('LOW_PRIORITY', 'IGNORE'))->insert ($table,$param);

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

$db->setQueryOption ('SQL_NO_CACHE');

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

$results = $db
    ->where('id', 1)
    ->where('login', 'admin')

Delete Query

$db->where('id', 1);
if($db->delete('users')) echo 'successfully deleted';

Ordering method

$db->orderBy("RAND ()");
$results = $db->get('users');
// Gives: SELECT * FROM users ORDER BY id ASC,login DESC, RAND ();

Order by values example:

$db->orderBy('userGroup', 'ASC', array('superuser', 'admin', 'users'));
// Gives: SELECT * FROM users ORDER BY FIELD (userGroup, 'superuser', 'admin', 'users') ASC;

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

$db->setPrefix ("t_");
$db->orderBy ("","asc");
$results = $db->get ('users');
// WRONG: That will give: SELECT * FROM t_users ORDER BY ASC;

$db->setPrefix ("t_");
$db->orderBy ("`users`.id", "asc");
$results = $db->get ('users');
// CORRECT: That will give: SELECT * FROM t_users ORDER BY ASC;

Grouping method

$db->groupBy ("name");
$results = $db->get ('users');
// Gives: SELECT * FROM users GROUP BY name;

Join table products with table users with LEFT JOIN by tenantID

JOIN method

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->where("", 6);
$products = $db->get ("products p", null, ", p.productName");
print_r ($products);

Join Conditions

Add AND condition to join statement

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->joinWhere("users u", "u.tenantID", 5);
$products = $db->get ("products p", null, ", p.productName");
print_r ($products);
// Gives: SELECT  u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID AND u.tenantID = 5)

Add OR condition to join statement

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");
$db->joinOrWhere("users u", "u.tenantID", 5);
$products = $db->get ("products p", null, ", p.productName");
print_r ($products);
// Gives: SELECT  u.login, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID OR u.tenantID = 5)

Properties sharing

It is also possible to copy properties

$db->where ("agentId", 10);
$db->where ("active", true);

$customers = $db->copy ();
$res = $customers->get ("customers", Array (10, 10));
// SELECT * FROM customers where agentId = 10 and active = 1 limit 10, 10

$cnt = $db->getValue ("customers", "count(id)");
echo "total records found: " . $cnt;
// SELECT count(id) FROM users where agentId = 10 and active = 1


Subquery init

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

$sq = $db->subQuery();
$sq->get ("users");

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

$sq = $db->subQuery("sq");
$sq->get ("users");

Subquery in selects:

$ids = $db->subQuery ();
$ids->where ("qty", 2, ">");
$ids->get ("products", null, "userId");

$db->where ("id", $ids, 'in');
$res = $db->get ("users");
// Gives SELECT * FROM users WHERE id IN (SELECT userId FROM products WHERE qty > 2)

Subquery in inserts:

$userIdQ = $db->subQuery ();
$userIdQ->where ("id", 6);
$userIdQ->getOne ("users", "name"),

$data = Array (
    "productName" => "test product",
    "userId" => $userIdQ,
    "lastUpdated" => $db->now()
$id = $db->insert ("products", $data);
// Gives INSERT INTO PRODUCTS (productName, userId, lastUpdated) values ("test product", (SELECT name FROM users WHERE id = 6), NOW());

Subquery in joins:

$usersQ = $db->subQuery ("u");
$usersQ->where ("active", 1);
$usersQ->get ("users");

$db->join($usersQ, "", "LEFT");
$products = $db->get ("products p", null, "u.login, p.productName");
print_r ($products);
// SELECT u.login, p.productName FROM products p LEFT JOIN (SELECT * FROM t_users WHERE active = 1) u on;


$sub = $db->subQuery();
    $sub->where("company", 'testCompany');
    $sub->get ("users", null, 'userId');
$db->where (null, $sub, 'exists');
$products = $db->get ("products");
// Gives SELECT * FROM products WHERE EXISTS (select userId from users where company='testCompany')

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.

$db->where("user", $user);
$db->where("password", md5($password));
if($db->has("users")) {
    return "You are logged";
} else {
    return "Wrong user/password";

Helper methods

Disconnect from the database:


Reconnect in case mysql connection died:

if (!$db->ping())

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.

    echo "Last executed query was ". $db->getLastQuery();

Check if table exists:

    if ($db->tableExists ('users'))
        echo "hooray";

mysqli_real_escape_string() wrapper:

    $escaped = $db->escape ("' and 1=1");

Transaction helpers

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

if (!$db->insert ('myTable', $insertData)) {
    //Error while saving, cancel new record
} else {

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.

$db->where('login', 'admin')->update('users', ['firstName' => 'Jack']);

if ($db->getLastErrno() === 0)
    echo 'Update succesfull';
    echo 'Update failed. Error: '. $db->getLastError();

Query execution time benchmarking

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

$db->setTrace (true);
// As a second parameter it is possible to define prefix of the path which should be striped from filename
// $db->setTrace (true, $_SERVER['SERVER_ROOT']);
print_r ($db->trace);
    [0] => Array
            [0] => SELECT  * FROM t_users ORDER BY `id` ASC
            [1] => 0.0010669231414795
            [2] => MysqliDb->get() >>  file "/avb/work/PHP-MySQLi-Database-Class/tests.php" line #151

    [1] => Array
            [0] => SELECT  * FROM t_test
            [1] => 0.00069189071655273
            [2] => MysqliDb->get() >>  file "/avb/work/PHP-MySQLi-Database-Class/tests.php" line #152

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:

$db->setLockMethod("READ")->lock(array("users", "log"));

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

0 results matching ""

    No results matching ""