[PHP] Getting info from two tables and sorting it

[liam_d]

Basically i am making a "LATEST POSTS" block for my forum showing the last 5 topics/replies the member has made.

I am wondering how i can get information from two tables, limit the total amount to 5 and then sort by the date/time posted.

My topics table:
http://i43.photobucket.com/albums/e397/clericvash/topics.jpg

Replies table:
http://i43.photobucket.com/albums/e397/clericvash/replies.jpg

 

[Crusader]

Do you mean the last 5 posts in general or by a specific user?

You could do this with a LEFT JOIN either case.

 

[Eric]

Threw this together really quick, untested, but a general idea:

<table>
<tbody>
<tr>
	<th>Thread</th>
	<th>Views</th>
	<th>Replies</th>
	<th>Last Reply</th>
</tr>
<?php

$posts = mysql_query("
	SELECT t.topic_id, t.author, t.title, t.date, t.replies, t.views, r.reply_id, r.date AS last_reply_date, r.author AS last_replier
	FROM topic_table AS t
	LEFT JOIN reply_table AS r ON (t.topic_id = r.reply_topic_id)
	ORDER BY r.date DESC
	LIMIT 5
");

while ($post = mysql_fetch_assoc($posts))
{
	echo "
<tr>
	<td><a href=\"http://www.forum.com/showthread.php?t=$post[topic_id]\" title=\"$post[title]\">$post[title]</a><br /><em>by $post[author]</em> on <em>$post[date]</em></td>
	<td>$post[views]</td>
	<td>$post[replies]</td>
	<td>by <a href=\"http://www.forum.com/showthread.php?t=$post[topic_id]#post$post[reply_id]\">$post[last_replier]</a> on <em>$post[last_reply_date]</em></td>
</tr>
";
}

?>
</tbody>
</table>

 

[user-7256]

Why not store the topics and replies in the same table?

 

[Peter]

Why not store the topics and replies in the same table?

he's most likely using a 3rd party software so doesn't have the choice to do that.

 

[user-7256]

he's most likely using a 3rd party software so doesn't have the choice to do that.

Okay, that's understandable.

 

[liam_d]

This is what i was looking for:

	SELECT text, date, title, topic_id
	FROM (
	
	SELECT text, date, title, topic_id
	FROM topics
	WHERE author = {$member['member_id']}
	UNION ALL
	SELECT r.text AS text, r.date AS date, t.title AS title, t.topic_id AS topic_id
	FROM replies r
	INNER JOIN topics t ON r.reply_topic_id = t.topic_id
	WHERE r.author = {$member['member_id']}
	) AS d
	ORDER
	BY date DESC
	LIMIT 5

Would have replied sooner but namepros only decided to email me the last reply made not the first one?

 

[nasaboy007]

woah i didn't know SQL statements were nestable like that. cool, thanks - i'll definitely remember that one. could be very useful in the future.

 

[liam_d]

Yeah i didn't have a clue either, but it works perfectly.