支持我
这个软件是我在空闲时间开发的,如果有人支持我,我会很高兴。
每个人的时间都应该很宝贵,所以请考虑捐赠。
安装
要使用此类,首先将 MysqliDb.php 导入到您的项目中,并需要它。
require_once ('MysqliDb.php');
使用作曲家安装
也可以通过composer安装库
<span style="background-color:var(--bgColor-muted, var(--color-canvas-subtle))"><span style="color:#1f2328"><span style="color:var(--fgColor-default, var(--color-fg-default))"><span style="background-color:var(--bgColor-muted, var(--color-canvas-subtle))"><code>composer require thingengineer/mysqli-database-class:dev-master
</code></span></span></span></span>
初始化
默认设置 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'));
表前缀、端口和数据库字符集参数是可选的。如果没有字符集应该设置charset,则将其设置为null
也可以重用已经连接的 mysqli 对象:
$mysqli = new mysqli ('host', 'username', 'password', 'databaseName');
$db = new MysqliDb ($mysqli);
如果在对象创建期间未设置表前缀,则可以稍后通过单独的调用进行设置:
$db->setPrefix ('my_');
如果与 mysql 的连接被断开,Mysqlidb 将尝试自动重新连接到数据库一次。要禁用此行为,请使用
$db->autoReconnect = false;
如果您需要从另一个类或函数使用已创建的 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();
...
}
多个数据库连接
如果需要连接多个数据库,请使用以下方法:
$db->addConnection('slave', Array (
'host' => 'host',
'username' => 'username',
'password' => 'password',
'db'=> 'databaseName',
'port' => 3306,
'prefix' => 'my_',
'charset' => 'utf8')
);
要选择数据库,请使用connection()方法
$users = $db->connection('slave')->get('users');
对象映射
dbObject.php 是一个构建在 mysqliDb 之上的对象映射库TP钱包下载,用于提供模型表示功能。请参阅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()
还支持限制参数:
$db->update ('users', $data, 10);
// Gives: UPDATE users SET ... LIMIT 10
选择查询
任何 select/get 函数调用后,金额或返回的行都存储在 $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);
// LOAD DATA ...
您可以指定使用 LOCAL DATA而不是DATA:
$options = Array("fieldChar" => ';', "lineChar" => '\r\n', "linesToIgnore" => 1, "loadDataLocal" => true);
$db->loadData("users", "/home/john/file.csv", $options);
// LOAD DATA LOCAL ...
插入XML
要将 XML 数据加载到表中,可以使用方法loadXML。该语法与 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" => "<user>" // The tag which marks the beginning of an entry
)
用法:
$options = Array("linesToIgnore" => 0, "rowTag" => "<user>"):
$path_to_file = "/home/john/file.xml";
$db->loadXML("users", $path_to_file, $options);
分页
使用 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
[login] => user1
[createdAt] => 2015-10-22 22:27:53
)
)
定义返回类型
MysqliDb 可以以 3 种不同的格式返回结果:数组的数组、对象的数组和 Json 字符串。要选择返回类型,请使用 ArrayBuilder()、ObjectBuilder() 和 JsonBuilder() 方法。请注意,ArrayBuilder() 是默认返回类型
// Array return type
$u= $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);
}
为了避免长时间的 if 检查,有几个辅助函数可以处理原始查询选择结果:
获取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 ?
)";
$results = $db->rawQuery ($q, $params);
print_r ($results); // contains Array of returned rows
哪里/有方法
where()
、orWhere()
、having()
和orHaving()
方法允许您指定查询的 where 和having 条件。 where() 支持的所有条件也由having() 支持。
警告:为了使用列与列的比较,只能将原始 where 条件用作列名称,否则函数不能作为绑定变量传递。
带变量的常规 == 运算符:
$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;
之间/不之间:
$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'
空比较:
$db->where ("lastName", NULL, 'IS NOT');
$results = $db->get("users");
// Gives: SELECT * FROM users where lastName IS NOT NULL
喜欢比较:
$db->where ("fullName", 'John%', 'like');
$results = $db->get("users");
// Gives: SELECT * FROM users where fullName like 'John%'
您还可以使用原始 where 条件:
$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}";
查询关键词
添加低优先级|延迟 |高优先级 | IGNORE 和 INSERT ()、REPLACE ()、GET ()、UPDATE ()、DELETE() 方法或 FOR UPDATE | 的其余 mysql 关键字将共享模式锁定到 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;
您可以选择使用方法链来多次调用 where ,而无需一遍又一遍地引用您的对象:
$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;
加入方法
通过tenantID使用LEFT 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);
// Gives: SELECT u.name, p.productName FROM products p LEFT JOIN users u ON p.tenantID=u.tenantID WHERE u.id = 6
加盟条件
在 join 语句中添加 AND 条件
$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.name, p.productName FROM products p LEFT JOIN users u ON (p.tenantID=u.tenantID AND u.tenantID = 5)
在 join 语句中添加 OR 条件
$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)
财产共享
也可以复制属性
$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 customers WHERE agentId = 10 AND active = 1
子查询
子查询初始化
子查询 init 没有在插入/更新/where 中使用的别名,例如。 (从用户中选择*)
$sq = $db->subQuery();
$sq->get ("users");
具有指定在 JOIN 中使用的别名的子查询。例如。 (从用户中选择*) sq
$sq = $db->subQuery("sq");
$sq->get ("users");
选择中的子查询:
$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)
插入中的子查询:
$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());
连接中的子查询:
$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;
存在/不存在条件
$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')
有方法
一个方便的函数,如果至少存在一个满足在该元素之前调用“where”方法指定的 where 条件的元素,则返回 TRUE。
$db->where("user", $user);
$db->where("password", md5($password));
if($db->has("users")) {
return "You are logged";
} else {
return "Wrong user/password";
}
辅助方法
断开与数据库的连接:
$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);
<span style="background-color:var(--bgColor-muted, var(--color-canvas-subtle))"><span style="color:#1f2328"><span style="color:var(--fgColor-default, var(--color-fg-default))"><span style="background-color:var(--bgColor-muted, var(--color-canvas-subtle))"><code> [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
)
</code></span></span></span></span>
表锁定
要锁定表,您可以将lock方法与setLockMethod一起使用。以下示例将锁定表用户的写访问权限。
$db->setLockMethod("WRITE")->lock("users");
调用另一个->lock()将删除第一个锁。您还可以使用
$db->unlock();
解锁之前锁定的表。要锁定多个表,可以使用数组。例子:
$db->setLockMethod("READ")->lock(array("users", "log"));
这将锁定表用户并仅进行读访问。确保之后使用* unlock(),否则你的表将保持锁定状态!