I have 3 queries. I was told that they were potentially inefficient so I was wondering if anyone who is experienced could suggest anything. The logic is somewhat complex so bear with me.
I have two tables: shoutbox, and topic. Topic stores all information on topics that were created, while shoutbox stores all comments pertaining to each topic. Each comment comes with a group labelled by reply_chunk_id. The earliest timestamp is the first comment, while any following with the same reply_chunk_id and a later timestamp are replies. I would like to find the latest comment for each group that was started by the user (made first comment) and if the latest comment was made this month display it.
What I have written achieves that with one problem: all the latest comments are displayed in random order. I would like to organize these groups/latest comments. I really appreciate any advice
Shoutbox
Field Type
-------------------
id int(5)
timestamp int(11)
user varchar(25)
message varchar(2000)
topic_id varchar(35)
reply_chunk_id varchar(35)
Topic
id mediumint(8)
topic_id varchar(35)
subject_id mediumint(8)
file_name varchar(35)
topic_title varchar(255)
creator varchar(25)
topic_host varchar(255)
timestamp int(11)
color varchar(10)
mp3 varchar(75)
custom_background varchar(55)
description mediumtext
content_type tinyint(1)
Query
$sql="SELECT reply_chunk_id FROM shoutbox
GROUP BY reply_chunk_id
HAVING count(*) > 1
ORDER BY timestamp DESC ";
$stmt16 = $conn->prepare($sql);
$result=$stmt16->execute();
while($row = $stmt16->fetch(PDO::FETCH_ASSOC)){
$sql="SELECT user,reply_chunk_id, MIN(timestamp) AS grp_timestamp
FROM shoutbox WHERE reply_chunk_id=? AND user=?";
$stmt17 = $conn->prepare($sql);
$result=$stmt17->execute(array($row['reply_chunk_id'],$user));
while($row2 = $stmt17->fetch(PDO::FETCH_ASSOC)){
$sql="SELECT t.topic_title, t.content_type, t.subject_id,
t.creator, t.description, t.topic_host,
c1.message, c1.topic_id, c1.user, c1.timestamp AS max
FROM shoutbox c1
JOIN topic t ON (t.topic_id = c1.topic_id)
WHERE reply_chunk_id = ? AND c1.timestamp > ?
ORDER BY c1.timestamp DESC, c1.id
LIMIT 1";
$stmt18 = $conn->prepare($sql);
$result=$stmt18->execute(array($row2['reply_chunk_id'],$month));
while($row3 = $stmt18->fetch(PDO::FETCH_ASSOC)){