falcon falcon - 5 days ago 7
MySQL Question

PHP counting MYSQL rows that contain the same value in one column

I have a database and there is the column named 'status'. This column could contain five possible values. Now I need to sum number of rows, where the status column contains value 'canceled'. What function in the PHP I should use to count it, please? Thanks for response.




EDIT:

I think here's the complete solution. Correct me if I'm wrong.

$canceledsum=mysql_query("SELECT COUNT(*) AS totalcanceled FROM nabidka WHERE status='canceled'");
$d=mysql_fetch_assoc($canceledsum);
echo $d['totalcanceled'];

Answer

You are probably looking for this:

SELECT COUNT(*)
FROM yourtable
WHERE status='canceled'

or this query that will count all rows for all different status values:

SELECT status, COUNT(*)
FROM yourtable
GROUP BY status

Edit: if you want to count only records created on the current month:

SELECT status, COUNT(*)
FROM yourtable
WHERE create_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
GROUP BY status

here DATE_FORMAT(CURDATE(), '%Y-%m-01') will return the first day of the current month, if you don't have records created in the future it will be fine.

Comments