Basic Application Instance of PDO in PHP

InPHP developmentMysql or mysqli are currently using many database connection methods. PDO support is already available in PHP5. I heard that in the next version of PHP6, PDO will be used as the default database link tool, whether it is true or false, PDO, as another connection method for databases, may be widely used in the near future. Here, Qingyuan is based on the current knowledge, I will share with you the use of PDO as an example. Some of the Code in this article comes from the network.


Before using PDO, you must first set the PHP. ini file so that PHP supports PDO, because PHP5 is not supported by default. Find the PHP. ini file in the PHP installation directory, open and search extension = php_pdo.dll and extension = php_pdo_mysql.dll, remove the semicolon (;), and restart apache.


Next, we will start to write PHP code. The first is connection.DatabaseSee the following example:

 exec('SET CHARACTER SET '.DB_CHARSET);    $DBH->exec('SET NAMES '.DB_CHARSET);} catch (PDOException $e) {    print "Error!: " . $e->getMessage() . "
";    die();}?>

After the connection is successful, we need to add, delete, modify, and query the data in the database. Here we mainly use the exec and query functions.


Example:

 Exec ($ SQL _insert); // return bool's true or fal $ lastInsertId = $ DBH-> lastInsertId (); // Delete data $ SQL _delete = 'delete FROM wp_options Where option_id = '. $ lastInsertId; $ back = $ DBH-> exec ($ SQL _delete); // return bool's true or fal $ lastInsertId = $ DBH-> lastInsertId (); // Update data $ SQL _update = 'Update wp_options SET option_name = \''. time (). rand (1,100 ). '\' Where option_id = '. $ lastInsertId; $ lastUpdateId = $ DBH-> lastInsertId ();/ /Return the corresponding operation id // query data $ SQL _select = 'select option_id FROM wp_options orDER BY option_id desc limit 4'; $ back = $ DBH-> query ($ SQL _select ); // returns an object. This object can be directly traversed using foreach for the query result set $ back = $ DBH-> query ($ SQL _select)-> fetch (); // return a data result. You can use foreach to directly traverse the result set in a loop. $ back = $ DBH-> query ($ SQL _select)-> fetchAll (); // returns an array. This object can be directly traversed using foreach for the query result set $ back = $ DBH-> query ($ SQL _select)-> fetchColumn (0 ); // Returns a field string, which is the first field in the first record of the returned record?>

At this point, the basic operations of PDO are basically finished. For general enterprise websites, small management systems can basically meet the requirements. However, if we develop large websites or management systems with complicated logic, it is far from enough to rely solely on Data Reading and writing, we may also need to operate on database stored procedures, transactions, and other objects. Next we will continue to look at how PDO is implemented.


For transactions, PDO has been encapsulated and we only need to call it.


Example:

 BeginTransaction (); // start a transaction // content of SQL Execution ...................... $ DBH-> exec ($ SQL _insert); // execute a series of operations $ DBH-> exec ($ SQL _update); $ DBH-> exec ($ SQL _delete ); // wait $ DBH-> commit (); // if the execution is completed correctly, confirm commit} catch (Exception $ e) {$ DBH-> rollBack (); // roll back if an error occurs during execution}?>

It is important to note that when using a transaction, you must use the try... catch statement. When an error is returned, the current event will not be submitted.


When executing the stored procedure, PDO still uses the old method because QingyuanI haven't fully figured it out yet. If you have a better solution, share it with us.


Check the Code:

 exec($sql_procedure);    $sql_call_procedure = 'CALL inout_test(100,@out_option_name,@out_option_value)';    $DBH->exec($sql_call_procedure);    $sql_select_procedure = 'Select @out_option_name,@out_option_value';    $back = $DBH->query($sql_select_procedure)->fetch();    $back = $DBH->exec($sql_drop_procedure);}catch(Exception $e){    echo $e->getMessage();}?>

In addition, UncleToo will introduce some knowledge about PDO preprocessing. The Preprocessing mechanism has many advantages. It can reuse SQL statements and has higher execution efficiency than direct execution. Therefore, if we use the PDO preprocessing mechanism, it is very good for the program. (PS: T good php q buckle: 276167802, verification: csl)


See the following example code:

 Prepare ('insert INTO wp_options (blog_id, option_name, option_value, autoload) VALUES (0,: option_name,: option_value, \ 'No \')'); $ option_name = $ option_value = ''; $ stmt-> bindParam (': option_name', $ option_name); // $ stmt-> bindParam (': option_value ', $ option_value); // insert A data entry. A $ option_name = 'name '. time (); $ option_value = 'value '. time (); $ stmt-> execute (); // insert another different data B $ option_name = 'name _'. time (); $ option_value = 'Value _'. time (); $ stmt-> execute (); $ stmt = $ DBH-> prepare ('insert INTO wp_options (blog_id, option_name, option_value, autoload) VALUES (0 ,?,?, \ 'No \ '); $ option_name = $ option_value = ''; $ stmt-> bindParam ('1', $ option_name ); // $ stmt-> bindParam ('2', $ option_value); // you can insert A data $ option_name = 'name '. time (); $ option_value = 'value '. time (); $ stmt-> execute (); // here we can also insert B data $ option_name = 'name _'. time (); $ option_value = 'value _'. time (); $ stmt-> execute (); $ stmt = $ DBH-> prepare ("Select * FROM wp_options where option_id =? "); If ($ stmt-> execute (array (@ $ _ GET ['option _ id']) {while (@ $ row = $ stmt-> fetch () {print_r ($ row) ;}}?>

All of the above are the basis of PDO, which can basically meet our needs in the development process. For other functions, such as PDO large objects and error handling, for more information about the functions such as cursor, see the official PHP manual. I will not describe them here (in fact, Qingyuan will not use ^_^ ...)