TalkPHP - View Single Post

jon · 01-29-2009, 01:34 PM

I'm working on a hilariously simple forum script. Right now to grab all of the thread information I'm doing the following query.

PHP Code:


			
$sql = "select thread.ID, title, thread, thread.date, views, name, avatar from thread, users where userid=users.ID  order by thread.date DESC;";

That works fine but I'd like to order the threads not by the date that they were created but by the latest comment made in them.

I have another table called "comments". Here is my table structure.

thread.ID in the "thread" table and threadid in the "comments" table match up.

How do I alter my original query so that I can order by comments.date? I've messed about and tried everything I can think of but it always fails.

Incidentally you can view the live fail forum here.

01-29-2009, 01:34 PM	#1 (permalink)
jon The Visitor Join Date: May 2005 Posts: 4 Thanks: 0	Probably a very easy mysql question. I'm working on a hilariously simple forum script. Right now to grab all of the thread information I'm doing the following query. PHP Code: `$sql = "select thread.ID, title, thread, thread.date, views, name, avatar from thread, users where userid=users.ID order by thread.date DESC;";` That works fine but I'd like to order the threads not by the date that they were created but by the latest comment made in them. I have another table called "comments". Here is my table structure. thread.ID in the "thread" table and threadid in the "comments" table match up. How do I alter my original query so that I can order by comments.date? I've messed about and tried everything I can think of but it always fails. Incidentally you can view the live fail forum here. __________________ yesthat.com