Page 1 of 1

PHP & MySQL

Posted: Sat May 20, 2006 7:53 pm
by Mortat
Well, it's mostly about MySQL though, but I couldn't find a subforum about it. The things is, I'm building a small forum for my website and am having a few problems with sorting the topics and reply's on date. My forum works as following (well, as soon as I get things worked):

Mainforum
- Subforums
-- Topics
--- Topic message + reply's

When you click on one of the subforums, you will get a list with all the topics in that subforum. They have to be sorted by date & time from the last post. My tables are as following:

TOPIC
subforum_id
topic_id
title
post
date
time
sticky

REPLY
subforum_id
topic_ud
post
date
time

I had the following query:

Code: Select all

SELECT   * 
FROM     topic AS topic, reply AS reply
WHERE 	topic.forum_id = '$subforum_id' OR   // not AND, because reply can be empty
			reply.forum_id = '$subforum_id'
ORDER BY topic.sticky DESC,    // first the sticky ones on top
			topic.datum DESC,     // then sorting dates (topic or reply, doesn't matter which one first)
			reply.datum DESC,
			topic.tijd DESC,      // then sorting on times
			reply.tijd DESC
But it doesn't work at all! And I don't know how to solve it, since I haven't worked much with multiple tables in one query. I do have another solution if there is no way to solve this, but it involves changing my tables and a hell lot of code. Now, start breaking your heads over this! :)

Posted: Sat May 20, 2006 8:11 pm
by bdlang
You can't use a SELECT ALL (*) because you have ambiguous column names on both tables. You have to select each column by name and most of them will have to have an ALIAS, e.g.

(untested example, modify as needed)

Code: Select all

SELECT
 t.subforum_id AS T_SID
, t.topic_id AS TID
, t.title
, t.post AS topic_post
, t.date AS topic_date
, t.time AS topic_time
, t.sticky
, r.subforum_id AS R_SID
, r.topic_id AS RID
, r.post AS reply_post
, r.date AS reply_date
, r.time AS reply_time
FROM TOPIC AS t
INNER JOIN REPLY AS r
WHERE ...

ORDER BY ...
Hopefully you get the idea.

BTW, saying 'it doesnt work at all' doesn't say much, please be descriptive. What is the error returned from MySQL? Your original query has you assigning an alias the same name as the table, doesn't have an effect. In order for an alias to be usefull, try using a single letter (as in 't') or something that is non-ambiguous and doesn't match up with a different table or column. I'd also address the fact that you have a `date` and `time` column on each table... why not use a TIMESTAMP or DATETIME and get both in one? You can use DATE_FORMAT() to get any value back you want.

Start with that query, add your own stuff, post back if needed.