I have this sample query:
$STH = $DBH->query("SELECT id FROM table");
I want to get the first row and then loop and display all rows. So I use the following to get the first row:
$STH->setFetchMode(PDO::FETCH_ASSOC);
$first_row = $STH->fetch();
$first_row = $first_row['id'];
I use while loop to display all rows again:
while ($list = $STH->fetch()) {
$id = $list['id'];
echo $id;
}
Now the while skips the first row and I want it to be displayed. Is there an equivalent to mysql_data_seek
to reset the pointer again to the first row? I know fetchall
can be used but it's bad on memory and wasteful. I could also run the query and limit to 1 but this is not recommended as I have a query that joins multiple tables and would be very slow. Is there any other solution?
Thanks
if you want all records .. for fetch you are going to need to loop like while($row = $stmt->fetch()) { # work with the record } for fetchAll() you have direct acces to the records as a array.
Introduction to the PHP fetchAll() method The $mode parameter accepts one of the PDO::FETCH_* constants. The most commonly used modes are: PDO::FETCH_BOTH – returns an array indexed by both column name and 0-indexed column number. This is the default. PDO::FETCH_ASSOC – returns an array indexed by column name.
By default, PDO returns each row as an array indexed by the column name and 0-indexed column position in the row. To request a different return style, specify one of the PDO::FETCH_* constants as the first parameter when you call the PDOStatement::fetch method: PDO::FETCH_ASSOC.
I take that back looks like you can use the cursor orientation contants to select the result... sample code coming... I havent tried this so you may need to play a bit. This is also based on the assumption that a PDO::FETCH_ORI_FIRST
acts like a data_seek and leaves the cursor on the first position as opposed to returning it to whatever it was before.
$stmt = $pdo->prepare('SELECT id FROM table', array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
$first = $pdo->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_FIRST);
$first_row = $first['id'];
// other stuff
// first iteration we rewind to the first record;
$cursor = PDO::FETCH_ORI_FIRST;
while (false !== ($row = $stmt->fetch(PDO::FETCH_ASSOC, $cursor))) {
$id = $row['id'];
// successive iterations we hit the "next" record
$cursor = PDO::FETCH_ORI_NEXT;
echo $id;
}
I dont think you can rewind a statement... Assuming these blocks arent seprated by a bunch of intermediary logic id just do it in the loop.
$STH->setFetchMode(PDO::FETCH_COLUMN); // no need to pull an array
$count = 0;
while ($id = $STH->fetch()) {
if($count === 0) {
$first_row = $id;
}
echo $id;
$count++;
}
Could you just use a do...while
loop instead?
$STH->setFetchMode(PDO::FETCH_ASSOC);
$list = $STH->fetch();
$first_id = $list['id'];
do {
$id = $list['id'];
echo $id;
} while ($list = $STH->fetch());
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With