2. PDO(PHP Data Object)

<?php
// PDO(PHP Data Object)
// 数据库访问抽象层，是一种用于多种数据库的一致接口。
$dsn = 'mysql:host=127.0.0.1;dbname=weestate';
$user = 'root';
$password = '';
/*Query方法*/
$username = '18666668888';
try
{
 $dbh = new PDO($dsn, $user, $password);

 // 设置出现错误时的处理机制。
 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 // quote()为SQL语句建立索引 & 转义。
 $sql = 'select * from htw_user where username =' . $dbh->quote($username);

 foreach ($dbh->query($sql) as $row)
 {
  echo $row['username'] . "\t";
  echo $row['password'] . "\t";
  echo $row['tenant_id'] . "\n";
 }
}
catch (PDOException $e)
{
 echo 'Connecttion failed:' . $e->getMessage();
}

/*Prepare和Execute方法*/
// 防止sql注入 && 节省资源
echo '<hr/>';
$= '18666668888';
try
{
 $dbh = new PDO($dsn, $user, $password);
 $sql = 'select * from htw_user where username =:username';

 // 设置出现错误时的处理机制(异常模式)
 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 // 预查询机制与参数绑定(bindParam 与 bindValue的区别)
 $stmt = $dbh->prepare($sql);
 $stmt->bindParam(':username', $username, PDO::PARAM_STR);

 // 执行查询操作
 $stmt->execute();

 // 对结果集进行处理(关联数组的形式, $row = $stmt->fetchObject() 对象形式)
 while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
  echo $row['username'] . "\t";
  echo $row['password'] . "\t";
  echo $row['tenant_id'] . "\n";
 }
}
catch (PDOException $e)
{
 echo 'ERROR：' . $e->getMessage();
}

/*数据的增加*/
echo '<hr/>';
// 要插入的数据
$id = '09aed5ff6ae108405eabd83b40ed095k';
$username = '胡楷飞2345';
$passwd = md5('123456');
$salt = md5('123');
$create_at = date('Y-m-d H:i:s', time());
$tenant_id = 1;

try
{
 // 实例化连接,并设置捕获错误机制
 $dbh = new PDO($dsn, $user, $password);
 // 设置字符集
 $dbh->query("set names 'utf8'");
 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 // 预处理sql
 $sql = 'insert into htw_user
   (id, username, password, salt, create_at, tenant_id) values
   (:id, :username, :password, :salt, :create_at, :tenant_id)';
 $stmt = $dbh->prepare($sql);

 // 绑定参数
 $stmt->bindParam(':id', $id);
 $stmt->bindParam(':username', $username);
 $stmt->bindParam(':password', $passwd);
 $stmt->bindParam(':salt', $salt);
 $stmt->bindValue(':create_at', date('Y-m-d H:i:s', time()));
 $stmt->bindValue(':tenant_id', 2);

 // 执行sql语句
 $result = $stmt->execute();

 // 查看操作结果
 if ($result !== false) {
  echo 'success';
 } else {
  echo 'failed!';
 }
}
catch (PDOException $e)
{
 echo 'ERROR：' . $e->getMessage();
}

/*数据的修改*/
echo '<hr/>' . '数据的修改' . '<hr/>';
// 根据主键修改
$id = '09aed5ff6ae108405eabd83b40ed095k';
// 修改的内容
$username = 'PHP深度分析';
$update_at = date('Y-m-d H:i:s', time());

try
{
 // 设置字符集和捕获错误的方式
 $dbh->query("set names 'utf8'");
 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 // 预处理sql
 $sql = 'update htw_user
   set username = :username, update_at = :update_at, tenant_id = :tenate_id
   where id = :id';
 $stmt = $dbh->prepare($sql);

 // 绑定参数
 $stmt->bindParam(':username', $username);
 $stmt->bindParam(':update_at', $update_at);
 $stmt->bindValue(':tenate_id', 1);
 $stmt->bindParam(':id', $id);

 // 执行sql语句
 $stmt->execute();

 // 判断结果(受影响的行数)
 $rowCount = $stmt->rowCount();
 if ($rowCount) {
  echo 'update success! affected rows is：' . $rowCount;
 } else {
  echo 'update failed';
 }
}
catch (PDOException $e)
{
 echo 'Error：' . $e->getMessage();
}

/*灵活的sql语句*/
// sql语句在预编译和执行之前,它只是一个字符串,可以根据需要进行操作。
echo '<hr/>创建灵活的sql语句<hr/>';
$order = $_GET['order'];
switch ($order) {
 case 'value1':
  $orderBy = " value1 desc";
  break;
 case 'value2':
  $orderBy = " value2 Asc";
  break;
 default:
  $orderBy = " id desc";
  break;
}
$format = 'select * from htw_user where id = :id order by %s';
$sql = sprintf($format, $orderBy);

echo $sql;

计算受影响的行数：$stmt->rowCount();
新增的id：$dbh->lastInsertId();
/*like和通配符_*/
$country = '%' . $country . '%';
$country = $country . '_G';

/*PDO的Transaction处理*/
begin_transaction：$dbh->beginTransaction()；
commit_transaction: try{...$dbh->commit();}
rollback_transaction: catch{...$dbh->rollback();}

/*存储过程的使用*/
try
{
 #...
 $sql = 'call getQuote()';
 $stmt = $dbh->prepare($sql);
 // 执行sql
 $stmt->execute();
 // 取结果集
 $result = $stmt->fetch();
 #...
 #do something
}
catch (PDOException $e)
{
 echo $e->getMessage();
}

?>