Page 1 of 1

MySQL - ordering results by others table column

Posted: Sun May 13, 2007 3:51 am
by KalleL
Hello. I'm not sure if this more MySQL or PHP problem but i'll put it here.

Okay, i'm developing simple forum app for a website. Now, i've little problem, hope someone can give me an idea how to solve it.

---

I have 2 tables:

for_topics

id | mediumint(8) unsigned // unique id for topic
catID | mediumint(9) // category id
title | varchar(100) // topic title
firstPost | mediumint(9) // first post's id
lastPost | mediumint(9) // last post's id (newest reply)
visible | smallint(6) // int to limit which user groups can view topic
locked | tinyint(4) // 1/0 replying locked

for_messages

id | int(11) // unique id for
userID | mediumint(9) // poster's id
topicID | mediumint(9) // topic's id
message | text // msg's text
msgtime | varchar(100) // post's time
visible | smallint(6) // again, who can see this post

---

So this is my database structure. Problem is this: when i'm listing topics, is there anyway to easily order topics by last post date? I know i can make one column for for_topics -table, but is there any "clean way" so i don't need to store info to 2 tables a time? (I'm using PHP+MySQL)

Thanks for help, and sorry admin if this is in wrong place.


Regards,
Kalle

Posted: Sun May 13, 2007 6:03 am
by mentor
Why you have type varchar for 'msgtime' column? Can't you make this datetime? After making this column datetime, use join to get the info and put ORDER BY on msgtime.

Descriptive subjects

Posted: Mon May 14, 2007 9:04 am
by feyd
Please update the thread subject to be more descriptive.
[url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url] Section 1.1 wrote:2. Use descriptive subjects when you start a new thread. Vague titles such as "Help!", "Why?" are misleading and keep you from receiving an answer to your question.

Posted: Mon May 14, 2007 1:42 pm
by califdon
It appears that you have several things wrong in your schema that will prevent you from doing much of anything with it. Assuming that you want to have messages linked to topics, your foreign key in messages must be the same data type (including size) as the primary key in topics. As mentioned, if you're going to use a timestamp, you must use one of the date data types.

What's the purpose in duplicating the visible property in both tables? Will you sometimes hide the whole topic and sometimes hide just certain messages within a topic?

Do you really need the record id's of the first and last message in the topics record? Why?

Once you clean up your schema, then all you need is a simple join to list all the messages in a topic, ordered by date, either ascending or descending. Something like:

Code: Select all

SELECT * FROM messages INNER JOIN topics ON topics.id = messages.id WHERE visible = 1 ORDER BY msgtime DESC;

Posted: Tue Aug 14, 2007 2:05 pm
by KalleL
Hello, again.

Okay, i've done some progress with this problem. I've managed to build basic forum app, but one thing aint working as i want it to work.

I'm using this MySQL command to retrieve topic listing (thanks for your tips):

Code: Select all

SELECT * FROM `messages` INNER JOIN `threads` ON `threads.id` = `messages.threadID` GROUP BY `messages.threadID` ORDER BY `messages.date` DESC;
Problem is this: it doesn't sort topics by last posts (newest on the top), it is sorting them by threadID. I know that's because of the GROUP BY definition here. But this is necessary for getting topic listed (not all messages in topics) (?). Is there some way to get around this problem, if is, how?

I cleaned tables a little bit, i'll list here columns, if somebody needs them to help me.

threads: id,header,sticky
messages: id,topicID,post,poster,postdate

Thanks for everybody, who's helping newbies around here.

Regards,
Kalle

// Edit: edited SQL code

Posted: Tue Aug 14, 2007 3:02 pm
by superdezign
Are you sure? What date does `messages`.`date` refer to and why are you ordering it ascendingly?

Posted: Wed Aug 15, 2007 4:18 am
by KalleL
Sorry, it supposed to be DESC. It's the field which stores messages post date, and that's why it's ordered by it.