MySQL - ordering results by others table column

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
KalleL
Forum Newbie
Posts: 12
Joined: Mon Apr 30, 2007 8:52 am

MySQL - ordering results by others table column

Post 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
Last edited by KalleL on Mon May 14, 2007 11:36 am, edited 1 time in total.
mentor
Forum Contributor
Posts: 100
Joined: Sun Mar 11, 2007 11:10 am
Location: Pakistan

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Descriptive subjects

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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;
KalleL
Forum Newbie
Posts: 12
Joined: Mon Apr 30, 2007 8:52 am

Post 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
Last edited by KalleL on Wed Aug 15, 2007 4:16 am, edited 1 time in total.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Are you sure? What date does `messages`.`date` refer to and why are you ordering it ascendingly?
KalleL
Forum Newbie
Posts: 12
Joined: Mon Apr 30, 2007 8:52 am

Post 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.
Post Reply