CODEWITHSUNDEEP
phpmysql
Jeff
Jeff

Reputation: 245

Get the count of the mysql query results in php

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

Upvotes: 3

Views: 9120

Answers (3)

Devon Bessemer
Devon Bessemer

Reputation: 35337

$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.

Upvotes: 2

Nate
Nate

Reputation: 1482

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.

Upvotes: 1

Akshay
Akshay

Reputation: 2229

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();
}

Upvotes: 0

Related Questions