[SOLVED] combining two queries.

[MadTechie]

Yep, i just checked..

 

FIXED:

$sql = "SELECT SUM( if(received=0,1,0 )) AS numreceived, SUM( if(unread='unread',1,0 )) AS numunread FROM messages WHERE receiver = '{$session->username}' LIMIT 1";

 

EDIT: someone owes me a beer

[stelthius]

Sorry my internet died yet again, no there is no specific reason other than consolidation sorry to cause you guys a massive head ache

[Ken2k7]

Tested it on a test table I have. Speed-wise, it didn't seem to matter.

 

bools = 'bunch of odd lists'... it's a test table. I have a bunch of random columns with random data.

 

mysql> SELECT SUM( if(paid='Y',1,0 )) AS numpaid, SUM( if(userid < 50, 1,0) ) AS numid FROM bools;
+---------+-------+
| numpaid | numid |
+---------+-------+
|    7586 |  9095 | 
+---------+-------+
1 row in set (0.00 sec)

mysql> SELECT (SELECT COUNT(paid) FROM bools WHERE paid='Y') AS numpaid, (SELECT COUNT(userid) FROM bools WHERE userid < 50) AS numid;
+---------+-------+
| numpaid | numid |
+---------+-------+
|    7586 |  9095 | 
+---------+-------+
1 row in set (0.01 sec)

mysql> SELECT SUM( if(paid='Y',1,0 )) AS numpaid, SUM( if(userid < 50, 1,0) ) AS numid FROM bools;
+---------+-------+
| numpaid | numid |
+---------+-------+
|    7586 |  9095 | 
+---------+-------+
1 row in set (0.01 sec)

mysql> SELECT (SELECT COUNT(paid) FROM bools WHERE paid='Y') AS numpaid, (SELECT COUNT(userid) FROM bools WHERE userid < 50) AS numid;
+---------+-------+
| numpaid | numid |
+---------+-------+
|    7586 |  9095 | 
+---------+-------+
1 row in set (0.01 sec)

mysql> SELECT SUM( if(paid='Y',1,0 )) AS numpaid, SUM( if(userid < 50, 1,0) ) AS numid FROM bools;
+---------+-------+
| numpaid | numid |
+---------+-------+
|    7586 |  9095 | 
+---------+-------+
1 row in set (0.00 sec)

mysql> SELECT (SELECT COUNT(paid) FROM bools WHERE paid='Y') AS numpaid, (SELECT COUNT(userid) FROM bools WHERE userid < 50) AS numid;
+---------+-------+
| numpaid | numid |
+---------+-------+
|    7586 |  9095 | 
+---------+-------+
1 row in set (0.00 sec)

 

I would choose MadTechie's. It's much more elegant.

[stelthius]

Hello Ken, im using Mad's currently.. All the help given is greatly appretiated guys, Thanks!