[SOLVED] Combine two sql statements

[acctman]

how can i combine these two sql statements into one? basically i need to add up both tables and get a total / sum

 

$en['num_pictures'] = mysql_num_rows(mysql_query("SELECT `i_id` FROM `rate_pictures` WHERE `i_status` != '0' AND `i_user` = '".$line[m_id]."'"));

 

$en['num_mobpics']  = mysql_num_rows(mysql_query("SELECT `mob_id` FROM `rate_mobile_pics` WHERE `mob_confirmed` != '0' AND `mob_user` = '".$line[m_id]."'"));

 

[corbin]

Looks like you need a JOIN.

[kickstart]

Hi

 

Not sure exactly how you want to combine them, as keeping them seperate looks to be a pretty simple way of just getting the 2 counts. Unless you want a count of the total of both of them. In which case you could union the 2 pieces of sql together

 

$en['num_pictures'] = mysql_num_rows(mysql_query("SELECT `i_id` FROM `rate_pictures` WHERE `i_status` != '0' AND `i_user` = '".$line[m_id]."'
UNION ALL
SELECT `mob_id` FROM `rate_mobile_pics` WHERE `mob_confirmed` != '0' AND `mob_user` = '".$line[m_id]."'"));

 

I have used UNION ALL as UNION will remove duplicates, and I would guess that you have some i_id values which are the same as some mod_id values.

 

All the best

 

Keith