Probably a very easy mysql question.

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.

jon · 01-29-2009, 02:48 PM

I managed to solve the problem. Rather than trying to do some out-of-my-depth mysql query I added another column to "thread" called "lastupdate". Then each time a new comment was made inserted the current timestamp into the thread table as well as the date column on the comments table.

Now it's working like a charm.

Wildhoney · 01-29-2009, 07:51 PM

I'm glad you sorted it. Although when you have queries of this nature that are quite intensive, you should take a look at using MySQL views. I realise it's supposed to be a simple forum package, however, views will add to the efficiency of such queries.

CoryMathews · 01-30-2009, 01:53 AM

Look into joins as well. Thats all that is needed to solve the problem without changing the db.

dp2 · 02-18-2009, 06:20 AM

Your table structure graphic are they easy to create?

How do you generate these diagrams of your database tables

Is this done in shell access and print screen and copy and paste into photoshhop or is this a spreadsheet graphic.

What is the name of these text boxes

allworknoplay · 02-26-2009, 03:15 AM

Quote:

Originally Posted by dp2

Your table structure graphic are they easy to create?

How do you generate these diagrams of your database tables

Is this done in shell access and print screen and copy and paste into photoshhop or is this a spreadsheet graphic.

What is the name of these text boxes

Since he didn't answer you, I am assuming they are just screenshots....

dp2 · 02-26-2009, 08:26 PM

I assume the screen shots are created through the shell access

Wildhoney · 02-26-2009, 11:11 PM

Yes, those ones are. I am not sure what the name of these tables are. Perhaps ASCII Tables? ASCII Tabular Data?