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

I'm just editing my search script after reading up on SQL injection attacks. I'm trying to get the same functionality out of my script using PDO instead of a regular mysql connection. So I've been reading other posts about PDO but am unsure. Will these two scripts give the same functionality?

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

With 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);

With regular mysql:

使用常规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);

I go on with the regular example to use

我继续使用常规示例

while($i < $numrows){
$row = mysql_fetch_array($result);

to create an array of matching results from the database. How do I do this with PDO?

从数据库创建匹配结果的数组。如何使用PDO执行此操作？

3 个解决方案

#1

Take a look at the PDOStatement.fetchAll method. You could also use fetch in an iterator pattern.

看一下PDOStatement.fetchAll方法。您还可以在迭代器模式中使用fetch。

Code sample for fetchAll, from the PHP documentation:

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

Results:

结果：

Array
(
    [0] => Array
        (
            [NAME] => pear
            [COLOUR] => green
        )

    [1] => Array
        (
            [NAME] => watermelon
            [COLOUR] => pink
        )
)

#2

There are three ways to fetch multiple rows returned by PDO statement.

有三种方法可以获取PDO语句返回的多行。

The simplest one is just to iterate over PDOStatement itself:

最简单的就是迭代PDOStatement本身：

$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")
$stmt->execute(array("%$query%"));
// iterating over a statement
foreach($stmt as $row) {
    echo $row['name'];
}

another one is to fetch rows using fetch() method inside a familiar while statement:

另一个是在熟悉的while语句中使用fetch（）方法获取行：

$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")
$stmt->execute(array("%$query%"));
// using while
while($row = $stmt->fetch()) {
    echo $row['name'];
}

but for the modern web application we should have our datbase iteractions separated from output and thus the most convenient method would be to fetch all rows at once using fetchAll() method:

但对于现代Web应用程序，我们应该将数据库迭代与输出分开，因此最方便的方法是使用fetchAll（）方法一次获取所有行：

$stmt = $pdo->prepare("SELECT * FROM auction WHERE name LIKE ?")
$stmt->execute(array("%$query%"));
// fetching rows into array
$data = $stmt->fetchAll();

and then output them in a template:

然后在模板中输出它们：

<ul>
<?php foreach($data as $row): ?>
    <li><?=$row['name']?></li>
<?php endforeach ?>
</ul>

Note that PDO supports many sophisticated fetch modes, allowing fetchAll() to return data in many different formats.

请注意，PDO支持许多复杂的提取模式，允许fetchAll（）以多种不同的格式返回数据。

#3

-2

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

#1