帮酷LOGO
0 0 评论
文章标签:STA  Mysqli  WRAP  prepared-statements  UTIL  换行    Prepared Statement  
Wrapper for a PHP MySQL class, which utilizes MySQLi and prepared statements.

  • 源代码名称:PHP-MySQLi-Database-Class
  • 源代码网址:http://www.github.com/joshcam/PHP-MySQLi-Database-Class
  • PHP-MySQLi-Database-Class源代码文档
  • PHP-MySQLi-Database-Class源代码下载
  • Git URL:
    git://www.github.com/joshcam/PHP-MySQLi-Database-Class.git
  • Git Clone代码到本地:
    git clone http://www.github.com/joshcam/PHP-MySQLi-Database-Class
  • Subversion代码到本地:
    $ svn co --depth empty http://www.github.com/joshcam/PHP-MySQLi-Database-Class
    Checked out revision 1.
    $ cd repo
    $ svn up trunk
    
  • MysqliDb带有准备语句的简单mysqli包装器和对象映射器






















    支持我

    这个软件是在我的空闲时间开发的,如果有人支持我,我会很高兴。

    每个人都有价值,所以请考虑捐赠。

    paypal捐赠

    安装

    要使用这个类,首先要将MysqliDb.php导入项目,并需要它。

    require_once ('MysqliDb.php');

    使用Composer安装

    也可以通过Composer安装库

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

    初始化

    默认情况下,使用utf8字符集进行简单初始化:

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

    高级初始化:

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

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

    此外,还可以重用已连接的MySQLi对象:

    $mysqli=newmysqli ('host', 'username', 'password', 'databaseName');$db=newMysqliDb ($mysqli);

    如果创建对象时没有设置表前缀,则可以稍后使用单独的调用将它设置为:

    $db->setPrefix ('my_');

    如果删除与MySQL的连接MySQLIDB将尝试自动重新连接数据库一次。 禁用此behavoir使用

    $db->autoReconnect=false;

    如果需要从另一个类或函数中获取已创建的mysqliDb对象,请使用

    functioninit () {// db staying private here$db=newMysqliDb ('host', 'username', 'password', 'databaseName'); }...functionmyfunc () {// 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之上的对象映射库,用于提供模型表示功能。请参见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;elseecho'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';elseecho'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 ($usersas$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 ($loginsas$login)echo$login;

    插入数据

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

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

    这将在(john home 目录)的文件夹/home/john/中加载名为file.csv的.CSV文件,你还可以附加可选的选项数组,有效选项包括:

    Array("fieldChar"=>';', // Char which separates the data"lineChar"=>'rn', // Char which separates the lines"linesToIgnore"=>1// Amount of lines to ignore at the beginning of the import);

    使用附件

    $options=Array("fieldChar"=>';', "lineChar"=>'rn', "linesToIgnore"=>1);$db->loadData("users", "/home/john/file.csv", $options);// LOAD DATA ...

    你可以指定使用本地数据,而不是使用数据:

    $options=Array("fieldChar"=>';', "lineChar"=>'rn', "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个要获取的字段,就方法将在其余情况下返回数组($k = > $v ) 和数组($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] =>stdClassObject ( [id] =>1 [login] =>user1 [createdAt] =>2015-10-2222:27:53 ))

    定义返回类型

    MysqliDb可以返回3种不同格式的结果: 数组的数组,对象数组和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 ($usersas$user) {print_r ($user);}

    要避免长时间检查,可以使用helper函数来处理原始查询选择结果:

    获取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: fora rawQueryValue() toreturnstringinsteadofanarray'limit 1'shouldbeaddedtotheendofthequery.

    从多行中获取1列值

    $logins=$db->rawQueryValue ('select login from users limit 10');foreach ($loginsas$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方法

    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 比较:

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

    like 比较:

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

    你也可以使用原始条件:

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

    查询关键字

    添加LOW PRIORITY |DELAYED |HIGH PRIORITY | IGNORE和其余的mysql关键字为INSERT(),REPLACE(),GET(),UPDATE(),DELETE()方法或UPDATE |将SHARE模式锁定为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 ...

    同样,关键字也可以用于选择查询中:

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

    ORDER BY值例子:

    $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表产品,由tenantID提供的带有LEFT JOIN的表用户,

    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条件

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

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

    共享属性

    还可以复制属性

    $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

    子查询

    子查询初始化

    inserts/updates/where中没有别名的子查询init例如,(select * from users )

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

    带有指定要在Join中使用的别名的子查询。 Eg . (select * from users) 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;

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

    一个方便的函数,如果存在至少一个满足where条件的元素,则返回TRUE,该元素在此之前调用"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查询:

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

    表锁定

    若要锁定表,可以将锁方法与setLockMethod一起使用,下面的例子将锁定表用户以进行写入访问。

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

    你还可以使用

    $db->unlock();

    取消锁定以前锁定的表,若要锁定多个表,可以使用数组,例如:

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

    这将锁定表的用户和日志以便只读访问,确保你以后使用*unlock(),否则你的表将保持锁定!



    文章标签:STA  PHP  WRAP  换行    PRE  UTIL  Mysqli  

    Copyright © 2011 HelpLib All rights reserved.    知识分享协议 京ICP备05059198号-3  |  如果智培  |  酷兔英语