如何使用PDO在PHP中获取结果数组？

Question

阅读完SQL注入攻击后，我只是在编辑搜索脚本。我正在尝试使用PDO而不是常规的mysql连接从脚本中获得相同的功能。因此，我一直在阅读有关PDO的其他文章，但不确定。这两个脚本会提供相同的功能吗？

使用PDO：

$pdo = new PDO('mysql:host=$host; dbname=$database;', $user, $pass);

$stmt = $pdo->prepare('SELECT * FROM auction WHERE name = :name');

$stmt->bindParam(':name', $_GET['searchdivebay']);

$stmt->execute(array(':name' => $name);

使用常规mysql：

$dbhost = @mysql_connect($host, $user, $pass) or die('Unable to connect to server');

@mysql_select_db('divebay') or die('Unable to select database');

$search = $_GET['searchdivebay'];

$query = trim($search);

$sql = "SELECT * FROM auction WHERE name LIKE '%" . $query . "%'";

if(!isset($query)){

echo 'Your search was invalid';

exit;

} //line 18

$result = mysql_query($trim);

$numrows = mysql_num_rows($result);

mysql_close($dbhost);

我继续使用常规示例

while($i < $numrows){

$row = mysql_fetch_array($result);

从数据库创建匹配结果数组。我该如何使用PDO？

Answer 1

看一下PDOStatement.fetchAll方法。您也可以fetch在迭代器模式中使用。fetchAll来自PHP文档的的代码示例：<?php$sth = $dbh->prepare("SELECT name, colour FROM fruit");$sth->execute();/* Fetch all of the remaining rows in the result set */print("Fetch all of the remaining rows in the result set:\n");$result = $sth->fetchAll(\PDO::FETCH_ASSOC);print_r($result);结果：Array(&nbsp; &nbsp; [0] => Array&nbsp; &nbsp; &nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [NAME] => pear&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [COLOUR] => green&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; [1] => Array&nbsp; &nbsp; &nbsp; &nbsp; (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [NAME] => watermelon&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [COLOUR] => pink&nbsp; &nbsp; &nbsp; &nbsp; ))

Answer 2

有三种方法来获取PDO语句返回的多行。最简单的方法就是迭代PDOStatement本身：$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")$stmt->execute(array("%$query%"));// iterating over a statementforeach($stmt as $row) {&nbsp; &nbsp; echo $row['name'];}另一个是在熟悉的while语句中使用fetch（）方法获取行：$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")$stmt->execute(array("%$query%"));// using whilewhile($row = $stmt->fetch()) {&nbsp; &nbsp; echo $row['name'];}但是对于现代Web应用程序，我们应该将datbase迭代与输出分开，因此，最方便的方法是使用fetchAll（）方法一次获取所有行：$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")$stmt->execute(array("%$query%"));// fetching rows into array$data = $stmt->fetchAll();然后将它们输出到模板中：<ul><?php foreach($data as $row): ?>&nbsp; &nbsp; <li><?=$row['name']?></li><?php endforeach ?></ul>请注意，PDO支持许多复杂的提取模式，从而允许fetchAll（）返回许多不同格式的数据。

Answer 3

$st = $data->prepare("SELECT * FROM exampleWHERE example LIKE :search LIMIT 10");&nbsp;