PHP中PDO的基础应用实例_PHP教程

<?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();
}
?>

<?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); //返回一个字段字符串，这个字符串是返回的记录的第一条记录的第一个字段
?>

<?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();//如果执行中有错误的情况下  回滚
}
?>

<?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();
}
?>

<?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);
  }
}
?>