PDO: Does fetchColumn moves the pointer of the returned results set?
I recently implemented PDO and noticed that my query results lacked the first row. That's probably because fetchColumn() retrieves the first row and moves the pointer to the second row so that the while() loop starts at row 2. Is that correct? If so, how can I avoid that and improve the following code block?
$STH = $DBH->prepare("SELECT * FROM users");
$result = $开发者_开发知识库STH->execute();
if (!$result)
{
return false;
}
elseif($STH->fetchColumn()>0)//counterpart of mysql_num_rows()
{
while ($row = $STH->fetch())
{
...
}
}
}
Is that correct?
Yes. Also, fetchColumn()
is not an equivalent for mysql_num_rows()
. Instead, fetchColumn()
retrieves the indexed column value (defaulting to index 0) of the current row and as assumed, advances the cursor.
If you need a count of the number of rows returned in your query, I suggest you first issue a SELECT COUNT(1) ...
query with the same conditions, using fetchColumn()
to return the count.
See example #2 on this manual page - http://www.php.net/manual/en/pdostatement.rowcount.php
For example
$stmt = $DBH->query('SELECT COUNT(1) FROM users');
// using a straight PDO::query() call as a prepared statement would be
// overkill for these queries
if ($stmt->fetchColumn() == 0) {
return false;
}
$stmt = $DBH->query('SELECT * FROM users');
while ($row = $stmt->fetch()) {
...
}
After googling around I came up with this solution:
$STH = $DBH->prepare("SELECT * FROM users");
if(!$STH)
{
$error = $DBH->errorInfo();
}
else
{
$result = $STH->execute();
if($result===false)
{
return false;
}
else
{
$rows = $STH->fetchAll(PDO::FETCH_ASSOC);
if(count($rows) > 0)
{
foreach ($rows as $row)
{
...
}
}
}
}
}
精彩评论