Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : PDO Functions : PDOStatement->fetch()

PDOStatement->fetch()

Fetches the next row from a result set ()

Example 1784. Fetching rows using different fetch styles

copy to clipboard
<?php
$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();

/* Exercise PDOStatement::fetch styles */
print("PDO::FETCH_ASSOC: ");
print("Return next row as an array indexed by column name\n");
$result = $sth->fetch(PDO::FETCH_ASSOC);
print_r($result);
print("\n");

print("PDO::FETCH_BOTH: ");
print("Return next row as an array indexed by both column name and number\n");
$result = $sth->fetch(PDO::FETCH_BOTH);
print_r($result);
print("\n");

print("PDO::FETCH_LAZY: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $sth->fetch(PDO::FETCH_LAZY);
print_r($result);
print("\n");

print("PDO::FETCH_OBJ: ");
print("Return next row as an anonymous object with column names as properties\n");
$result = $sth->fetch(PDO::FETCH_OBJ);
print $result->NAME;
print("\n");
?>

The above example will output:

copy to clipboard
PDO::FETCH_ASSOC: Return next row as an array indexed by column name
Array
(
   [NAME] => apple
   [COLOUR] => red
)

PDO::FETCH_BOTH: Return next row as an array indexed by both column name and number
Array
(
   [NAME] => banana
   [0] => banana
   [COLOUR] => yellow
   [1] => yellow
)

PDO::FETCH_LAZY: Return next row as an anonymous object with column names as properties
PDORow Object
(
   [NAME] => orange
   [COLOUR] => orange
)

PDO::FETCH_OBJ: Return next row as an anonymous object with column names as properties
kiwi

Example 1785. Fetching rows with a scrollable cursor

copy to clipboard
<?php
function readDataForwards($dbh) {
 $sql = 'SELECT hand, won, bet FROM mynumbers ORDER BY BET';
 try {
   $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
   $stmt->execute();
   while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT)) {
     $data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\n";
     print $data;
   }
   $stmt = null;
 }
 catch (PDOException $e) {
   print $e->getMessage();
 }
}
function readDataBackwards($dbh) {
 $sql = 'SELECT hand, won, bet FROM mynumbers ORDER BY bet';
 try {
   $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
   $stmt->execute();
   $row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST);
   do {
     $data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\n";
     print $data;
   } while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_PRIOR));
   $stmt = null;
 }
 catch (PDOException $e) {
   print $e->getMessage();
 }
}

print "Reading forwards:\n";
readDataForwards($conn);

print "Reading backwards:\n";
readDataBackwards($conn);
?>

The above example will output:

copy to clipboard
Reading forwards:
21    10    5
16    0     5
19    20    10

Reading backwards:
19    20    10
16    0     5
21    10    5

Code Examples / Notes » pdostatement_fetch

babna
When you do a SELECT query for one row, and want to check if it's there, you don't need to count the fetchAll() result, you can just check if $result->fetch() is true:
<?php
$bbnq = sprintf("SELECT login
FROM users
WHERE id = %u",27);
try
{ $req = $db_bbn->query($bbnq); }
catch (Exception $e)
{ bbnf_pdo_error($e,__FILE__,__LINE__); }
if ( $r = $req->fetch() )
{ echo "This query has a row result"; }
else
{ echo "This query has an empty result"; }
?>

dennis
Regarding the two previous notes - I downloaded the "latest CVS" a week ago for Windows and was surprised to notice that this issue is gone there, ie you don't have to close cursor before doing next query.

gerard van beek
If you to use a new instance of a class for a record you can use:
<?php
include_once("user.class");
$sth = $db->prepare("SELECT * FROM user WHERE id = 1");
/* create instance automatically */
$sth->setFetchMode( PDO::FETCH_CLASS, 'user');
$sth->execute();
$user = $sth->fetch( PDO::FETCH_CLASS );
$sth->closeCursor();
print ($user->id);
/* or create an instance yourself and use it */
$user= new user();
$sth->setFetchMode( PDO::FETCH_INTO, $user);
$sth->execute();
$user= $sth->fetch( PDO::FETCH_INTO );
$sth->closeCursor();
print ($user->id);
?>

marcini
Be careful with fetch() when you use prepared statements and MySQL (I don`t know how it is with other databases). Fetch won`t close cursor and won`t let you send any other query, even if your result set has only one row, .
If you use $statement->fetch(), you will also have to use $statement->closeCursor() afterwards, to be albe to execute another query.
Alternatively you can use $statement->fetchAll() without $statement->closeCursor().

redcube
as an alternative for marcini's note:
just clear the statement variable before you issue another query:
<?php
$stmt = $db->prepare('SELECT * FROM test');
// fetch only the first row
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// clear the variable so its contents are destroyed (including the other rows in the result)
$stmt = null;
// now you can issue another query
$stmt = $db->prepare('SELECT * FROM test2');
?>

Change Language


Follow Navioo On Twitter
PDO->beginTransaction()
PDO->commit()
PDO->__construct()
PDO->errorCode()
PDO->errorInfo()
PDO->exec()
PDO->getAttribute()
PDO->getAvailableDrivers()
PDO->lastInsertId()
PDO->prepare()
PDO->query()
PDO->quote()
PDO->rollBack()
PDO->setAttribute()
PDOStatement->bindColumn()
PDOStatement->bindParam()
PDOStatement->bindValue()
PDOStatement->closeCursor()
PDOStatement->columnCount()
PDOStatement->errorCode()
PDOStatement->errorInfo()
PDOStatement->execute()
PDOStatement->fetch()
PDOStatement->fetchAll()
PDOStatement->fetchColumn()
PDOStatement->fetchObject()
PDOStatement->getAttribute()
PDOStatement->getColumnMeta()
PDOStatement->nextRowset()
PDOStatement->rowCount()
PDOStatement->setAttribute()
PDOStatement->setFetchMode()
Terms of Service Privacy Policy Sitemap

©2024 -EPSILONSYS SOFTWARE inc

Other EPSILONSYS SOFTWARE Web Sites : Programming Resources | News Headline | Free local classifieds

eXTReMe Tracker