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 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jul 2010
    Location
    Sheffield
    Posts
    824
    Thanks
    93
    Thanked 18 Times in 18 Posts

    Do I need all the queries?

    It seems to me that I have a lot of unnecessary queries but I might be wrong. What I have is

    PHP Code:
    $stmt $db->query("SELECT * FROM profile WHERE total_zombie_kills > 10 ORDER BY total_zombie_kills DESC"); 
    then

    PHP Code:
    while ($row $stmt->fetch(PDO::FETCH_ASSOC)) {
                                        
    $stmt2 $db->query("SELECT * FROM survivor WHERE unique_id = " $row['unique_id'] . " ORDER BY last_updated DESC LIMIT 1");
                                        
    $row2 $stmt2->fetchAll(PDO::FETCH_ASSOC);
                                        
                                        
    $stmt4 $db->query("SELECT * FROM survivor WHERE unique_id = " $row['unique_id'] . " ORDER BY survival_time DESC LIMIT 1");
                                        
    $row4 $stmt4->fetchAll(PDO::FETCH_ASSOC);

                                        echo 
    '<tr><td>' $row['name'] . '</td>';
                                        
    $kills $row['total_zombie_kills'] + $row2[0]['zombie_kills'];
                                        echo 
    '<td>' $kills '</td>';
                                        echo 
    '<td>' $row['humanity'] . '</td>';

                                        echo 
    '<td>' $row['total_survivor_kills'] . '</td>';
                                        echo 
    '<td>' $row4[0]['survival_time'] . '</td>';
                                        echo 
    '</tr>';
                                    } 
    Am I running too many queries (the first query returns roughly 10-15 rows) or for that number of queries shouldn't it matter really?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    You could probably do it all in 1 query, but unless this code is executed dozens of times per minute I'd just leave it alone.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    This is my stab at a single query:
    Code:
    SELECT P.name, P.humanity, P.total_survivor_kills,
           (P.total_zombie_kills + S1.zombie_kills) AS all_zombie_kills,
           S2.survival_time
    FROM profile AS P, survivor AS S1, survivor AS S2,
        ( SELECT unique_id, MAX(last_updated) AS lastupdate
          FROM survivor GROUP BY unique_id ) AS U1,
        ( SELECT unique_id, MAX(survival_time) AS lastsurvive
          FROM survivor GROUP BY unique_id ) AS U2
    WHERE P.unique_id = S1.unique_id
      AND P.unique_id = S2.unique_id
      AND ( S1.unique_id = U1.unique_id AND S1.last_updated = U1.lastupdate )
      AND ( S2.unique_id = U2.unique_id AND S2.survival_time = U2.lastsurvive )
    AND P.total_zombie_kills > 10
    ORDER BY P.total_zombie_kills DESC
    But it will fail unless your last_updated and survival_time values are unique on a per unique_id basis. (That is, for example, if one person has two different survival_time values that are both the same and both the maximum of all survival_time values, you will get confusing results.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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