PHP MySQL PDO use

Source: Internet
Author: User
Tags getmessage php mysql rowcount stmt

1<?PHP2 $DBH=NewPDO (' Mysql:host=localhost;dbname=access_control ', ' root ', '); 3 $DBH->setattribute (Pdo::attr_errmode, PDO::errmode_exception); 4 $DBH-exec(' Set names UTF8 '); 5 /*Add*/6 //$sql = "INSERT into ' user ' SET ' login ' =:login and ' password ' =:p assword";7 $sql= "INSERT into ' user ' (' login ', ' password ') VALUES (: Login,:p assword)";$stmt=$DBH->prepare ($sql);$stmt->execute (Array(': Login ' = ' kevin2 ', ':p assword ' = ')); 8 Echo $DBH-Lastinsertid (); 9 /*Modify*/Ten $sql= "UPDATE ' user ' SET ' password ' =:p assword WHERE ' user_id ' =:userid";  One $stmt=$DBH->prepare ($sql);  A $stmt->execute (Array(': userId ' = ' 7 ', ':p assword ' = ' 4607e782c4d86fd5364d7e4508bb10d9 '));  - Echo $stmt-RowCount (); - /*Delete*/ the $sql= "DELETE from ' user ' WHERE ' login ' like ' kevin_ '";//kevin% - $stmt=$DBH->prepare ($sql);  - $stmt-execute ();  - Echo $stmt-RowCount ();  + /*Enquiry*/ - $login= ' kevin% ';  + $sql= "SELECT * from ' user ' WHERE ' login ' Like:login";  A $stmt=$DBH->prepare ($sql);  at $stmt->execute (Array(': Login ' =$login));  -  while($row=$stmt->fetch (PDO::Fetch_assoc)) {      -  Print_r($row);  - }   - Print_r($stmt->fetchall (PDO::Fetch_assoc));  -?>

1 Establishing the connection

1 <? PHP 2 $dbh=newpdo (' mysql:host=localhost;port=3306; Dbname=test ',$user,$pass,array (3 pdo::attr_persistent=>true4)); 5 ?>

Persistent link Pdo::attr_persistent=>true

2. Catching errors

1<?PHP2 Try{3 $DBH=newpdo (' Mysql:host=localhost;dbname=test ',$user,$pass);4 5 $DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception);6 7 $DBH-exec("Set CHARACTER set UTF8");8 $DBH=NULL;//Disconnect Connection9}Catch(pdoexception$e){Ten Print"Error!:".$e->getmessage (). " <br/> "; One  die(); A } -?>

3. The Business

1<?PHP2 Try{3 $DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception);4 5 $DBH->begintransaction ();//Open Transaction6 $DBH-exec("Insertintostaff (id,first,last) VALUES (' Joe ', ' Bloggs ')");7 $DBH-exec("Insertintosalarychange (id,amount,changedate)8VALUES (23,50000,now ()) ");9 $DBH->commit ();//Commit a transactionTen  One}Catch(Exception$e){ A $DBH->rollback ();//Error Rollback - Echo"Failed:".$e-getMessage (); - } the?>

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 error, and continue execution

$DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception);//Generate fatal error, pdoexception

1<?PHP2 Try{    3  $DBH=NewPDO ($dsn,$user,$password); 4  $sql= ' Select * from city where CountryCode =:country '; 5  $DBH->setattribute (Pdo::attr_errmode, PDO::errmode_warning); 6  $stmt=$DBH->prepare ($sql); 7  $stmt->bindparam (': Country ',$countryPdo::param_str); 8  $stmt-execute (); 9   while($row=$stmt->fetch (PDO::Fetch_assoc)) {      Ten   Print $row[' Name ']. "/T";  One  }   A}//If there is a problem we can handle it here - Catch(pdoexception$e)  {     -  Echo' PDO Exception caught. ‘;  the  Echo' Error with the database: <br/> ';  -  Echo' SQL Query: ',$sql;  -  Echo' Error: '.$e-GetMessage ();  - }  +?>

1. Use Query ()

<?PHP$DBH->query ($sql); When$sqlThe middle variable can be used$DBH->quote ($params);//escaping the data of a string$sql= ' Select * from city where CountryCode = '.$DBH->quote ($country); foreach($DBH->query ($sql) as $row)   {     Print $row[' Name ']. "/T"; Print $row[' CountryCode ']. "/T"; Print $row[' Population ']. "/n"; } ?>

2. Use prepare, Bindparam and execute [recommended, and can be added, modified, deleted]

<? PHP $dbh->prepare ($sql), produced a Pdostatement object pdostatement, Bindparam () Pdostatement->execute (); // you can put the corresponding variable of the binding here ?>

3. Things

<?PHPTry {    $DBH=NewPDO (' Mysql:host=localhost;dbname=test ', ' root ', '); $DBH->query (' Set names UTF8; ')); $DBH->setattribute (Pdo::attr_errmode, PDO::errmode_exception); $DBH-BeginTransaction (); $DBH-exec(' Insert into ' test '. ' Table ' (' Name ', ' age ') VALUES (' Mick ', 22); "); $DBH-exec("Insert into ' test '. ' Table ' (' Name ', ' age ') VALUES (' Lily ', 29);"); $DBH-exec(' Insert into ' test '. ' Table ' (' Name ', ' age ') VALUES (' Susan ', 21); "); $DBH-commit (); } Catch(Exception $e) {    $DBH-RollBack (); Echo"Failed:".$e-GetMessage (); }  ?>

PDO Common methods:
Pdo::query () is primarily used for operations (pdostatement) that have logged results returned, especially for select operations.

Pdo::exec () is primarily for operations returned without 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 insert operation, but note that if you use INSERT into TB (col1,col2) VALUES (V1,V2), (V11,V22): By inserting multiple records at once, Lastinsertid () returns only the ID of the first (V1,V2) insert, not the record ID that was inserted in the last record.
Pdostatement::fetch () is used to get a record. With while to traverse.
Pdostatement::fetchall () is to get all recordsets into one.
Pdostatement::fetchcolumn ([int column_indexnum]) is used for direct access to the column, and the parameter column_indexnum is the 0-based index value in the row of the column, but this method can only get one row at a time. Once executed, jump to the next line. Therefore, it is better to use for direct access to a column, but it is not useful to traverse multiple columns.
Pdostatement::rowcount () is used when using the query ("SELECT ...") method to get the number of records. can also be used in preprocessing. $stmt->rowcount ();
Pdostatement::columncount () is used when using the query ("SELECT ...") method to get the number of columns of a record.

Annotations:
1, pick or fetchall?
Small record set, with fetchall efficiency, reduce the number of retrieval from the database, but for large result sets, with Fetchall to the system brings a great burden. The database is too large to transmit to the web front end 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 saved, returns associations and indexes.
$row = $rs->fetchall (PDO::FETCH_ASSOC); The FETCH_ASSOC parameter determines that only the associative array is returned.
$row = $rs->fetchall (pdo::fetch_num); Returns an indexed array
$row = $rs->fetchall (pdo::fetch_obj); If fetch () returns the object, and if it is Fetchall (), returns a two-dimensional array consisting of the object

PHP MySQL PDO use

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.