社区 学习 工具库 休闲
搜索
简体中文
首页 > 后端开发 > php教程 > php mysql PDO使用

php mysql PDO使用

WBOY
发布： 2016-08-08 09:29:24
原创
997 人浏览过

<span> 1</span> <span>php
</span><span> 2</span> <span>$dbh</span> = <span>new</span> PDO('mysql:host=localhost;dbname=access_control', 'root', ''<span>);  
</span><span> 3</span> <span>$dbh</span>->setAttribute(PDO::ATTR_ERRMODE, PDO::<span>ERRMODE_EXCEPTION);  
</span><span> 4</span> <span>$dbh</span>-><span>exec</span>('set names utf8'<span>); 
</span><span> 5</span> <span>/*</span><span>添加</span><span>*/</span>
<span> 6</span> <span>//</span><span>$sql = "INSERT INTO `user` SET `login`=:login AND `password`=:password"; </span>
<span> 7</span> <span>$sql</span> = "INSERT INTO `user` (`login` ,`password`)VALUES (:login, :password)";  <span>$stmt</span> = <span>$dbh</span>->prepare(<span>$sql</span>);  <span>$stmt</span>->execute(<span>array</span>(':login'=>'kevin2',':password'=>''<span>));  
</span><span> 8</span> <span>echo</span> <span>$dbh</span>-><span>lastinsertid();  
</span><span> 9</span> <span>/*</span><span>修改</span><span>*/</span>
<span>10</span> <span>$sql</span> = "UPDATE `user` SET `password`=:password WHERE `user_id`=:userId"<span>;  
</span><span>11</span> <span>$stmt</span> = <span>$dbh</span>->prepare(<span>$sql</span><span>);  
</span><span>12</span> <span>$stmt</span>->execute(<span>array</span>(':userId'=>'7', ':password'=>'4607e782c4d86fd5364d7e4508bb10d9'<span>));  
</span><span>13</span> <span>echo</span> <span>$stmt</span>-><span>rowCount(); 
</span><span>14</span> <span>/*</span><span>删除</span><span>*/</span>
<span>15</span> <span>$sql</span> = "DELETE FROM `user` WHERE `login` LIKE 'kevin_'"; <span>//</span><span>kevin%  </span>
<span>16</span> <span>$stmt</span> = <span>$dbh</span>->prepare(<span>$sql</span><span>);  
</span><span>17</span> <span>$stmt</span>-><span>execute();  
</span><span>18</span> <span>echo</span> <span>$stmt</span>-><span>rowCount();  
</span><span>19</span> <span>/*</span><span>查询</span><span>*/</span>
<span>20</span> <span>$login</span> = 'kevin%'<span>;  
</span><span>21</span> <span>$sql</span> = "SELECT * FROM `user` WHERE `login` LIKE :login"<span>;  
</span><span>22</span> <span>$stmt</span> = <span>$dbh</span>->prepare(<span>$sql</span><span>);  
</span><span>23</span> <span>$stmt</span>->execute(<span>array</span>(':login'=><span>$login</span><span>));  
</span><span>24</span> <span>while</span>(<span>$row</span> = <span>$stmt</span>->fetch(PDO::<span>FETCH_ASSOC)){     
</span><span>25</span>  <span>print_r</span>(<span>$row</span><span>);  
</span><span>26</span> <span>}  
</span><span>27</span> <span>print_r</span>( <span>$stmt</span>->fetchAll(PDO::<span>FETCH_ASSOC)); 
</span><span>28</span> ?>
登录后复制

1 建立连接

<span>1</span> <span>php
</span><span>2</span> <span>$dbh</span>=newPDO('mysql:host=localhost;port=3306; dbname=test',<span>$user</span>,<span>$pass</span>,<span>array</span><span>(
</span><span>3</span> PDO::ATTR_PERSISTENT=><span>true</span>
<span>4</span> <span>));
</span><span>5</span> ?>
登录后复制

持久性链接PDO::ATTR_PERSISTENT=>true

2. 捕捉错误

<span> 1</span> <span>php
</span><span> 2</span> <span>try</span><span>{
</span><span> 3</span> <span>$dbh</span>=newPDO('mysql:host=localhost;dbname=test',<span>$user</span>,<span>$pass</span><span>);
</span><span> 4</span> 
<span> 5</span> <span>$dbh</span>->setAttribute(PDO::ATTR_ERRMODE,PDO::<span>ERRMODE_EXCEPTION);
</span><span> 6</span> 
<span> 7</span> <span>$dbh</span>-><span>exec</span>("SET CHARACTER SET utf8"<span>);
</span><span> 8</span> <span>$dbh</span>=<span>null</span>; <span>//</span><span>断开连接</span>
<span> 9</span> }<span>catch</span>(PDOException<span>$e</span><span>){
</span><span>10</span> <span>print</span>"Error!:".<span>$e</span>->getMessage()."<br>"<span>;
</span><span>11</span> <span>die</span><span>();
</span><span>12</span> <span>}
</span><span>13</span> ?>
登录后复制

3. 事务的

<span> 1</span> <span>php
</span><span> 2</span> <span>try</span><span>{
</span><span> 3</span> <span>$dbh</span>->setAttribute(PDO::ATTR_ERRMODE,PDO::<span>ERRMODE_EXCEPTION);
</span><span> 4</span> 
<span> 5</span> <span>$dbh</span>->beginTransaction();<span>//</span><span>开启事务</span>
<span> 6</span> <span>$dbh</span>-><span>exec</span>("insertintostaff(id,first,last)values(23,'Joe','Bloggs')"<span>);
</span><span> 7</span> <span>$dbh</span>-><span>exec</span>("<span>insertintosalarychange(id,amount,changedate)
</span><span> 8</span> values(23,50000,NOW())"<span>);
</span><span> 9</span> <span>$dbh</span>->commit();<span>//</span><span>提交事务</span>
<span>10</span> 
<span>11</span> }<span>catch</span>(<span>Exception</span><span>$e</span><span>){
</span><span>12</span> <span>$dbh</span>->rollBack();<span>//</span><span>错误回滚</span>
<span>13</span> <span>echo</span>"Failed:".<span>$e</span>-><span>getMessage();
</span><span>14</span> <span>}
</span><span>15</span> ?>
登录后复制

4. 错误处理

a. 静默模式(默认模式)

$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_SILENT); //不显示错误

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);//显示警告错误，并继续执行

$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);//产生致命错误，PDOException

<span> 1</span> <span>php
</span><span> 2</span> <span>try</span><span>{    
</span><span> 3</span>  <span>$dbh</span> = <span>new</span> PDO(<span>$dsn</span>, <span>$user</span>, <span>$password</span><span>);    
</span><span> 4</span>  <span>$sql</span> = 'Select * from city where CountryCode =:country'<span>;    
</span><span> 5</span>  <span>$dbh</span>->setAttribute(PDO::ATTR_ERRMODE, PDO::<span>ERRMODE_WARNING);    
</span><span> 6</span>  <span>$stmt</span> = <span>$dbh</span>->prepare(<span>$sql</span><span>);    
</span><span> 7</span>  <span>$stmt</span>->bindParam(':country', <span>$country</span>, PDO::<span>PARAM_STR);    
</span><span> 8</span>  <span>$stmt</span>-><span>execute();    
</span><span> 9</span>  <span>while</span> (<span>$row</span> = <span>$stmt</span>->fetch(PDO::<span>FETCH_ASSOC)) {      
</span><span>10</span>   <span>print</span> <span>$row</span>['Name'] . "/t"<span>;    
</span><span>11</span> <span> }  
</span><span>12</span> }   <span>//</span><span> if there is a problem we can handle it here  </span>
<span>13</span> <span>catch</span> (PDOException <span>$e</span><span>)  {    
</span><span>14</span>  <span>echo</span> 'PDO Exception Caught.  '<span>;    
</span><span>15</span>  <span>echo</span> 'Error with the database: <br>'<span>;    
</span><span>16</span>  <span>echo</span> 'SQL Query: ', <span>$sql</span><span>;   
</span><span>17</span>  <span>echo</span> 'Error: ' . <span>$e</span>-><span>getMessage();  
</span><span>18</span> <span>} 
</span><span>19</span> ?>
登录后复制

1. 使用 query()

<span>php
</span><span>$dbh</span>->query(<span>$sql</span>); 当<span>$sql</span> 中变量可以用<span>$dbh</span>->quote(<span>$params</span>); <span>//</span><span>转义字符串的数据</span>

<span>$sql</span> = 'Select * from city where CountryCode ='.<span>$dbh</span>->quote(<span>$country</span><span>);  
</span><span>foreach</span> (<span>$dbh</span>->query(<span>$sql</span>) <span>as</span> <span>$row</span><span>)   {    
 </span><span>print</span> <span>$row</span>['Name'] . "/t"<span>;    
 </span><span>print</span> <span>$row</span>['CountryCode'] . "/t"<span>;    
 </span><span>print</span> <span>$row</span>['Population'] . "/n"<span>; 
} 
</span>?>
登录后复制

2. 使用 prepare, bindParam和 execute [建议用,同时可以用添加、修改、删除]

<span>php
</span><span>$dbh</span>->prepare(<span>$sql</span><span>); 产生了个PDOStatement对象

PDOStatement</span>-><span>bindParam()

PDOStatement</span>->execute();<span>//</span><span>可以在这里放绑定的相应变量</span>
?>
登录后复制

3. 事物

