PHP MySQL connection method PDO usage

$DBH = new PDO (' Mysql:host=localhost;dbname=access_control ', ' root ', '); $DBH->setattribute (Pdo::attr_errmode, pdo::errmode_exception); $DBH->exec (' Set names UTF8 '); /* Add */ $sql = "INSERT into ' user ' SET ' login ' =:login and ' password ' =:p assword"; $sql = "INSERT into ' user ' (' login ', ' password ') VALUES (: Login,:p assword)"; $stmt = $dbh->prepare ($sql); $stmt->execute (': Login ' = ' kevin2 ', ':p assword ' = ') '); echo $DBH->lastinsertid (); /* Modify */ $sql = "UPDATE ' user ' SET ' password ' =:p assword WHERE ' user_id ' =:userid"; $stmt = $dbh->prepare ($sql); $stmt->execute (Array (': userId ' = ' 7 ', ':p assword ' = ' 4607e782c4d86fd5364d7e4508bb10d9 ')); echo $stmt->rowcount (); /* Delete */ $sql = "DELETE from ' user ' WHERE ' login ' like ' kevin_ '"; kevin% $stmt = $dbh->prepare ($sql); $stmt->execute (); echo $stmt->rowcount (); /* Query */ $login = ' kevin% '; $sql = "SELECT * from ' user ' WHERE ' login ' Like:login"; $stmt = $dbh->prepare ($sql); $stmt->execute (Array (': Login ' = $login)); while ($row = $stmt->fetch (PDO::FETCH_ASSOC)) { Print_r ($row); } Print_r ($stmt->fetchall (PDO::FETCH_ASSOC)); ?> Copy Code 1. Establish the connection $DBH =newpdo (' mysql:host=localhost;port=3306; Dbname=test ', $user, $pass, Array ( Pdo::attr_persistent=>true )); ?> Copy Code Persistent link Pdo::attr_persistent=>true 2, catch error try{ $DBH =newpdo (' mysql:host=localhost;dbname=test ', $user, $pass); $DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception); $DBH->exec ("Set CHARACTER set UTF8"); $DBH =null; Disconnect Connection }catch (pdoexception$e) { Print "error!:". $e->getmessage (). " "; Die (); } ?> Copy Code 3,PDO transactions try{ $DBH->setattribute (pdo::attr_errmode,pdo::errmode_exception); $DBH->begintransaction ();//Open transaction $DBH->exec ("Insertintostaff (Id,first,last) VALUES (+, ' Joe ', ' Bloggs ')"); $DBH->exec ("Insertintosalarychange (id,amount,changedate) VALUES (23,50000,now ()) "); $DBH->commit ();//Commit a transaction }catch (exception$e) { $DBH->rollback ();//Error rollback echo "Failed:". $e->getmessage (); } ?> Copy Code 4. Error handling A. Silent mode (default mode) try{ $DBH = new PDO ($DSN, $user, $password); $sql = ' Select * from city where CountryCode =:country '; $DBH->setattribute (Pdo::attr_errmode, pdo::errmode_warning); $stmt = $dbh->prepare ($sql); $stmt->bindparam (': Country ', $country, PDO::P aram_str); $stmt->execute (); while ($row = $stmt->fetch (PDO::FETCH_ASSOC)) { Print $row [' Name ']. "/t"; } }//If there is a problem we can handle it here catch (Pdoexception $e) { Echo ' PDO Exception caught. '; Echo ' Error with the database: '; Echo ' SQL Query: ', $sql; Echo ' Error: '. $e->getmessage (); } ?> Copy Code 1, using Query () $DBH->query ($sql); When the $sql 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"; } ?> Copy Code 2, using prepare, Bindparam and execute [recommended, can also be added, modified, deleted] $DBH->prepare ($sql); Produced a Pdostatement object. Pdostatement->bindparam () Pdostatement->execute ();//You can put the corresponding variable of the binding here ?> Copy Code 3. PHP PDO Transaction Example try { $DBH = new PDO (' 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 (); } ?> Copy Code

