PHP PDO loop returning everything except the first line

Question

There was a problem with PDOStatement->fetch under symfony (v1.4.6), because when extracting records from the first line, lines are always excluded.

Code below:

 <?php var_dump($stats->rowCount()); ?> <?php var_dump(count($stats->fetchAll())); ?> 

It produces:

 int 14 int 13 

And below:

 <?php $i = 0; ?> <?php var_dump($stats->rowCount()); ?> <?php while ($v = $stats->fetch()): ?> <?php var_dump(++$i); ?> 

It produces:

 int 14 int 1 int 2 int 3 int 4 int 5 int 6 int 7 int 8 int 9 int 10 int 11 int 12 int 13 

Any ideas why the line is excluded?

Answer 1

The problem is SOLVED , and it is not related to PDO, but the problem is with Symfony (one of the OutputDecorators that I think of, but don't know yet).

PDOStatement was valid, and when passing through it with the internal controller ->fetch everything was fine (14 records were extracted). After moving the same code to view the first record, they were always excluded from the results (and I think the associated output decoders use Iterator and ArrayAccess).

A quick workaround for this problem is NOT using a while loop , but using implemented Iterator and ArrayAccess , so the final code that works as expected (returns all lines) uses foreach

 <?php foreach ($stats as $v): ?> <?php //do stuff with record ?> <?php endforeach; ?> 

insted while + ->fetch() loop

 <?php while ($v = $stats->fetch()): ?> <?php //1st record is missing here somehow ?> <?php endwhile; ?> 

Answer 2

Specifying PDOStatement->rowCount :

If the last SQL statement executed associated with this PDOStatement was SELECT, some databases may return the number of rows returned by this expression.
However, this behavior is not guaranteed for all databases and should not be relied on for portable applications.

and note for example # 2:

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT .
Instead, use PDO::query() to issue a SELECT COUNT(*) with the same predicates as your intended PDOStatement::fetchColumn() , then use PDOStatement::fetchColumn() to get the number of rows to be returned.
Your application may then perform the correct action.

So, this is not really the answer to the question about your question, but I would say that you should not use rowCount() to query SELECT ; -)

See also the notes on this documentation page; for example this one that says (quoting):

It appears that the behavior of rowCount is different from Mysql 5.0 and 5.1.

And with such a simple query like "SELECT 1" :

Mysql 5.0.45, PHP 5.2.5 returned 1
Mysql 5.1.30, PHP 5.1.6 returned 0

Answer 3

I also had the same problem, one way to solve this is fetchAll() and fetchAll() over the array. In my case, this caused a second problem: repeating more than 10,000 entries gave a memory error.

After some digging into Symfony, I saw that it was possible to set variables directly (using $this->setVar in your action methods). setVar has 3 parameters: the name of the variable in the view, the actual data, and if the data is safe (it will be escaped). When data is not safe / escaped, all records are in mysql statement. Allows you to iterate over a select string.

Answer 4

I just ran into the same problem (the first line does not appear during line iteration), but it was my own mistake.

In my template, I:

 <?php if(!$statement_iter->fetch()): ?> ... <?php endif; ?> <?php foreach($statement_iter as $row): ?> ... <?php endforeach; ?> 

When the PDOStatement had any rows, the foreach loop would always exclude the first row.

Obviously (now I understand), if-statement calls fetch (), which moves the cursor forward one by one. So, the if statement eats the first line! Derp.