Can't access Row count using PDO in PHP

[peterv12]

I'm using PDO in PHP to process MySQL tables. I want to get a row count of records returned to use in determining what further processing needs to be done. The code below will correctly display the number of rows returned, but I can't figure out a way to get the number into a variable.

[b][COLOR=red]   echo 'There are ', $stmt->fetchColumn(), ' rows returned.<br>';[/color][/b]

This is a little more of the code:

   $dbh = new PDO($dsn, $user, $password);
   $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Uncomment this code to get record count.
   $sql = 'Select COUNT(*) from MYAUTHORS INNER JOIN MYBOOKS USING (authorid)';
   $stmt = $dbh->prepare($sql);
   $stmt->bindParam(':var1', $var1, PDO::PARAM_STR);
   $stmt->execute();
   echo 'There are ', $stmt->fetchColumn(), ' rows returned.<br>'; [COLOR=green] <= Does work [/color]
   $rows = $stmt->fetchColumn(); [COLOR=red] <= doesn't work[/color]
   echo "rows: $rows again <br>";

Can anyone give me an idea of what might work? I'm kinda new to PDO.
Thanks,
Peter V.

 

[jpadie]

fetchColumn() is an iterator method. every time you call it, the recordset cursor is advanced one row. since you are using a count() query, you will only ever get one row.

even though the first call to fetchColumn() will work, you cannot use a comma as a concatenator. in php the concatenator is a DOT. this is the reason why you are having problems.

the variable assignment will work provided that you have not already iterated the rowset.

i don't see why you are using a bound parameter as there is no variable in your query.

you are also not using try...catch blocks even though you have specified exceptions as the error method. perhaps better to use another error method?

$dbh = new PDO($dsn, $user, $password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$sql = 'Select COUNT(*) from MYAUTHORS INNER JOIN MYBOOKS USING (authorid)';
$stmt = $dbh->query($sql);
if ($stmt === false){
	$i = $dbh->errorInfo();
	die('Database error: ' . $i[2]);
}
$rowCount = $stmt->fetchColumn();
echo "rows: $rowCount again <br/>";

 

[peterv12]

jpadie,
Thanks for the reply.

I'm just beginning to learn PHP & PDO. As for my "concatenation" error, I actually did a cut & paste out of a book for that, so I didn't realize it until you pointed it out. Thanks for that.

I do have try catch blocks, I only posted a section of the code I was having a problem with.

The reason for the bound parameter is that it is something I was using when I had a variable in the query, but when I changed the query by removing the variable, I forgot to remove the bound parameter.

Lastly, I don't see why the comma concatenator would result in the row count being blank. I could see it messing up the display, but why would that have anything to do with the value being assigned to the $rows variable?

 

[jpadie]

$rows is not populated in your code because there is only one row and you have already accessed it in the line above.

think about it as a ladder with one rung. everytime you call fetch you climb a rung. if you call fetch at the top, you fall off and get a false returned.

if you re-read my post i say that the call to fetchColumn() will work, just that you cannot use a comma as a concatenator.