PHP-MYSQL query

[ksharma]

Hi,
Thanks to thsi wonderfull site for the service it renders for all PHP user.
I am novice in PHP and MYSQL.

I have just deployed PHP along with MYSQL. I am using PHP% along with MYSQL 5.0.2.

I have a table which captures all test results as P-Pass, F-Fail,B-Blocked.
along with date.

I want to know as how do I generate a report which will give me datewise pass, fail, blocked and total. I want the report in the below mentioned format.

Date — Total — Passed — Failed – Blocked.

Can some one let me know the code for the same

Thanks in advance

[Bane]

This can be done in a single SQL query. All you need to do is group the query by date (and probably sort it as well). For the total field, a simple COUNT(*) will do. For the other fields, you will have to use a little more than that. Considering you have a field result with possible values ‘P’, ‘F’ and ‘B’, one of the columns you select would look like this:

COUNT( IF( result = 'P', 1, NULL ) ) as passed

If you have not done much SQL, this probably does not give much help, but look in the MySQL documentation for the GROUP BY syntax. Try it out. I don’t really like giving complete answers.