CODEWITHSUNDEEP
phpmysqlpdo
Phillip
Phillip

Reputation: 1570

Get Number of Rows from a Select Statement Efficiently

Until recently I've been using mysql_real_escape_string() to fix most of my variables before making SQL queries to my database. A friend said that I should be using PDO's prepared statements instead, so after reading a bit about them I'm now switching over to them.

I've only encountered one problem so far in switching over, and that's counting the rows to returned by a SELECT statement. On occasion in my code, I'd run an SQL query and then count the number of rows returned from the SELECT statement. Depending on whether a result set returned, I would take different actions. Sometimes I do need to use the result set from it. MySQL let me go straight to mysql_fetch_assoc() after mysql_num_rows() with no problem. However, PDO doesn't seem to have anything like mysql_num_rows().

I've been reading some responses on SO that gave me a solution, to either use COUNT() in the SQL statement or to use the PHP function count() on the result set. COUNT() would work fine in the SQL statement if I didn't need the result set in some places, however, several people have mentioned that using count() on the result set is fairly inefficient.

So my question is, how should I be doing this if I need to count the number of rows selected (if any), then run a script with the result set? Is using count() on the result set the only way in this case, or is there a more efficient way to do things?

Below is a short example of something similar to my previous SQL code:

$query=mysql_query('SELECT ID FROM Table WHERE Name='Paul' LIMIT 1);

if(mysql_num_rows($query)>0)
{
  print_r(mysql_fetch_assoc($query));
}
else
{
  //Other code.
}

Thanks.

EDIT

I do know that you use fetchAll() on the statement before counting the result set (which gives me what I need), but I'm just trying to figure out the most efficient way to do things.

Upvotes: 1

Views: 704

Answers (2)

goat
goat

Reputation: 31813

$stmt->rowCount();

http://php.net/manual/en/pdostatement.rowcount.php

the rows must be fetched(buffered into memory, or iterated) for it to work. It's not uncommon for your pdo driver to be configured to do this automatically.

Upvotes: 2

Zohaib
Zohaib

Reputation: 7116

You will have to use Count(). You can run two queries like

SELECT COUNT(ID) FROM Table WHERE Name='Paul'

one you have get the count, then run the query with select clause

SELECT ID FROM Table WHERE Name='Paul' LIMIT 1

Count() function is not inefficient at all if you are using it like COUNT(ID), because most probably id is primary key and have an index. MYSQL wont even have to access the table.

Upvotes: 1

Related Questions