Something I try to do is to combine 4 queries into one queries with php/mysql. No idea if it is possible (I hope!).

I have an account table that contains all my accounts, ... which there is a "status" field. There is 4 Status: pending, trial, active, expired.

I currently do something like

SELECT count(id) AS expired FROM user WHERE status = 'expired';
SELECT count(id) AS active FROM user WHERE status = 'active'; ... and such.

Is it possible to combine everything so that when I do $data = mysql_fetch_array(); I will have $data['expired'], $data['active'], $data['pending'] and $data['trial'];

Instead right now I have to play with 4 different variables ... and 4 queries, which is not optimised I am sure.

Yes, most definitely. The defined name doesn't really matter. There's no need to put two different queries for the same table name.

You could replace:


SELECT count(id) AS expired FROM user WHERE status = 'expired';
SELECT count(id) AS active FROM user WHERE status = 'active'; ... and such.

for:


$sql = "

SELECT count(id) AS expired_and_active
FROM user
WHERE status = 'expired' AND status = 'active'

";

$result = mysql_query($sql);
$data = $result['expired_and_active'];

Then, you could use $data['id'] for instance. ;)

hum this wouldnt give me the correct amount :stickout:

I need to know how many expired, how many active, how many trial, how many pending, this is what I need to know, and would like to know if it is possible with only one query ;)

(sorry if it was unclear :) )

this was written for oracle. might or might not be useful as not sure if mysql can do things in the same way..


select DATA1,count(*),
sum(decode(status, 'A', 1, 0)) as active,
sum(decode(status, 'M', 1, 0)) as maint,
sum(decode(status, 'D', 1, 0)) as deleted,
sum(decode(status, 'S', 1, 0)) as suspend,
sum(decode(status, 'P', 1, 0)) as proxy
from TABLE1
group by DATA1

hum this wouldnt give me the correct amount :stickout:

I need to know how many expired, how many active, how many trial, how many pending, this is what I need to know, and would like to know if it is possible with only one query ;)

(sorry if it was unclear :) )
Due to the missing SQL fields info, I'd require the rest of your targeted SQL fields in order to apply the right conditions.

@slidey:

Looks like you know how to code oracle structure. Always wanted to learn that. ;)

just reading some mysql docs, i think this could be done with an if statement, but id have to have access to your data (or an example table) to test...

in fact:

http://dev.mysql.com/tech-resources/articles/wizard/page4.html


mysql> SELECT location
-> , SUM(IF(dept = "Development", 1,0)) AS `Development`
-> , SUM(IF(dept = "Personnel", 1,0)) AS `Personnel`
-> , SUM(IF(dept = "Research", 1,0)) AS `Research`
-> , SUM(IF(dept = "Sales", 1,0)) AS `Sales`
-> , SUM(IF(dept = "Training", 1,0)) AS `Training`
-> , COUNT(*) AS total
-> FROM locations INNER JOIN employees USING (loc_code)
-> INNER JOIN departments USING (dept_code)
-> GROUP BY location;


doesnt look too difficult

Slidey you are the nearest one :)


SELECT SUM(
IF (
STATUS = "PENDING", 1, 0
) ) AS `PENDING` , SUM(
IF (
STATUS = "ACTIVE", 1, 0
) ) AS `ACTIVE` , SUM(
IF (
STATUS = "EXPIRED", 1, 0
) ) AS `EXPIRED` , SUM(
IF (
STATUS = "TRIAL", 1, 0
) ) AS `TRIAL`
FROM users
GROUP BY STATUS LIMIT 0 , 30

And I get somethin like :


PENDING ACTIVE EXPIRED TRIAL
0 563 0 0
0 0 4292 0
1162 0 0 0
0 0 0 362
well, it'd be great to have everything in 1 row but it is something good to start with

Wow, really cool.

I really need to take some sort of Advanced Mysql program (or class).

Peace,

will it work without the grouping and limit?

(the limit is appended by phpmyadmin automatically :stickout:)

for the grouping, I tried without and YES IT WORK! only 1 row for everything.

Thanks a lot Slidey! that will really help me to make things really better.

no worries - glad we got where you wanted :)