adding 2 counts to an existing query

i need to add the following 2 queries:

$query = mysql_query("SELECT COUNT(*) FROM `peers` WHERE `hash` = RTRIM('$hash) AND `left` = 0 AND `expire_time` > NOW()");
$query = mysql_query("SELECT COUNT(*) FROM `peers` WHERE `hash` = RTRIM('$hash) AND `left` > 0 AND `expire_time` > NOW()");

PHP:

to this query:

$sql = mysql_query("select t.id, t.uploader_id, s.memberName, t.category_id, c.name, t.hash, t.title, t.file_name, t.file_size, t.rating, t.date_uploaded
                    from thefiles as t, smf_members as s, categories as c
                    where t.uploader_id = s.ID_MEMBER and t.category_id = c.id
                    order by t.id desc
                    limit 50") or die(mysql_error());

PHP:

i created that query myself, but i have no idea how to add the counts to it so its all in 1 nice query. can a guru help?

 

I have never done it that way or seen it done that way (of course I do not claim to be a pro).

The count query is always seperate from any other query. Also it is usually converted to a $var so it can be used anywhere within the current script after being performed once.

 

Is there a reason you need them combined? You are better off leaving them separate if not seeing as the two new queries just return numbers and the old one returns actual results.

 

$sql = mysql_query("select (SELECT COUNT(*) AS R1 FROM `peers` WHERE `hash` = RTRIM('$hash) AND `left` = 0 AND `expire_time` > NOW()) AS RESULT1,(SELECT COUNT(*) AS R2 FROM `peers` WHERE `hash` = RTRIM('$hash) AND `left` > 0 AND `expire_time` > NOW()) AS RESULT2,t.id, t.uploader_id, s.memberName, t.category_id, c.name, t.hash, t.title, t.file_name, t.file_size, t.rating, t.date_uploaded
                    from thefiles as t, smf_members as s, categories as c
                    where t.uploader_id = s.ID_MEMBER and t.category_id = c.id
                    order by t.id desc
                    limit 50") or die(mysql_error());

Code (markup):

MySQL will optimize it and will treat both counts like constants after the first row in result set-u gain from saving sending 2 (count) separated queries.

 

You can combine the two count queries in a single query, that would at least save you one query.
Search for the usage of the case statement in SQL.
You would query and the response would contain two columns, one with the answer for the first query, the second with the answer for the second query.
Roughly something like this.
SELECT SUM(CASE WHEN condition1 then 1 else 0 END) as query1, SUM(CASE WHEN condition2 then 1 else 0 END) as query2 from peers where `hash` = RTRIM('$hash) AND `expire_time` > NOW()");