Get the count of the mysql query results in php

Question

I would like to get the count of the mysql query results in php.

The query usually returns up to 10 rows.

Which one in the below is better to use:

1.

$query = "SELECT * FROM test_table WHERE test_no=12345";
$queryResult = $db->query($query);
$count = $queryResult->size();
if($count < 5){}

2.

$query = "SELECT COUNT(test) AS count FROM test_table WHERE test_no=12345";
$queryResult = $db->query($query);
$result = $queryResult->fetch();
if($result['count'] < 5){}

Also, please let me know if there is another better way to use it

I'd use the second one, no use getting the data if all you're doing is counting indices. — adeneo
– adeneo, Commented Dec 7, 2015 at 23:39

Devon Bessemer · Accepted Answer · 2015-12-07 23:48:40Z

2

$query = 'SELECT COUNT(1) FROM test_table WHERE test_no=12345';
$queryResult = $db->query($query);
$count = $queryResult->fetchColumn();

This is the best way, assuming you are using PDO. Do not specify a column name in COUNT(), use * or 1. Then use fetchColumn() to get the first column's data.

edited Dec 7, 2015 at 23:48

answered Dec 7, 2015 at 23:41

Devon Bessemer

35.6k10 gold badges77 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nate · Accepted Answer · 2015-12-07 23:42:14Z

1

It's probably better to let MySQL handle row counting. This is more important when large amounts of data are in play. If PHP were in charge of counting the rows, all of that data would need to be sent from MySQL to PHP, only for PHP to then count & discard it. It's better to skip the middle man. So the SELECT COUNT(test) query is the preferable one, of the two choices you provided.

edited Dec 7, 2015 at 23:42

answered Dec 7, 2015 at 23:39

Nate

1,48215 silver badges24 bronze badges

5 Comments

Jeff Over a year ago

is it better to use SELECT COUNT(column_name) instead of SELECT COUNT(*) ?

Nate Over a year ago

That shouldn't make a difference, but since it's a best practice to avoid using wildcards in SQL queries, I'd opt for the former. Also, apologies for introducing that confusion with my answer. I edited it to state SELECT COUNT(test)

Devon Bessemer Over a year ago

@Jeff, no you should not use a column name for this approach. This is causing extra work for SQL to check against the expression you provide, if you're just trying to count rows, you should always use * or 1 which evaluate to the same thing.

Nate Over a year ago

Is the extra work essentially "verify that the referenced column name exists", or is it more significant than that?

Devon Bessemer Over a year ago

@NateB, good question. I don't know the specifics regarding using a column name in there, but a column name isn't what the expression is supposed to be used for. MySQL doesn't have very much documentation on the count() function, from my experience, it's usually never a good idea to put something in the count() function unless you need a distinct call.

Akshay · Accepted Answer · 2015-12-07 23:37:40Z

0

If you're using PDO, the best way is to use PDO's rowCount() method. This methods returns the number of rows returned by the query. Here's an example.

try
{
$s = $conn->execute("YOUR QUERY HERE");
$count = $s->rowCount();
}
catch(PDOException $e)
{
echo $e->getMessage();
}

answered Dec 7, 2015 at 23:37

Akshay

2,2393 gold badges17 silver badges35 bronze badges

1 Comment

Devon Bessemer Over a year ago

This is inefficient for large results as you're actually getting all the data from MySQL only to count the number of rows. It's much more efficient to use the SQL count() function. It's also important to note, while rowCount() works for MySQL selects, it does not work for all databases.

Collectives™ on Stack Overflow

Get the count of the mysql query results in php

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related