View Full Version : Do I need all the queries?

04-23-2013, 12:54 PM
It seems to me that I have a lot of unnecessary queries but I might be wrong. What I have is

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


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?

Old Pedant
04-23-2013, 09:54 PM
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.

Old Pedant
04-23-2013, 10:05 PM
This is my stab at a single query:

SELECT P.name, P.humanity, P.total_survivor_kills,
(P.total_zombie_kills + S1.zombie_kills) AS all_zombie_kills,
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.)