Do I run 2 queries at once?

Im trying to create a simple forum, heres the post in the first topic,
http://coronadoshores.ca/community/category.php?id=1&topic=Welcome
so there are 2 replies to the topic, when I click to see them, this pops up
http://coronadoshores.ca/community/post.php?id=3
which shows the original post with its replies, heres the query used.

SELECT mess.subject, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, users.name
FROM messages AS mess
INNER JOIN `users` 
ON users.id =  mess.user_id
WHERE mess.parent_id = 3
OR mess.mess_id = 3
GROUP BY mess.created

I then run it through a while loop to print each record

while($row = $stmt->fetch()) {
echo "<blockquote><h3>{$row['subject']}</h3><p>".$row['message_txt']."</P>";
echo "<footer class='pull-right'>".$row['name']." on ".$row['created']."</footer>";
echo "</blockquote>";
}

How do I target the first record (mess_id = 3) to style it like the post on the first page (and put the 2 replies inside it?

Two Choices:
Dont immediately process the data.
fetchAll, or while($row = $stmt->fetch()) { $datarow = $row; }

Then you can manipulate it as you do any other array. Your GROUP BY should probably be an ORDER BY instead, as you’re not actually doing any grouping. (This bit applies to both choices.)

This is the preferred choice if you’re going to also do things to other elements of the array. (Last, somewhere in the middle, etc)

Other Choice:
fetch a single row before the while loop, process it as you would the first message
then fetch the rest inside the loop.

This is probably faster if you’re only looking at the first element of the array.

I think the fastest way is what i’ll try

try {

$stmt = $dbh->prepare('SELECT mess.subject, mess.message_txt, mess.mess_id, mess.parent_id, mess.created, users.name
FROM messages AS mess
INNER JOIN `users` 
ON users.id =  mess.user_id
WHERE mess.parent_id = :id
OR mess.mess_id = :id
ORDER BY mess.created');

$stmt->execute(array(
':id' => $id 
));	

while($row = $stmt->fetch()) {
//put each message (replies and pos)t into aray
$datarow[] = $row;
}

foreach($array as $key => $val) {
   if($datarow[0]) {
   //style like a post
   echo ...;
  } else {
    //style like a reply
   	echo "<blockquote><h3>{$key['subject']}</h3><p>".$key['message_txt']."</P>";
echo "<footer class='pull-right'>".$row['name']." on ".$key['created']."</footer>";
echo "</blockquote>";
  }
}
} catch(PDOException $e) {
echo 'ERROR: ' . $e->getMessage();
}

Im really not sure about the foreach loop though, am I on the right track?

foreach datarow, and then check to see if $key is 0.

If you order on your creation time field in addition to grouping by it you should always have the parent show up as the first result (it normally will be first anyway because it should have the lowest primary key). This is sufficient for a single generation pass. In multiple generation displays you’ll have to order by parent_id, creation_time.

Yes, the first record will always be the parent, but I dont know how to select it (Single Generation Pass?).

I changed my foreach

foreach($datarow as $key) {
if($key = 0) {
//style like a post
echo "<div class='panel panel-default'><div class='panel-heading'>";
echo "<a class='pull-right'  href='post.php?id={$row['mess_id']}'>";
echo "<span class='glyphicon glyphicon-share-alt' style='margin:0 10px'></span>";
echo "replies <span class='badge'>{$row['replies']}</span></a>";
echo "<h3 class='panel-title' style='margin-right:175px; font-size:150%'>".$key['subject']."</h3>";
echo "</div>\n";
echo "<table class='table table-bordered table-condensed' style='width:auto'>";
echo "<tr><td><span class='glyphicon glyphicon-user' style='margin:0 10px'></span>Post by ".$key['name']."</td></tr>";
echo "<tr><td><span class='glyphicon glyphicon-time' style='margin:0 10px'></span>on ".date( 'm/d/y g:i A', strtotime($key['created']))."</td></tr>";
echo "</table>\n";
echo "<div class='panel-body comment more'>".$key['message_txt'];
echo "</div>";
echo "</div>";
} else {
//style like a reply
echo "<blockquote><h3>{$key['subject']}</h3><p>".$key['message_txt']."</P>";
echo "<footer class='pull-right'>".$key['name']." on ".$key['created']."</footer>";
echo "</blockquote>";
}
}

and got 3 empty blockquotes

Not following topic, but if you don’t use => for the VALUE, the AS $key will be the value not the KEY.

ok, I changed it to

foreach($datarow as $key => $data)

I can use the SELECT TOP FROM …
(http://www.w3schools.com/sql/sql_top.asp) but I dont understand how to select that record as well as all the others using 1 query?

So the general form of forum handling is:
A post is a post is a post.
All posts are the same thing. A post. It’s got some text in it, etc.
All posts belong to a Thread. A thread is a new object.
All posts in a thread refer to that thread (IE: your parent_id).
Thus, all posts in a thread can be pulled by the thread ID.

You’ve structured it slightly differently, in that you’ve said all posts are in a tree; the posts in a thread are children of the thread-starter’s post.

That said, you can STILL pull everything in a single query, as you have, by saying WHERE id = 3 OR parent_id = 3.
Which post is your original? ORDER BY date. A child cant have a date smaller than it’s parent, so it is guaranteed that the first row of your result set is the thread-starter.

thank you
http://coronadoshores.ca/community/post.php?id=3
(I forgot that when its a original post, its parent_id is null…

while($row = $stmt->fetch()) {
if(is_null($row['parent_id'])) {
//style like a post
	echo "<h2>{$row['subject']}</h2>";
	echo "<div class='panel panel-default'>";
	echo "<table class='table table-bordered table-condensed' style='width:auto'>";
	echo "<tr><td><span class='glyphicon glyphicon-user' style='margin:0 10px'></span>Post by ".$row['name']."</td></tr>";
	echo "<tr><td><span class='glyphicon glyphicon-time' style='margin:0 10px'></span>on ".date( 'm/d/y g:i A', strtotime($row['created']))."</td></tr>";
	echo "</table>\n";
	echo "<div class='panel-body comment more'>".$row['message_txt'];
	echo "</div>";
  } else {
	//style like a reply
	echo "<div class='well well-sm'>".$row['message_txt'];
	echo "<p class='text-right small'>".$row['name']." on ".date( 'm/d/y g:i A', strtotime($row['created']))."</p>";
	echo "</div>\n";
  }
}

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.