安装

要使用此类,首先将MysqliDb.php导入到项目中。

require_once ('MysqliDb.php'); 

使用 composer 安装

composer require joshcam/mysqli-database-class:dev-master

初始化

使用utf8编码进行简单初始化默认设置:

$db = new MysqliDb ('host', 'username', 'password', 'databaseName');  

高级初始化:

$db = new MysqliDb (Array (  
                'host' => 'host',  
                'username' => 'username',   
                'password' => 'password',  
                'db'=> 'databaseName',  
                'port' => 3306,  
                'prefix' => 'my_',  
                'charset' => 'utf8'));  

表前缀,端口和数据库字符集参数是可选的。 如果不设置字符集,则将其设置为null

也可以重用已连接的mysqli对象:

$mysqli = new mysqli ('host', 'username', 'password', 'databaseName');  
$db  new MysqliDb ($mysqli);  

如果在创建对象期间没有设置表前缀,则可以稍后通过单独的调用进行设置:

$db->setPrefix ('my_');  

如果您需要从其他类或函数中使用已经创建的mysqliDb对象

function init () {  

    // db staying private here  

    $db = new MysqliDb ('host', 'username', 'password', 'databaseName');  

}  

...  

function myfunc () {  

    // obtain db object created in init  ()  

    $db = MysqliDb::getInstance();  

    ...  

}  

对象映射

dbObject.php是建立在mysqliDb之上的对象映射库,用于提供模型表示功能。dbObject手册了解更多信息

插入查询
简单例子

$data = Array ("login" => "admin",  

               "firstName" => "John",  

               "lastName" => 'Doe'  

);  

$id = $db->insert ('users', $data);  

if($id)  

    echo 'user was created. Id=' . $id;  

插入功能使用

$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;  

else  

    echo 'insert failed: ' . $db->getLastError();  

插入与重复密钥更新

$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);  

一次插入多个数据集

$data = Array(  

    Array ("login" => "admin",  

        "firstName" => "John",  

        "lastName" => 'Doe'  

    ),  

    Array ("login" => "other",  

        "firstName" => "Another",  

        "lastName" => 'User',  

        "password" => "very_cool_hash"  

    )  

);  

$ids = $db->insertMulti('users', $data);  

if(!$ids) {  

    echo 'insert failed: ' . $db->getLastError();  

} else {  

    echo 'new users inserted with following id\'s: ' . implode(', ', $ids);  

}  

如果所有数据集只有相同的键,可以简化

$data = Array(  

    Array ("admin", "John", "Doe"),  

    Array ("other", "Another", "User")  

);  

$keys = Array("login", "firstName", "lastName");  

  

$ids = $db->insertMulti('users', $data, $keys);  

if(!$ids) {  

    echo 'insert failed: ' . $db->getLastError();  

} else {  

    echo 'new users inserted with following id\'s: ' . implode(', ', $ids);  

}  

替换查询

Replace() 方法实现与insert() 相同的API;

更新查询

$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';  

else  

    echo 'update failed: ' . $db->getLastError();  

update() 也支持limit参数:



$db->update ('users', $data, 10);  

// Gives: UPDATE users SET ... LIMIT 10  

选择查询

在任何select / get函数调用之后,$count或者返回的行存储在$count变量中

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

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

或选择自定义列集。功能也可以使用

$cols = Array ("id", "name", "email");  

$users = $db->get ("users", null, $cols);  

if ($db->count > 0)  

    foreach ($users as $user) {   

        print_r ($user);  

    }  

或选择一行

$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";  


或选择一个列值或函数结果


$count = $db->getValue ("users", "count(*)");  

echo "{$count} users found";  


从多行中选择一个列值或函数结果:

 
$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;  

插入数据您还可以将.CSV或.XML数据加载到特定的表中。要插入.csv数据,请使用以下语法:

$path_to_file = "/home/john/file.csv";  

$db->loadData("users", $path_to_file);  

这将在 /home/john/(john的主目录)文件夹中加载一个名为file.csv的.csv文件。您还可以附加可选的选项数组。有效的选项有:       
Array(     
    "fieldChar" => ';',  // Char which separates the data   

    "lineChar" => '\r\n',    // Char which separates the lines     

    "linesToIgnore" => 1 // Amount of lines to ignore at the beginning of the import   

);  
使用它们
$options = Array("fieldChar" => ';', "lineChar" => '\r\n', "linesToIgnore" => 1);  

$db->loadData("users", "/home/john/file.csv", $options);  

###插入XML要将XML数据加载到表中,可以使用loadXML方法。 语法是smarse到loadData语法。

$path_to_file = "/home/john/file.xml";  

$db->loadXML("users", $path_to_file);  

