PDO::query

(PHP 5 >= 5.1.0, PHP 7, PHP 8, PECL pdo >= 0.2.0)

PDO::query — 执行 SQL 语句，以 PDOStatement 对象形式返回结果集

说明

public PDO::query ( string $statement ) : PDOStatement
public PDO::query ( string $statement , int $PDO::FETCH_COLUMN , int $colno ) : PDOStatement
public PDO::query ( string $statement , int $PDO::FETCH_CLASS , string $classname , array $ctorargs ) : PDOStatement
public PDO::query ( string $statement , int $PDO::FETCH_INTO , object $object ) : PDOStatement

PDO::query() 在单次函数调用内执行 SQL 语句，以 PDOStatement 对象形式返回结果集（如果有数据的话）。

如果反复调用同一个查询，用 PDO::prepare() 准备 PDOStatement 对象，并用 PDOStatement::execute() 执行语句，将具有更好的性能。

如果没有完整获取结果集内的数据，就调用下一个 PDO::query()，将可能调用失败。 应当在执行下一个 PDO::query() 前，先用 PDOStatement::closeCursor() 释放数据库PDOStatement 关联的资源。

Note:

如果传入函数的参数数量超过一个，多余的参数将相当于调用结果对象 PDOStatement::setFetchMode() 方法。

参数

statement

需要准备、执行的 SQL 语句。

查询里的数据应该用恰当的形式转义。

返回值

PDO::query() 返回 PDOStatement 对象，或在失败时返回 false。

范例

Example #1 展示 PDO::query 的使用

PDO::query() 一个不错的功能是：执行 SELECT 语句，并能够循环遍历结果集。

<?php
function getFruit($conn) {
    $sql = 'SELECT name, color, calories FROM fruit ORDER BY name';
    foreach ($conn->query($sql) as $row) {
        print $row['name'] . "\t";
        print $row['color'] . "\t";
        print $row['calories'] . "\n";
    }
}
?>

以上例程会输出：

apple   red     150
banana  yellow  250
kiwi    brown   75
lemon   yellow  25
orange  orange  300
pear    green   150
watermelon      pink    90

参见

User Contributed Notes

Ilyas Bakirov 31-Mar-2021 06:22
I would like to mention fetching rows from SQL query using PDO:

<?php

$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);

// use the connection here
$sth = $dbh->query('SELECT * FROM countries');

// fetch all rows into array, by default PDO::FETCH_BOTH is used
$rows = $stm->fetchAll();

// iterate over array by index and by name
foreach($rows as $row) {

    printf("$row[0] $row[1] $row[2]\n");
    printf("$row['id'] $row['name'] $row['population']\n");

}
?>
Anonymous 24-Jul-2019 06:45
The Documentation doesn't mention this, but you can use a fetch style as a second parameter. For example:

<?php
    $data=$pdo->query($sql,PDO::FETCH_NUM);
    foreach($data as $row) {
        //    etc
    }
?>
stefano[dot]bertoli [at] gmail[dot]com 08-Sep-2014 02:14
Trying to pass like second argument PDO::FETCH_ASSOC it still work.

So passing FETCH TYPE like argument seems work.

This save you from something like:

<?php
$result = $stmt->setFetchMode(PDO::FETCH_NUM);
?>

Example:
<?php
$res = $db->query('SELECT * FROM `mytable` WHERE true', PDO::FETCH_ASSOC);

?>
marcos at marcosregis dot com 24-Sep-2008 09:14
After a lot of hours working with DataLink on Oracle->MySQL and PDO we (me and Adriano Rodrigues, that solve it) discover that PDO (and oci too) need the attribute AUTOCOMMIT set to FALSE to work correctly with.
There's  3 ways to set autocommit to false: On constructor, setting the atribute after construct and before query data or initiating a Transaction (that turns off autocommit mode)

The examples:
<?php
// First way - On PDO Constructor
$options = array(PDO::ATTR_AUTOCOMMIT=>FALSE);

$pdo = new PDO($dsn,$user,$pass,$options);

// now we are ready to query DataLinks

?>

<?php
// Second Way - Before create statements
$pdo = new PDO($dsn,$user,$pass);

$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,FALSE);
// or
$pdo->beginTransaction();

// now we are ready to query DataLinks
?>

To use DataLinks on oci just use OCI_DEFAULT on oci_execute() function;
andrea at bhweb dot it 16-Jul-2008 03:57
If someone is suffering of the "MySQL server has gone away" problem after executing multiple queries, this is a solution that solved it for me. It's similar to the one needed for the exact same problem in mysqli.

<?php
$stmt=$db->prepare($query);
$stmt->execute();
do { $stmt->fetch(); $stmt->closeCursor(); ++$line; } while($stmt-
>nextRowset());
?>

I found this only works using prepare and execute this way, not if you
directly execute the query with query().
fredrik at NOSPAM dot rambris dot com 05-May-2007 09:04
The handling of errors by this function is controlled by the attribute PDO::ATTR_ERRMODE.

Use the following to make it throw an exception:
<?php
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>
dozoyousan at gmail dot com 02-May-2006 10:26
> When query() fails, the boolean false is returned.

I think that is "Silent Mode".
If that set attribute ErrorMode "Exception Mode"
then that throw PDOException.
 $pdoObj = new PDO( $dsn, $user, $pass );
 $pdoObj->setAttribute("PDO::ATTR_ERRMODE", PDO::ERRMODE_EXCEPTION);