[SOLVED] Ordering a joined table

[Anzeo]

Hey,

 

 

 

I'm currently working on my own forum and so far everything's working. I have made four tables, CATEGORY, FORUM, TOPIC, POST.

I've completed the category part of my forum and I am almost done with the forum coding, but there's one question which I can't answer.

 

I want to order my posts/topic on date,time (in that order), but I have no clue whatsoever on how to do this. I mean, when a new topic is made it's added to the TOPIC table (so no data added in the POST table) and so the topic's date is stored in the TOPIC table, but a reply is added to the POST table and it's date and time are stored in the POST table.

 

How do I have to make my query in order for it to sort my posts and topics on date? (like on this forum for example).

 

Any help greatly appreciated.

 

 

 

Regards,

Anzeo

[utexas_pjm]

SELECT * 
FROM TOPIC t 
  INNER JOIN POST p 
  USING (topic_id) 
WHERE t.topic_id = 1 
ORDER BY 
  p.date_posted, 
  p.time_posted DESC

 

Best,

 

Patrick

[Anzeo]

Hey,

 

 

Thanks for the reply, but I tried it out and it's giving me this error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 WHERE TOPIC.ID = 1 ORDER BY POST.Datum DESC, POST.Tijd DESC' at line 1

 

and this is the query I wrote

 

$tpcqry = "SELECT * FROM TOPIC INNER JOIN POST USING $FID WHERE TOPIC.ID = 1 ORDER BY POST.Datum DESC, POST.Tijd DESC";

 

any idea what I'm doing wrong here?

[utexas_pjm]

<?php
$tpcqry = "SELECT * FROM TOPIC INNER JOIN POST ON (TOPIC.ID = POST.TOPICID) WHERE TOPIC.ID = " . mysql_escape_string($id). " ORDER BY POST.Datum DESC, POST.Tijd DESC";
?>

 

POST.TOPICID should be whatever you use in the POST table to realate the post to a topic.

 

Best,

 

Patrick

[Anzeo]

Hmm it's not working correctly.

 

I wander why I need to use a where clause, cause I want it to list all my topics. and now it's only outputting one topic. But it repeats it as much as there are replies in it.

[utexas_pjm]

I misunderstood your problem.  You want something like this:

 

SELECT * FROM
(
   SELECT * FROM TOPIC 
   INNER JOIN POST ON (TOPIC.ID = POST.TOPICID) 
   GROUP BY TOPIC.ID HAVING (MAX(POST.POST.Datum))
) AS topics
ORDER BY topics.Dautm DESC, topics.Tijd DESC

 

Patrick

[Anzeo]

Hey Patrick,

 

thanks for your help and time so far.

 

I've now wrote my query as:

$tpcqry = "SELECT * FROM ( SELECT * FROM TOPIC INNER JOIN POST ON TOPIC.ID = POST.Topic GROUP BY TOPIC.ID HAVING (MAX(POST.Datum))) AS TOPICS ORDER BY TOPICS.Datum DESC, TOPICS.Tijd DESC";

 

And this is the output  I get:

Column 'TOPICS.Datum' in order clause is ambiguous

 

Could this problem be caused because of the fact that I have a Datum column in both my TOPIC as my POST table?

 

 

 

Regards,

Anzeo

 

 

[utexas_pjm]

Yes, you'll need to explicitly alias the columns you want in the inner select:

 

$tpcqry = "SELECT * FROM ( SELECT TOPIC.Datum as td, POST.Datum as pd, etc..etc.. FROM TOPIC INNER JOIN POST ON TOPIC.ID = POST.Topic GROUP BY TOPIC.ID HAVING (MAX(POST.Datum))) AS TOPICS ORDER BY TOPICS.pd DESC, TOPICS.Tijd DESC";

[Anzeo]

I think it's working I need to alias all my colums though, but I think it's ok.

 

Thank you very much for your time and great help! I'll reply when I checked everything (I unfortunatly don't have much time right now).

 

 

 

Cheers,

Anzeo

[Anzeo]

OK I think I almost got it

 

But there's one problem and I think I knoiw what's causing it it, but I can't figure out how to adjust the query to solve it.

