pull up Last Post with topic title name

[dadamssg]

Developing a forum. i have a table named Topics with the topic_name, content, create_date, created_by, and topic_id. And then i have a table name Responses with content, create_date, created_by, topic_id, and response_id. I want to list out the Topic names and then have the when the last response and who it it was by next to it, exactly like this forum. I am clueless how i would write the query to do that though.

 

i need to combine this to pull up all topics

$query = "SELECT * FROM Topics WHERE 1 ORDER BY create_date DESC";

 

with another query to pull up the last post date and user.

 

anybody know how to do this?

 

[Bjom]

Just JOIN the tables (and don't use the asterisk - in joint tables it will lead to problems anyway)

What is "WHERE 1" good for btw?

 

SELECT Top.topic_name, Res.create_date, Res.created_by FROM Topics AS Top JOIN Responses as Res ON Top.topic_id = Res.topic_id ORDER BY create_date DESC LIMIT 1;

[dadamssg]

alright, what if i want to pull the create_date from the topic as well?

[dadamssg]

oh and does the limit 1 apply to the topics as well? i want to list ALL the topics i have in the table

[Bjom]

did you try and simply run the query without the limit 1?

did you try to simply write the extra field you want to query to the field list?