您还可以添加可选参数。有效参数:

Array(  
    "linesToIgnore" => 0,        // Amount of lines / rows to ignore at the beginning of the import  
    "rowTag"    => ""    // The tag which marks the beginning of an entry  
)  

用法:

$options = Array("linesToIgnore" => 0, "rowTag"  => ""):  

$path_to_file = "/home/john/file.xml";  

$db->loadXML("users", $path_to_file, $options);  

Pagination使用paginate() 而不是get() 来获取分页结果

$page = 1;  

// set page limit to 2 results per page. 20 by default  

$db->pageLimit = 2;  

$products = $db->arraybuilder()->paginate("products", $page);  

echo "showing $page out of " . $db->totalPages;  

结果转换/地图

而不是得到一个纯数组的结果,可能会得到一个关联数组与一个所需的关键。 如果在get() 中只能设置2个字段来获取,那么在其余的情况下,方法会在array($k => $v)和array($k => array($v,$v)) 中返回结果。



$user = $db->map ('login')->ObjectBuilder()->getOne ('users', 'login, id');  
Array     
(  
    [user1] => 1  
)  

$user = $db->map ('login')->ObjectBuilder()->getOne ('users', 'id,login,createdAt');  

Array  
(  
    [user1] => stdClass Object  

        (  
            [id] => 1  

            [login1] => user1  

            [createdAt] => 2015-10-22 22:27:53  
        )  
)  

定义返回类型

MysqliDb可以返回3种不同格式的结果:Array数组,对象数组和Json字符串。 选择一个返回类型使用ArrayBuilder(),ObjectBuilder() 和JsonBuilder() 方法。 请注意,ArrayBuilder() 是默认的返回类型

// Array return type  

$= $db->getOne("users");  

echo $u['login'];  

// Object return type  

$u = $db->ObjectBuilder()->getOne("users");  

echo $u->login;  

// Json return type  

$json = $db->JsonBuilder()->getOne("users");  

运行SQL查询

$users = $db->rawQuery('SELECT * from users where id >= ?', Array (10));  

foreach ($users as $user) {  

    print_r ($user);  

}  

为了避免长时间,如果检查有几个帮助函数使用原始查询选择结果:

获取1行结果:

$user = $db->rawQueryOne ('select * from users where id=?', Array(10));  

echo $user['login'];  

// Object return type  

$user = $db->ObjectBuilder()->rawQueryOne ('select * from users where id=?', Array(10));  

echo $user->login;  

获取1列值作为字符串:

$password = $db->rawQueryValue ('select password from users where id=? limit 1', Array(10));  

echo "Password is {$password}";  

NOTE: for a rawQueryValue() to return string instead of an array 'limit 1' should be added to the end of the query.  

从多行获取1列值:

$logins = $db->rawQueryValue ('select login from users limit 10');  

foreach ($logins as $login)  

    echo $login;  

高级例子:

$params = Array(1, 'admin');  

$users = $db->rawQuery("SELECT id, firstName, lastName FROM users WHERE id = ? AND login = ?", $params);  

print_r($users); // contains Array of returned rows          

// will handle any SQL query  

$params = Array(10, 1, 10, 11, 2, 10);  

$q = "(   
    SELECT a FROM t1  

        WHERE a = ? AND B = ?  

        ORDER BY a LIMIT ?  

) UNION (  

    SELECT a FROM t2   

        WHERE a = ? AND B = ?  

        ORDER BY a LIMIT ?  

)";  

$resutls = $db->rawQuery ($q, $params);  

print_r ($results); // contains Array of returned rows  

Where / Having Methods
where(),orWhere(),having() 和orHaving() 方法允许您指定查询的位置和条件。 where() 支持的所有条件都由 having() 支持。

警告:为了使用列与列比较,只有原始条件应用作列名或函数不能作为绑定变量传递。

带有变量的常量==运算符:

$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';  

正则==运算符与列到列比较:

// WRONG  

$db->where ('lastLogin', 'createdAt');  

// CORRECT  

$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;  

BETWEEN / NOT BETWEEN:

$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  

IN / NOT IN:

$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');  

OR CASE

$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  

还可以使用raw条件:

$db->where ("id != companyId");  

$db->where ("DATE(createdAt) = DATE(lastLogin)");  

$results = $db->get("users");  

或原始条件与变量:

$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';  

找到匹配的总行数。 简单分页例:

$offset = 10;  

$count = 15;  

$users = $db->withTotalCount()->get('users', Array ($offset, $count));  

echo "Showing {$count} from {$db->totalCount}";  

关键字查询

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);  

// GIVES: INSERT LOW_PRIORITY INTO table ...  


    $db->setQueryOption ('FOR UPDATE')->get ('users');  
    
    // GIVES: SELECT * FROM USERS FOR UPDATE;  

