Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jun 2008
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    adding 2 counts to an existing query

    i need to add the following 2 queries:


    PHP Code:
    $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()"); 
    to this query:


    PHP Code:
    $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()); 
    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?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    First of all, I don't see how those first two queries can possibly work.

    You are missing an apostrophe in each
    $query = mysql_query("SELECT COUNT(*) FROM `peers` WHERE `hash` = RTRIM('$hash) AND `left` = 0 AND `expire_time` > NOW()");

    It should be
    RTRIM('$hash')

    Secondly, if you want to get *both* counts in a single record, then you can't use COUNT, actually.

    Back in a bit after I look at the other query.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,189
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Sorry, I don't see the relationship between the bigger query and the first two.

    Each of the first two get a *single* value for the entire peers table.

    The third query is getting up to 50 records from completely unrelated tables. I can't see any good reason (or way) to integrate the first two with it.

    I think you should just leave things alone.

    You could consilidate the first two queries into one, but I doubt that will make a huge performance difference.

    ************

    OH WAIT!

    Are you wanting to get those two COUNTs on a *PER* t.hash basis, where t.hash is gotten as it is in the 3rd query???

    Now *that* would make sense.

  • #4
    New Coder
    Join Date
    Jun 2008
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Sorry, I don't see the relationship between the bigger query and the first two.

    Each of the first two get a *single* value for the entire peers table.

    The third query is getting up to 50 records from completely unrelated tables. I can't see any good reason (or way) to integrate the first two with it.

    I think you should just leave things alone.

    You could consilidate the first two queries into one, but I doubt that will make a huge performance difference.

    ************

    OH WAIT!

    Are you wanting to get those two COUNTs on a *PER* t.hash basis, where t.hash is gotten as it is in the 3rd query???

    Now *that* would make sense.
    yes man. i want them to count where the peers.hash = thefile.hash, and where left is > 0 and = 0 for 2 different counts.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •