CODEWITHSUNDEEP
phpmysqlcount
Nita
Nita

Reputation: 561

PHP MySQL- 1 query counting and displaying records besad on on different "status"

I have table with records that have different "status". So far i use seperate queries to call number of these records depending on the "status". I think (hope) there is a better way (faster) to do so. Any ideas?

My SQL:

$res1 = $mysqli->query("SELECT status FROM test WHERE status='1'") 
$num_1 = mysqli_num_rows($res1);

$res2 = $mysqli->query("SELECT status FROM test WHERE status='2'") 
$num_2 = mysqli_num_rows($res2);

$res3 = $mysqli->query("SELECT status FROM test WHERE status='3'") 
$num_3 = mysqli_num_rows($res3);

MY PHP

echo $num_1;
echo $num_2;
echo $num_3;

Upvotes: 0

Views: 283

Answers (1)

Professor Abronsius
Professor Abronsius

Reputation: 33813

If you just need to get the count of each status you can do it like this in one query by using the group by clause.

select `status`,count(`status`) as 'count' from `test` group by `status`;

This would return a recordset like this:

+--------+-------+
| status | count |
+--------+-------+
|      1 |    10 |
|      2 |     8 |
|      3 |    12 |
|      4 |     8 |
|      5 |     7 |
|      6 |     3 |
|      7 |     9 |
+--------+-------+

So in PHP after you have run the query you could do something like:

while( $rs=$result->fetch_object() ) echo $rs->status.'='.$rs->count;



$dbhost = 'localhost';
$dbuser = 'xxx'; 
$dbpwd  = 'xxx'; 
$dbname = 'xxx';
$db  =  new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );

$sql="select `status`, count(`status`) as 'count' from `test` group by `status`;";
$result=$db->query( $sql );

while( $rs=$result->fetch_object() ) {
    echo '<div>Status: ' . $rs->status.', Total: ' . $rs->count . '</div>';
}

$db->close();
$db=null;

Upvotes: 1

Related Questions