Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

get number of rows with pdo

Tags:

mysql

pdo

rows

I have a simple pdo prepared query:

$result = $db->prepare("select id, course from coursescompleted where person=:p"); 
$result ->bindParam(':p', $q, PDO::PARAM_INT);
$result->execute();
$rows = $result->fetch(PDO::FETCH_NUM);
echo $rows[0];

the echo seems to be returning the ID value of the record, not the number of records returned by the query?

any idea or explanation for this?

like image 379
Smudger Avatar asked Nov 02 '12 13:11

Smudger


2 Answers

PDO::FETCH_NUM: returns an array indexed by column number as returned in your result set, starting at column 0

You aren't fetching the row-count at all.

SELECT COUNT(*) FROM coursescompleted where person=:p

This query would return total rows in $rows[0];

EDIT: Please see @ray's answer. using count(id) is better than count(*) for InnoDB.


You could get row-count in the following manner, from your earlier query.

$row_count = $result->rowCount();

But be warned:

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

Documentation

like image 173
Anirudh Ramanathan Avatar answered Oct 14 '22 02:10

Anirudh Ramanathan


You've executed a query that returns rows from the database, fetched the first row from the result into a variable and then echo'd the first column of that row.

If you want to count, do an SQL count()

$result = $db->prepare("select count(*) from coursescompleted where person=:p"); 
$result->bindParam(':p', $q, PDO::PARAM_INT);
$result->execute();
$rowCount = $result->fetchColumn(0);
echo $rowCount;
like image 45
Stephen Avatar answered Oct 14 '22 01:10

Stephen