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

Question

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'];

Actually, I'm a newer and I'm learning, so this is probably the reason. Thank you. — falcon
– falcon, Commented Mar 23, 2014 at 9:27
Counting, sorting and other MySQL operations are always faster than PHP. — synkro
– synkro, Commented Dec 1, 2016 at 7:05

fthiella · Accepted Answer · 2014-03-23 16:32:54Z

9

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.

edited Mar 23, 2014 at 16:32

answered Mar 23, 2014 at 9:21

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

7 Comments

falcon Over a year ago

Thank you for solving my goal (and more).

falcon Over a year ago

As already mentioned, I have the column named status. SELECT COUNT (*) FROM quote WHERE status='canceled' Thanks to your code is to counting included each row that meet the condition status='canceled'. But I need to include to counting the second condition. I have a column create_date and need to be included to counting only rows in this column according to the current month. This is a column of type DATE, so today's date appears in the column as follows: 03/23/2014 Is there a way to achieve this? Thank you very much for any help.

falcon Over a year ago

I'm sorry that I'm bothering you, but this is something what could learn me a lot...

falcon Over a year ago

And what about this? WHERE status='canceled' AND MONTH(create_date) = MONTH(CURDATE()) But this returns me 0 as well as your code. In the DB there's 1 record according both conditions.

fthiella Over a year ago

@user3424754 using just MONTH() function will be fine as long as you don't have records from different years, but it won't make use of an index on the create_date column. Is create_date a DATE column? or a varchar?

|

nanobash · Accepted Answer · 2014-03-23 09:23:42Z

1

Instead of using PHP to count, you might directly use sql.

Fire this query:

SELECT COUNT(*) FROM table_name WHERE status = 'canceled';

As for how to fire queries and getting data from PHP, please refer this link

edited Mar 23, 2014 at 9:23

nanobash

5,4967 gold badges41 silver badges56 bronze badges

answered Mar 23, 2014 at 9:22

Tzar

1,8472 gold badges15 silver badges21 bronze badges

1 Comment

falcon Over a year ago

Thanks for the response Tzar.

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related