PHP MySQLi 封装类完整版(汉化使用说明)
来源:互联网 发布:北京软件测试培训 编辑:程序博客网 时间:2024/06/09 14:24
MySQLi 封装类下载
安装
要使用此类,首先将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 5foreach ($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 [login] => 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
$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() 或你的表将保持锁定!
好吧!翻译的不好,我承认使用Google翻译出来的!外语老鸟可以直接Github看说明
- PHP MySQLi 封装类完整版(汉化使用说明)
- PHP MySQLi 封装类完整版
- php的Mysqli封装类
- php中mysqli扩展封装数据库类conn(mysqli实例)
- php封装的mysqli类完整实例
- 封装php连接mysqli类 封装增删改
- 封装php连接mysqli类 封装增删改
- MySQL----MySQLi 类封装
- mysqli封装类
- PHP mysql与mysqli事务使用说明 分享
- PHP mysqli的prepare准备语句使用说明
- 【php高级】MySQLI类
- PHP中mysqli类
- PHP mysqli数据库操作类
- 基于mysqli封装的数据库类
- php mysqli面向对象封装mysql数据库常用操作
- php封装msql 常用函数 增删改查 mysqli
- php mysqli
- C#设计模式(六)原型模式(Prototype Pattern)
- keepalived配置文件
- exercise3
- 编程中遇到的有趣的问题
- 关于perl中中文乱码的解决办法
- PHP MySQLi 封装类完整版(汉化使用说明)
- 继承的基本概念(Java面向对象高级篇)
- 《软件研发成本度量规范释义》(第2版)
- Docker学习总结(23)——Docker搭建大规模测试环境的实践
- 51Nod-1496-最小异或和
- 真实记录我入门学习Linux系统的经历
- Java-数组的三大排序方法(冒泡排序,选择排序,插入排序)
- Monkey 介绍
- fastdfs-java-client 使用指南