The problem is the output is not returning topics with no posts (so no replies). I tried right or left join but nothing changed.

 

EDIT: Hmm, apparently for some strange reason it's not updating correctly. At first it seemed working but the order of the topics does not change when I add a reply.

[Anzeo]

Okay after taking a look at it, I still haven't figured out yet what's wrong, I did exactly as you said, but the topics are ordered by a specific order and won't be rearranged again.

 

Can you help me out with this last bit, it's nearly done

[utexas_pjm]

I think the problem is that you are storing your dates and times in two differenct columns.  Combine them into one datetime column, then the HAVING (MAX(POST.POST.Datum)) will give you the most recent TOPIC

[Anzeo]

Okay and how should I store them best,

 

like: 2007-04-25 18:12:00?

 

Or should i use a clear seperator? If I'm correct, I could use space as a sepperator right?

 

Thanks!

[utexas_pjm]

MySQL datetime is in the form Y-m-d H:i:s. The way you have it looks correct.

[Anzeo]

Ok I switched my table structures and everything is still working great except for two consistent problems.

 

My query:

 

$tpcqry = "SELECT * FROM ( SELECT TOPIC.ID AS TID, TOPIC.Datum AS TDatum, POST.Datum AS PDatum, TOPIC.Titel AS Titel, TOPIC.Forum AS Forum, TOPIC.Auteur AS TAuteur FROM TOPIC INNER JOIN POST ON TOPIC.ID = POST.Topic GROUP BY TOPIC.ID HAVING (MAX(POST.Datum))) AS TOPICS WHERE Forum='$FID' ORDER BY TOPICS.PDatum DESC";

 

Datum column format "0000-00-00 00:00:00".

 

The problems I get while outputting:

 

1. When a topic hasn't been replied to, it does not show up in the list.

2. It's still not ordering correctly :/

 

Do you have a clue at what I'm doing wrong here?

 

I cannot repeat "thank you" enough

[Anzeo]

I still haven't found out, going to test a little but more. Does anyone have clue what I'm doing wrong here?

[per1os]

"SELECT * FROM ( SELECT TOPIC.ID AS TID, TOPIC.Datum AS TDatum, POST.Datum AS PDatum, TOPIC.Titel AS Titel, TOPIC.Forum AS Forum, TOPIC.Auteur AS TAuteur FROM TOPIC INNER JOIN POST ON TOPIC.ID = POST.Topic GROUP BY TOPIC.ID HAVING (MAX(POST.Datum)) ORDER BY COLUMN HERE) AS TOPICS WHERE Forum='$FID' ORDER BY TOPICS.PDatum DESC";

 

Note the "ORDER BY COLUMN HERE" portion. Dunno if it will work but give it a try.

[Anzeo]

Nope, still the same problem.

[utexas_pjm]

If you wouldn't mind giving me your schema (either post it here or send it via PM) I'll write and test the SQL for you.

 

Best,

 

Patrick

[Anzeo]

what do you mean by schema? My table structure etc?

[utexas_pjm]

Yeah.  A SQL dump would be awesome.  But if you can't get that, the output of:

 

DESCRIBE <tablename>;

 

for each table involved will work.

 

Patrick

[utexas_pjm]

This should do it.

 

SELECT * FROM (
SELECT TOPIC.ID AS TID,
  TOPIC.Datum AS TDatum,
  POST.Datum AS PDatum,
  MAX( IFNULL(POST.Datum, TOPIC.Datum)) as orderByDatum,
  TOPIC.Titel AS Titel,
  TOPIC.Forum AS Forum,
  TOPIC.Auteur AS TAuteur
FROM TOPIC
LEFT JOIN POST ON (TOPIC.ID = POST.Topic)
GROUP BY TOPIC.ID HAVING (MAX(TO_DAYS(IFNULL(POST.Datum, TOPIC.Datum)))))
AS TOPICS
ORDER BY TOPICS.orderByDatum DESC

 

Good luck,

 

Patirck

[Anzeo]

It works !

 

Yes! Thank you SO much for your help and time, Patrick!

[utexas_pjm]

Good luck,

 

Patirck

 

Who misspells their own name? (sigh)

 

Glad that worked out for you.

 

Patirck[sic]