还可以使用一系列关键字:

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

// GIVES: INSERT LOW_PRIORITY IGNORE INTO table ...  

在SELECT查询中也可以使用相同的方式关键字:


$db->setQueryOption ('SQL_NO_CACHE');  

$db->get("users");  

// GIVES: SELECT SQL_NO_CACHE * FROM USERS;  

或者,您可以使用方法链接多次调用它,而无需通过以下方式引用对象:

$results = $db  

    ->where('id', 1)  

    ->where('login', 'admin')  

    ->get('users');  

删除查询

$db->where('id', 1);  

if($db->delete('users')) echo 'successfully deleted'; 


排列排序方法

$db->orderBy("id","asc");  

$db->orderBy("login","Desc");  

$db->orderBy("RAND ()");  

$results = $db->get('users');  

// Gives: SELECT * FROM users ORDER BY id ASC,login DESC, RAND ();  

按值排列示例:

$db->orderBy('userGroup', 'ASC', array('superuser', 'admin', 'users'));  

$db->get('users');  

// Gives: SELECT * FROM users ORDER BY FIELD (userGroup, 'superuser', 'admin', 'users') ASC;  

如果您使用setPrefix() 功能,并且需要在orderBy() 方法中使用表名,请确保使用``转义表名。

$db->setPrefix ("t_");  

$db->orderBy ("users.id","asc");  

$results = $db->get ('users');  

// WRONG: That will give: SELECT * FROM t_users ORDER BY users.id ASC;  

$db->setPrefix ("t_");  

$db->orderBy ("`users`.id", "asc");  

$results = $db->get ('users');  

// CORRECT: That will give: SELECT * FROM t_users ORDER BY t_users.id ASC;  

分组方法

$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方

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");  

$db->where("u.id", 6);  

$products = $db->get ("products p", null, "u.name, p.productName");  

print_r ($products);  

JOIN条件
将AND条件添加到JOIN语句

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");  

$db->joinWhere("users u", "u.tenantID", 5);  

$products = $db->get ("products p", null, "u.name, 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)  

将OR条件添加到JOIN语句

$db->join("users u", "p.tenantID=u.tenantID", "LEFT");  

$db->joinOrWhere("users u", "u.tenantID", 5);  

$products = $db->get ("products p", null, "u.name, 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)  

属性共享

Its 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 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

[php] view plain copy

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

$sq->get ("users");  

子查询 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)


子查询 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());  

子查询 joins:

$usersQ = $db->subQuery ("u");  

$usersQ->where ("active", 1);  

$usersQ->get ("users");  

  

$db->join($usersQ, "p.userId=u.id", "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 p.userId=u.id;  

EXISTS / NOT EXISTS条件

$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方法

一个方便的函数返回TRUE,如果至少存在一个满足指定的where条件的元素,在此之前调用“where”方法。

$db->where("user", $user);  

$db->where("password", md5($password));  

if($db->has("users")) {  

    return "You are logged";  

} else {  

    return "Wrong user/password";  

}  

Helper方法

断开与数据库的连接:

$db->disconnect();  

重新连接,以防万一mysql连接死机:

if (!$db->ping())  
    $db->connect()  

获取最后执行的SQL查询:请注意,函数返回SQL查询仅用于调试目的,因为它的执行最有可能由于char变量周围缺少引号而失败。

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

检查表是否存在:

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

mysqli_real_escape_string() 包装:

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

交易助手

请记住,事务正在处理innoDB表。 回滚事务如果插入失败:

$db->startTransaction();  

...  

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


错误助手

执行查询后,您可以选择是否有错误。 您可以获取MySQL错误字符串或上次执行查询的错误代码。

$db->where('login', 'admin')->update('users', ['firstName' => 'Jack']); 
 
if ($db->getLastErrno() === 0)  
    echo 'Update succesfull';  
else  
    echo 'Update failed. Error: '. $db->getLastError();  

查询执行时间基准

要跟踪查询执行时间,应该调用setTrace()函数。

$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']);  
$db->get("users");  
$db->get("test");  
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  
    )  

表锁定要锁定表,可以使用lock方法和setLockMethod。 以下示例将锁定表用户以进行写访问。

$db->setLockMethod("WRITE")->lock("users");  

调用另一个 ->lock() 将删除第一个锁。 你也可以使用

$db->unlock();  

解锁以前锁定的表。 要锁定多个表,可以使用数组。 例:

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

这将锁定表用户,并仅记录读取访问权限。 确保你以后使用 *unlock() 或你的表将保持锁定!

最后修改:2019 年 04 月 11 日
如果觉得我的文章对你有用,请随意赞赏