php mysql PDO使用，phpmysqlpdo使用
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(),返回由对象组成的二维数组
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Solution to PHP Fatal error: Call to undefined method PDO::prepare() in
Jun 22, 2023 pm 06:40 PM
PHP is a popular web development language that has been used for a long time. The PDO (PHP Data Object) class integrated in PHP is a common way for us to interact with the database during the development of web applications. However, a problem that some PHP developers often encounter is that when using the PDO class to interact with the database, they receive an error like this: PHPFatalerror:CalltoundefinedmethodPDO::prep
How to use PHP's PDO_PGSQL extension?
Jun 02, 2023 pm 06:10 PM
As a popular programming language, PHP is widely used in the field of web development. Among them, PHP's PDO_PGSQL extension is a commonly used PHP extension. It provides an interactive interface with the PostgreSQL database and can realize data transmission and interaction between PHP and PostgreSQL. This article will introduce in detail how to use PHP's PDO_PGSQL extension. 1. What is the PDO_PGSQL extension? PDO_PGSQL is an extension library of PHP, which
PHP and PDO: How to perform bulk inserts and updates
Jul 28, 2023 pm 07:41 PM
PHP and PDO: How to perform batch inserts and updates Introduction: When using PHP to write database-related applications, you often encounter situations where you need to batch insert and update data. The traditional approach is to use loops to perform multiple database operations, but this method is inefficient. PHP's PDO (PHPDataObject) provides a more efficient way to perform batch insert and update operations. This article will introduce how to use PDO to implement batch insert and update operations. 1. Introduction to PDO: PDO is PH
PHP and PDO: How to perform paging queries and display data
Jul 29, 2023 pm 04:10 PM
PHP and PDO: How to query and display data in pages When developing web applications, querying and displaying data in pages is a very common requirement. Through paging, we can display a certain amount of data at a time, improving page loading speed and user experience. In PHP, the functions of paging query and display of data can be easily realized using the PHP Data Object (PDO) library. This article will introduce how to use PDO in PHP to query and display data by page, and provide corresponding code examples. 1. Create database and data tables
PHP and PDO: How to handle JSON data in a database
Jul 29, 2023 pm 05:17 PM
PHP and PDO: How to handle JSON data in databases In modern web development, processing and storing large amounts of data is a very important task. With the popularity of mobile applications and cloud computing, more and more data are stored in databases in JSON (JavaScript Object Notation) format. As a commonly used server-side language, PHP's PDO (PHPDataObject) extension provides a convenient way to process and operate databases. Book
PHP PDO vs. mysqli: compare and contrast
Feb 19, 2024 pm 12:24 PM
PDOPDO is an object-oriented database access abstraction layer that provides a unified interface for PHP, allowing you to use the same code to interact with different databases (such as Mysql, postgresql, oracle). PDO hides the complexity of underlying database connections and simplifies database operations. Advantages and Disadvantages Advantages: Unified interface, supports multiple databases, simplifies database operations, reduces development difficulty, provides prepared statements, improves security, supports transaction processing Disadvantages: performance may be slightly lower than native extensions, relies on external libraries, may increase overhead, demo code uses PDO Connect to mysql database: $db=newPDO("mysql:host=localhost;dbnam
How to connect to Redis database using PDO
Jul 28, 2023 pm 04:29 PM
How to use PDO to connect to the Redis database. Redis is an open source, high-performance, in-memory storage key-value database that is commonly used in cache, queue and other scenarios. In PHP development, using Redis can effectively improve the performance and stability of applications. Through the PDO (PHPDataObjects) extension, we can connect and operate the Redis database more conveniently. This article will introduce how to use PDO to connect to a Redis database, with code examples. Install the Redis extension at the beginning
PHP and PDO: How to perform database backup and restore operations
Jul 29, 2023 pm 06:54 PM
PHP and PDO: How to perform database backup and restore operations When developing web applications, database backup and restore are very important tasks. As a popular server-side scripting language, PHP provides a wealth of libraries and extensions, among which PDO (PHP Data Objects) is a powerful database access abstraction layer. This article will introduce how to use PHP and PDO to perform database backup and restore operations. Step 1: Connect to the database Before actual operation, we need to establish a connection to the database. Use PDO pair
