Community Learn Tools Library Leisure
search
English
Home > Backend Development > PHP Tutorial > php mysql PDO use

php mysql PDO use

WBOY
Release： 2016-08-08 09:29:24
Original
997 people have browsed it

<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> ?>
Copy after login

1 Establish a connection

<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> ?>
Copy after login

Persistent link PDO::ATTR_PERSISTENT=>true

2. Catching errors

<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> ?>
Copy after login

3. Business

<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> ?>
Copy after login

4. Error handling

a. Silent mode (default mode)

$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_SILENT); //Do not display errors

$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);//Display warning errors and continue execution

$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); //A fatal error occurs, 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> ?>
Copy after login

1. Use 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>?>
Copy after login

2. Use prepare, bindParam and execute [recommended, you can also add, modify, delete]

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

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

PDOStatement</span>->execute();<span>//</span><span>可以在这里放绑定的相应变量</span>
?>
Copy after login

3. Things

<?<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>?>
Copy after login

Commonly used methods of PDO:
PDO::query() is mainly used for operations that return recorded results (PDOStatement), especially select operations.

PDO::exec() is mainly for operations that do not return a result set. Such as insert, update and other operations. Returns the number of affected rows.
PDO::lastInsertId() returns the last ID of the last insertion operation, but please note: if you use insert into tb(col1,col2) values(v1,v2),(v11,v22).. to insert multiple items at a time For records, lastinsertid() returns only the ID of the first record (v1, v2) inserted, not the record ID of the last record inserted.
PDOStatement::fetch() is used to get a record. Use while to traverse.
PDOStatement::fetchAll() fetches all records into one.
PDOStatement::fetchcolumn([int column_indexnum]) is used to directly access the column. The parameter column_indexnum is the index value of the column starting from 0 in the row. However, this method can only obtain one column of the same row at a time. As long as it is executed once, it will Jump to the next line. Therefore, it is easier to use when directly accessing a certain column, but it is not useful when traversing multiple columns.
PDOStatement::rowcount() is suitable for getting the number of records when using the query("select...") method. Can also be used in preprocessing. $stmt->rowcount();
PDOStatement::columncount() is suitable for obtaining the number of columns in a record when using the query("select...") method.

Notes:
1. Should you choose fetch or fetchall?
For small record sets, using fetchall is more efficient and reduces the number of retrieval times from the database. However, for large result sets, using fetchall will put a lot of burden on the system. The amount of data the database needs to transmit to the WEB front-end is too large and inefficient.
2. fetch() or fetchall() has several parameters:
mixed pdostatement::fetch([int fetch_style [,int cursor_orientation [,int cursor_offset]]])
array pdostatement::fetchAll(int fetch_style)

fetch_style parameters:
■$row=$rs->fetchAll(PDO::FETCH_BOTH); FETCH_BOTH is the default, can be omitted, and returns the association and index.
■$row=$rs->fetchAll(PDO::FETCH_ASSOC); The FETCH_ASSOC parameter determines that only associative arrays are returned.
■$row=$rs->fetchAll(PDO::FETCH_NUM); Returns the index array
■$row=$rs->fetchAll(PDO::FETCH_OBJ); If fetch() returns the object, if it is fetchall (), returns a two-dimensional array composed of objects

The above introduces the use of php mysql PDO, including aspects of the content. I hope it will be helpful to friends who are interested in PHP tutorials.

Related labels：
fetch gt pdo
source：php.cn
Previous article：PHP----$_SERVER---The meaning of related variables Next article：Convert redis data (table) into json in lua-redis
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
PX automatic conversion to REM error  <style>html {   font-size: calc(100vw / 3.75);      }...
From 2024-04-16 09:34:16
0
0
4687
PHP arrays obtained from URL parameters do not behave as expected I have a URL parameter that contains the category ID and I want to treat it as an array li...
From 2024-04-06 22:09:02
0
1
1428
How to trigger render (with filter and pagination) on sort method in React? Here is my Sort.js style component: <SortWrapper><SortText>SortBy</SortText...
From 2024-04-06 21:28:37
0
1
2257
Creating this specific design using a grid: a step-by-step guide I'm trying to create a layout similar to this image, I've tried using this grid but can't ...
From 2024-04-06 20:29:08
0
2
492
Select woocommerce related products using custom taxonomy with 3 level hierarchy I have a woocommerce store with a custom classification of "Sports". The classif...
From 2024-04-06 20:05:30
0
1
544
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template