MySQL - ordering results by others table column
Posted: Sun May 13, 2007 3:51 am
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
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