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
MySQL - ordering results by others table column
Moderator: General Moderators
MySQL - ordering results by others table column
Last edited by KalleL on Mon May 14, 2007 11:36 am, edited 1 time in total.
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Descriptive subjects
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.
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:
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;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):
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
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;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
Last edited by KalleL on Wed Aug 15, 2007 4:16 am, edited 1 time in total.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm