php mysql PDO使用,phpmysqlpdo使用
<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和Python各有优势,选择应基于项目需求。1.PHP适合web开发,语法简单,执行效率高。2.Python适用于数据科学和机器学习,语法简洁,库丰富。

PHP不是在消亡,而是在不断适应和进化。1)PHP从1994年起经历多次版本迭代,适应新技术趋势。2)目前广泛应用于电子商务、内容管理系统等领域。3)PHP8引入JIT编译器等功能,提升性能和现代化。4)使用OPcache和遵循PSR-12标准可优化性能和代码质量。

PHP的未来将通过适应新技术趋势和引入创新特性来实现:1)适应云计算、容器化和微服务架构,支持Docker和Kubernetes;2)引入JIT编译器和枚举类型,提升性能和数据处理效率;3)持续优化性能和推广最佳实践。

在PHP中,trait适用于需要方法复用但不适合使用继承的情况。1)trait允许在类中复用方法,避免多重继承复杂性。2)使用trait时需注意方法冲突,可通过insteadof和as关键字解决。3)应避免过度使用trait,保持其单一职责,以优化性能和提高代码可维护性。

依赖注入容器(DIC)是一种管理和提供对象依赖关系的工具,用于PHP项目中。DIC的主要好处包括:1.解耦,使组件独立,代码易维护和测试;2.灵活性,易替换或修改依赖关系;3.可测试性,方便注入mock对象进行单元测试。

SplFixedArray在PHP中是一种固定大小的数组,适用于需要高性能和低内存使用量的场景。1)它在创建时需指定大小,避免动态调整带来的开销。2)基于C语言数组,直接操作内存,访问速度快。3)适合大规模数据处理和内存敏感环境,但需谨慎使用,因其大小固定。

PHP通过$\_FILES变量处理文件上传,确保安全性的方法包括:1.检查上传错误,2.验证文件类型和大小,3.防止文件覆盖,4.移动文件到永久存储位置。

JavaScript中处理空值可以使用NullCoalescingOperator(??)和NullCoalescingAssignmentOperator(??=)。1.??返回第一个非null或非undefined的操作数。2.??=将变量赋值为右操作数的值,但前提是该变量为null或undefined。这些操作符简化了代码逻辑,提高了可读性和性能。


热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

适用于 Eclipse 的 SAP NetWeaver 服务器适配器
将Eclipse与SAP NetWeaver应用服务器集成。

MinGW - 适用于 Windows 的极简 GNU
这个项目正在迁移到osdn.net/projects/mingw的过程中,你可以继续在那里关注我们。MinGW:GNU编译器集合(GCC)的本地Windows移植版本,可自由分发的导入库和用于构建本地Windows应用程序的头文件;包括对MSVC运行时的扩展,以支持C99功能。MinGW的所有软件都可以在64位Windows平台上运行。

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)

VSCode Windows 64位 下载
微软推出的免费、功能强大的一款IDE编辑器

SublimeText3 英文版
推荐:为Win版本,支持代码提示!