一些PHP+PDO的查询技巧

by 尘缘 on 五月 12th, 2010 // Filed Under → php, SQl

1，类似PHP框架中的预处理，简单理解为可绑定参数自动传值

This example fetches data based on a key value supplied by a form. The user input is automatically quoted, so there is no risk of a SQL injection attack.

这个例子里获取的数据，根据用户提交的表单自动生成，不需要程序员判断变量，用户提交的数据将被自动转义，所有没有SQL注入的问题

<?php
$stmt = $dbh->prepare("SELECT * FROM users where name = ?");
if ($stmt->execute(array($_GET['name']))) {
while ($row = $stmt->fetch()) {
print_r($row);
}
}
?>

2，同上，自动赋值的另外一种代码样式

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>

3，PHP+PDO+Mysql

<?php
try {
   $dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
   foreach ($dbh->query('SELECT * from FOO') as $row) {
      print_r($row);
   }
   $dbh = null;
} catch (PDOException $e) {
   print "Error!: " . $e->getMessage() . "<br/>";
   die();
}
?>

4，完善的事务处理功能

<?php
try {
  $dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2',
      array(PDO::ATTR_PERSISTENT => true));
  echo "Connected\n";
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $dbh->beginTransaction();
  $dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
  $dbh->exec("insert into salarychange (id, amount, changedate)
      values (23, 50000, NOW())");
  $dbh->commit();

} catch (Exception $e) {
  $dbh->rollBack();
  echo "Failed: " . $e->getMessage();
}
?>