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

参见

PDO::exec() - 执行一条 SQL 语句，并返回受影响的行数
PDO::prepare() - 准备要执行的语句，并返回语句对象
PDOStatement::execute() - 执行一条预处理语句

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;

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