Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to fetch 2 times in MYSQL PDO without FETCHALL

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

like image 633
Michael Samuel Avatar asked Dec 31 '12 15:12

Michael Samuel


People also ask

What is the difference between fetch and fetchAll?

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.

What is fetchAll PDO :: Fetch_assoc?

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.

Which one is default mode of fetching data in PDO?

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.


2 Answers

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++;
}
like image 117
prodigitalson Avatar answered Sep 19 '22 20:09

prodigitalson


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());
like image 27
Eric Petroelje Avatar answered Sep 19 '22 20:09

Eric Petroelje