PDO::query()
(PHP 5 >= 5.1.0, PHP 7, PECL pdo >= 0.2.0)
执行 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
。
范例
展示 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() 执行一条预处理语句
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); ?>
> 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);
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;
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); ?>
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().
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 } ?>