PDO::quote

PDO::prepare

Last updated: Fri, 05 Sep 2008

PDO::query

(PHP 5 >= 5.1.0, PECL pdo:0.2-1.0.3)

PDO::query — Executes an SQL statement, returning a result set as a PDOStatement object

Description

PDOStatement PDO::query ( string $statement )

PDOStatement PDO::query ( string $statement , int $PDO::FETCH_COLUMN , int $colno )

PDOStatement PDO::query ( string $statement , int $PDO::FETCH_CLASS , string $classname , array $ctorargs )

PDOStatement PDO::query ( string $statement , int $PDO::FETCH_INTO , object $object )

PDO::query() executes an SQL statement in a single function call, returning the result set (if any) returned by the statement as a PDOStatement object.

For a query that you need to issue multiple times, you will realize better performance if you prepare a PDOStatement object using PDO::prepare() and issue the statement with multiple calls to PDOStatement::execute().

If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().

Note: Although this function is only documented as having a single parameter, you may pass additional arguments to this function. They will be treated as though you called PDOStatement::setFetchMode() on the resultant statement object.

Parameters

statement: The SQL statement to prepare and execute.

Return Values

PDO::query() returns a PDOStatement object.

Examples

Example #1 Demonstrate PDO::query

A nice feature of PDO::query() is that it enables you to iterate over the rowset returned by a successfully executed SELECT statement.


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

The above example will output:

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


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().

NUNTIUS
22-Jan-2008 06:01


I found this method extremely useful for getting the iteration count. Note the usage of "for" instead of "while" or "foreach". Just place the "$row = $query->fetch()" as the second condition of your for loop (which is do until). This is the best of both worlds IMHO. Criticism welcome.



try {

    $hostname = "servername";

    $dbname = "dbname";

    $username = "username";

    $pw = "password";

    $pdo = new PDO ("mssql:host=$hostname;dbname=$dbname","$username","$pw");

  } catch (PDOException $e) {

    echo "Failed to get DB handle: " . $e->getMessage() . "\n";

    exit;

  }

      $query = $pdo->prepare("select name FROM tbl_name");

      $query->execute();

      

      for($i=0; $row = $query->fetch(); $i++){

        echo $i." - ".$row['name']."<br/>";

      }



      unset($pdo); 

      unset($query);

wuzup_13 at hotmail dot com
20-Nov-2007 06:49


This is an example PDO query function, dbQuery only needs to be passed your database query in order to work.

Our return solves the problem of not being able to count PDO returns/objects.

You can perform a count() on the return array, unless only 1 row is returned, then you will be counting columns, instead of rows.

See for yourself, I think you'll find this useful, it solved many of our problems - we have to do a bit more backend work, but such is the cost of a reasonable PDO function.



EXAMPLE:



// If you know the select statement will return only one row:

$row = dbQuery("SELECT * FROM users WHERE user_id = 1");

print $row['user_id']; // Prints one row. If more than one, will print "Array"



// If you are expecting one or more rows:

$query = dbQuery("SELECT * FROM users");

foreach ($query as $row) {

  print $row['user_id']; // Prints ALL rows

}



<?php



function dbConnect() {

  global $dbh;



  $dbInfo['database_target'] = "localhost";

  $dbInfo['database_name'] = "my_db";

  $dbInfo['username'] = "root";

  $dbInfo['password'] = "";



  $dbConnString = "mysql:host=" . $dbInfo['database_target'] . "; dbname=" . $dbInfo['database_name'];

  $dbh = new PDO($dbConnString, $dbInfo['username'], $dbInfo['password']);

  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $error = $dbh->errorInfo();

  if($error[0] != "") {

      print "<p>DATABASE CONNECTION ERROR:</p>";

      print_r($error);

  }

}



function dbQuery($queryString) {

  global $dbh;



  $query = $dbh->query($queryString);

  $i = 0;

  foreach ($query as $query2) {

    $queryReturn[$i] = $query2;

    $i++;

  }

  if($i > 1) {

    return $queryReturn;

  } else {

    return $queryReturn[0];

  }

}



dbConnect(); // Connect to Database



?>

Adael
19-Nov-2007 11:23


For get one row from one query:

<?PHP

$row = $dbh->query("SELECT * FROM customers")->fetch();

?>

fredrik at NOSPAM dot rambris dot com
05-May-2007 06: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);

?>

nicobn at gmail dot com
27-Apr-2007 12:29


Please note that when Query() fails, it does not return a PDOStatement object . It simply returns false.

Ravo
25-Apr-2007 04:49


I struggled with this trying to figure out why I couldn't return a single row using a simple query. This may or may be obvious but you have to use PDOstatement functions of the result of a PDO->query(). This took me a while to figure out since it is a far cry from the query functions of old.



<?php

$connection = new pdo("sqlite:file.sq3");

$query="SELECT * FROM table";

$result = $connection->query($query);

$row = $result->fetch(PDO::FETCH_ASSOC);

print_r($row);

?>

ts at tpdada dot art dot pl
24-Jan-2007 06:17


Look out for re-using this same variable for PDOStatement twice. You should always clear it if you want use it again.



<?php



/* WRONG */



$oPDO = new PDO();



$oPDOStatement = $oPDO->query('SELECT something');



/* making something with $oPDOStatement, i.e. fetch() */



  $oPDOStatement = $oPDO->query('SELECT something completly different');



/* and now $oPDOStatement is unuseful, fetch() doesn't returns result etc. */



?>



But...



<?php



/* GOOD */



 $oPDO = new PDO();



 $oPDOStatement = $oPDO->query('SELECT something');



 /* making something with $oPDOStatement, i.e. fetch() */



 /* and destroy poor, little object as we had used it */



 $oPDOStatement  = null;



 $oPDOStatement = $oPDO->query('SELECT something completly different');



/* and everything works fine */



?>

jonmsawyer at gmail dot com
23-Jan-2007 09:03


@ dozoyousan at gmail dot com

> 03-May-2006 05: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);



What you say is correct, however, your PHP code is incorrect:



<?php

    // This is fine

    $pdoObj = new PDO( $dsn, $user, $pass );

    

    // This line is wrong

    $pdoObj->setAttribute("PDO::ATTR_ERRMODE", PDO::ERRMODE_EXCEPTION);

    

    // It should be:

    $pdoObj->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    

    // Parameter 1 should not be in quotes. PHP interprets

    // that as a string. Instead, internally, its represented

    // as type LONG INT. Try it :)

?>



Hope this helps. Cheers.

yannikh at gmeil dot com
28-Oct-2006 11:47


@E. Rnie:

The example DOES work fine.

query returns in iterable object. print_r just does not show that!



Ich you use fetchAll() you will receive an Array with all rows. Of course this works too, but it is not a good idea on big resultsets.

E. Rnie
22-May-2006 03:36


the above example does not function well...

it just returned

(

    [queryString] => "SELECT * FROM...."

)



with a trailing ->fetchAll() everthing is working fine...



the complete correct line:



foreach ($conn->query($sql)->fetchAll as $row) {

.

.

.

dozoyousan at gmail dot com
03-May-2006 07: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);

Nicolas Bérard Nault
06-Mar-2006 12:03


When query() fails, the boolean false is returned.

add a note