Basic application examples of PDO in PHP_PHP tutorial

In PHP development, mysql or mysqli is the most commonly used database connection method now. PDO support has begun 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 may be very popular as another database connection method in the near future. It is widely used. Based on the current knowledge, Qingyuan will share the use of PDO with you in the form of examples. Some of the codes in the article come from the Internet.


Before using PDO, you must first set up the PHP.INI file so that PHP supports PDO, because PHP5 does not support it by default. Find the PHP.INI file in the PHP installation directory, open and search for extension=php_pdo.dll and extension=php_pdo_mysql.dll, remove the preceding semicolon (;), and restart apache.


Next we start writing PHP code. The first is to connect to the database . See the example below:

<?php
define('DB_NAME', 'pdo_test');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8');
try {
    $DBH = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWORD);
    $DBH->exec('SET CHARACTER SET '.DB_CHARSET);
    $DBH->exec('SET NAMES '.DB_CHARSET);
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    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:

<?php
//增加数据
$sql_insert = 'Insert INTO wp_options(blog_id,option_name,option_value,autoload) VALUES (0,'.time().rand(1,100).','.time().rand(1,100).',\'no\')';
$back = $DBH->exec($sql_insert); //返回 bool 的true or fal
$lastInsertId = $DBH->lastInsertId();
//删除数据
$sql_delete = 'Delete FROM wp_options Where option_id='.$lastInsertId;
$back = $DBH->exec($sql_delete); //返回 bool 的true or fal
$lastInsertId = $DBH->lastInsertId();
//更新数据
$sql_update = 'Update wp_options SET option_name = \''.time().rand(1,100).'\' Where option_id='.$lastInsertId;
$lastUpdateId = $DBH->lastInsertId(); //返回的对应的操作的id
//查询数据
$sql_select = 'Select option_id FROM wp_options orDER BY option_id DESC LIMIT 4 ';
$back = $DBH->query($sql_select); //返回一个对象 这个对象可以用foreach 直接遍历循环 循环的为查询的结果集
$back = $DBH->query($sql_select)->fetch(); //返回一条数据结果 这个对象可以用foreach 直接遍历循环 循环的为查询的结果集
$back = $DBH->query($sql_select)->fetchAll(); //返回一个数组 这个对象可以用foreach 直接遍历循环 循环的为查询的结果集
$back = $DBH->query($sql_select)->fetchColumn(0); //返回一个字段字符串，这个字符串是返回的记录的第一条记录的第一个字段
?>

At this point, the basic operation of PDO is basically over. For general corporate websites, a small management system can basically suffice. However, if we develop a large website or a management system with complex logic, it is not enough to just read and write data. We may also need to modify the database. Stored procedures, transactions and other objects are operated. Let's continue to see how to implement it using PDO.


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


Example:

<?php
try{
    $DBH->beginTransaction();//开启一个事务
    //Sql 执行的内容 ......................
    $DBH->exec($sql_insert); //执行一系列的操作
    $DBH->exec($sql_update);
    $DBH->exec($sql_delete);
    //等等
    $DBH->commit(); //如果正确执行完成 那么确认 commit
} catch(Exception $e) {
    $DBH->rollBack();//如果执行中有错误的情况下  回滚
}
?>

It is important to note here that when using transactions, be sure to use try..catch statements. When an error is returned, the transaction will not be committed.


PDO still uses an older method when executing stored procedures, because this aspect is not fully understood yet. If you have a better way, please share it.
Look at the code:

<?php
$sql_procedure = '
            Create PROCEDURE inout_test(
                IN in_option_id INT,
                OUT out_option_name VARCHAR(255),
                OUT out_option_value TEXT
            )
            BEGIN
                Select option_name INTO out_option_name   FROM wp_options Where option_id = in_option_id;
                Select option_value INTO out_option_value FROM wp_options Where option_id = in_option_id;
            END;
';
try{
    $sql_drop_procedure = 'Drop PROCEDURE inout_test';
    $back = $DBH->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 you to some knowledge about PDO preprocessing. The preprocessing mechanism has many benefits. It allows SQL statements to be reused, and the execution efficiency is higher than direct execution. Therefore, if we make good use of PDO's preprocessing mechanism, it is very good for the program. (PS: T good PHP Q buckle: 276167802, verification: csl)


Look at the example code below:

<?php
$stmt = $DBH->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);
                                                                  
// 插入一条数据A
$option_name = 'name'.time();
$option_value = 'value'.time();
$stmt->execute();
                                                                  
// 插入另一条不同的数据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);
                                                                  
// 这里我们还可以插入A数据
$option_name = 'name'.time();
$option_value = 'value'.time();
$stmt->execute();
                                                                  
// 这里我们同样还可以插入B数据
$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);
  }
}
?>

The above introduction is the basis of PDO, which can basically meet our needs in the development process. For other functions, such as PDO large objects, error handling, cursors and other functions, you can refer to the official PHP manual, which will not be explained here ( In fact, Qingyuan doesn’t know either ^_^...)

http://www.bkjia.com/PHPjc/635048.html

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/635048.htmlTechArticleIn PHP development, mysql or mysqli is the most commonly used database connection method. PDO support has begun in PHP5. I heard that in the next version of PHP6, PDO will be used as the default...