PDO::prepare
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;
PDO::query
(PHP 5 >= 5.1.0, PECL pdo >= 0.2.0)
PDO::query — Выполняет SQL запрос и возвращает результирующий набор в виде объекта PDOStatement
Описание
PDOStatement PDO::query
( string
$statement
, int $PDO::FETCH_CLASS
, string $classname
, array $ctorargs
)PDO::query() выполняет SQL запрос без подготовки и возвращает результирующий набор (если есть) в виде объекта PDOStatement.
Если запрос будет запускаться многократно, для улучшения производительности приложения имеет смысл этот запрос один раз подготовить методом PDO::prepare(), а затем запускать на выполнение методом PDOStatement::execute() столько раз, сколько потребуется.
Если после выполнения предыдущего запроса вы не выбрали все данные из результирующего набора, следующий вызов PDO::query() может потерпеть неудачу. В таких случаях следует вызываеть метод PDOStatement::closeCursor(), который освободит ресурсы базы данных занятые предыдущим объектом PDOStatement. После этого можно безопасно вызывать PDO::query().
Замечание:
Несмотря на то, что функция документирована, как принимающая один аргумент, вы можете передать дополнительные аргументы. Они будут обработаны, как если бы вы вызвали PDOStatement::setFetchMode() применительно к возвращаемому объекту.
Список параметров
-
statement -
Текст SQL запроса для подготовки и выполнения.
Данные в запросе должны быть правильно экранированы.
Возвращаемые значения
PDO::query() возвращает объект PDOStatement или FALSE,
если запрос выполнить не удалось.
Примеры
Пример #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() - Запускает подготовленный запрос на выполнение
add a note
User Contributed Notes
PDO::query - [14 notes]
marcos at marcosregis dot com ¶
4 years ago
fredrik at NOSPAM dot rambris dot com ¶
6 years ago
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);
?>
NUNTIUS ¶
5 years ago
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);
nicobn at gmail dot com ¶
6 years ago
Please note that when Query() fails, it does not return a PDOStatement object . It simply returns false.
Adael ¶
5 years ago
For get one row from one query:
<?PHP
$row = $dbh->query("SELECT * FROM customers")->fetch();
?>
jonmsawyer at gmail dot com ¶
6 years ago
@ 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.
andrea at bhweb dot it ¶
4 years ago
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().
Ravo ¶
6 years ago
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);
?>
oohall [AT] gmail [DOT] com ¶
4 years ago
Since query() dosn't support ? paramaters I hacked this up:
<?php
class MyPDO extends PDO
{
private $qcache; // prepared query cache
// Usage: $dbobject->query($sql, $args...)
public function pquery($sql)
{
if( is_object($this->qcache[$sql]))
{
$query = $this->qcache[$sql];
} else {
$query = $this->prepare($sql);
$this->qcache[$sql] = $query;
}
$args = func_get_args();
array_shift($args);
$query->execute($args);
return $query;
}
}
?>
Food for thought: If you're going though the results of a query and use this function to perform another query of the same type (meaning same object) the results from the previous query will be lost. Granted, this probably won't happen (often). but it should be noted.
wuzup_13 at hotmail dot com ¶
5 years ago
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
?>
dozoyousan at gmail dot com ¶
7 years ago
> 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);
ts at tpdada dot art dot pl ¶
6 years ago
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 */
?>
yannikh at gmeil dot com ¶
6 years ago
@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.