How to fetch 2 times in MYSQL PDO without FETCHALL

Question

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

prodigitalson · Accepted Answer · 2012-12-31 15:41:54Z

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++; }

I need it to be in an array to use all the rows also I need to display the first row before the while to use it for another purpose...thanks :)
this is just a simplified query to explain the problem..the query is much bigger than that
wouldn't fetching the next row in the loop cause also performance problems even more than fetchALL?
How would that cause a performance issue? Thats essentially what it does normally... There may be slightly more overhead for using a scrollable cursor but its probably negligible. It might be an issue with using relative or absolute fetching where it needs to calculate or seek past a bunch of records but even then its probably not much since i think all this is done on the C side. Why dont you try it and find out? Since the answers you have so far are about the only way to do what you need to accomplish and they all have trade offs...
Thanks @prodigitalson for your help :) I will try it as well as another approach which is to echo the first row alone and then the while results...that way will get all the results together..the first result and all the others..wouldn't that also work?

Eric Petroelje · Accepted Answer · 2012-12-31 15:16:49Z

1

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

answered Dec 31, 2012 at 15:16

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

3 Comments

Michael Samuel Over a year ago

for some reason this ignores the ORDER BY in the query...thanks :)

Eric Petroelje Over a year ago

@Michael - really? I'm not seeing how this could have any effect on the order by in the query...

Michael Samuel Over a year ago

don't know also why this happens..i'm ordering by desc but now it displays as ascending

Martin Bean · Accepted Answer · 2012-12-31 15:17:27Z

1

You can fetch all the result, and then just act on it as an array. So, for instance, you could shift the first result off the front, and then loop over any additional rows:

<?php $sql = "YOUR QUERY"; $stmt = $pdo->prepare($sql); $stmt->execute(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); // get first row $firstRow = array_shift($rows); // loop over remaining rows foreach ($rows as $row) { // do something }

answered Dec 31, 2012 at 15:17

Martin Bean

39.6k28 gold badges134 silver badges207 bronze badges

6 Comments

Eric Petroelje Over a year ago

I think he specifically wanted an answer that avoided using fetchall

Michael Samuel Over a year ago

but fetchALL wastes memory from the experience of others...thanks :)

Martin Bean Over a year ago

@MichaelSamuel Why? Have you got proof in the way of benchmarks that fetchAll() is “bad on memory” and “wasteful”, or are you just going on what you’ve read elsewhere? I imagine one fetchAll() is better than multiple fetch()s.

Michael Samuel Over a year ago

haven't tested it personally but a lot of people here reported memory problems using it

Martin Bean Over a year ago

Can’t say I’ve had a problem with it in 3+ years.

|

Collectives™ on Stack Overflow

How to fetch 2 times in MYSQL PDO without FETCHALL

3 Answers 3

6 Comments

3 Comments

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

6 Comments

Linked

Related