<span>php 
 </span><span>try</span><span> {  
  </span><span>$dbh</span> = <span>new</span> PDO('mysql:host=localhost;dbname=test', 'root', ''<span>);  
  </span><span>$dbh</span>->query('set names utf8;'<span>);  
  </span><span>$dbh</span>->setAttribute(PDO::ATTR_ERRMODE, PDO::<span>ERRMODE_EXCEPTION);  
  </span><span>$dbh</span>-><span>beginTransaction();  
  </span><span>$dbh</span>-><span>exec</span>("Insert INTO `test`.`table` (`name` ,`age`)VALUES ('mick', 22);"<span>);  
  </span><span>$dbh</span>-><span>exec</span>("Insert INTO `test`.`table` (`name` ,`age`)VALUES ('lily', 29);"<span>); 
  </span><span>$dbh</span>-><span>exec</span>("Insert INTO `test`.`table` (`name` ,`age`)VALUES ('susan', 21);"<span>);  
  </span><span>$dbh</span>-><span>commit(); 
 } </span><span>catch</span> (<span>Exception</span> <span>$e</span><span>) {  
  </span><span>$dbh</span>-><span>rollBack();  
  </span><span>echo</span> "Failed: " . <span>$e</span>-><span>getMessage();  
 }  
</span>?>
登录后复制

PDO常用方法：
PDO::query()主要用于有记录结果返回的操作（PDOStatement），特别是select操作。

PDO::exec()主要是针对没有结果集合返回的操作。如insert,update等操作。返回影响行数。
PDO::lastInsertId()返回上次插入操作最后一条ID，但要注意：如果用insert into tb(col1,col2) values(v1,v2),(v11,v22)..的方式一次插入多条记录，lastinsertid()返回的只是第一条(v1,v2)插入时的ID,而不是最后一条记录插入的记录ID。
PDOStatement::fetch()是用来获取一条记录。配合while来遍历。
PDOStatement::fetchAll()是获取所有记录集到一个中。
PDOStatement::fetchcolumn([int column_indexnum])用于直接访问列，参数column_indexnum是该列在行中的从0开始索引值，但是，这个方法一次只能取得同一行的一列，只要执行一次，就跳到下一行。因此，用于直接访问某一列时较好用，但要遍历多列就用不上。
PDOStatement::rowcount()适用于当用query("select ...")方法时，获取记录的条数。也可以用于预处理中。$stmt->rowcount();
PDOStatement::columncount()适用于当用query("select ...")方法时，获取记录的列数。

注解：
1、选fetch还是fetchall？
小记录集时，用fetchall效率高，减少从数据库检索次数，但对于大结果集，用fetchall则给系统带来很大负担。数据库要向WEB前端传输量太大反而效率低。
2、fetch()或fetchall()有几个参数：
mixed pdostatement::fetch([int fetch_style [,int cursor_orientation [,int cursor_offset]]])
array pdostatement::fetchAll(int fetch_style)

fetch_style参数:
■$row=$rs->fetchAll(PDO::FETCH_BOTH); FETCH_BOTH是默认的，可省，返回关联和索引。
■$row=$rs->fetchAll(PDO::FETCH_ASSOC); FETCH_ASSOC参数决定返回的只有关联数组。
■$row=$rs->fetchAll(PDO::FETCH_NUM); 返回索引数组
■$row=$rs->fetchAll(PDO::FETCH_OBJ); 如果fetch()则返回对象，如果是fetchall(),返回由对象组成的二维数组

以上就介绍了php mysql PDO使用，包括了方面的内容，希望对PHP教程有兴趣的朋友有所帮助。

相关标签：
fetch gt pdo
来源：php.cn
上一篇：PHP----$_SERVER---相关的变量的意义 下一篇：lua-redis中redis数据（table）转换成json
本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
作者最新文章
最新问题
PX自动转换为REM错误  <style>html {   字体大小：calc(100vw / 3.75);      }body {    字体大小：0.16rem；      }</s...
来自于 2024-04-16 09:34:16
0
0
4687
PHP数组从URL参数中获取的行为不如预期 我有一个包含类别ID的URL参数，我想将其视为一个数组，如下所示：http://example.com?cat[]=3,9,13在PHP中，我使用它从URL参数获取数组：$catI...
来自于 2024-04-06 22:09:02
0
1
1428
如何在 React 中的排序方法上触发渲染（带有过滤器和分页）？ 这是我的Sort.js样式组件：<SortWrapper><SortText>SortBy</SortText><SortSelecton...
来自于 2024-04-06 21:28:37
0
1
2257
使用网格创建此特定设计：分步指南 我正在尝试创建一个类似于此图像的布局，我尝试过使用此网格但无法实现。<ulclass="container"><liclass="f...
来自于 2024-04-06 20:29:08
0
2
492
使用具有 3 级层次结构的自定义分类法选择 woocommerce 相关产品 我有一家woocommerce商店，其自定义分类为“运动”。该分类具有三个级别-父级、子级、子级-例如：室内运动>竞技场运动>篮球。如果用户查看篮球项目，那么我希望相关...
来自于 2024-04-06 20:05:30
0
1
544
相关专题
更多>
热门推荐
热门教程
更多>
相关教程
热门推荐
最新课程
最新下载
更多>
网站特效
网站源码
网站素材
前端模板