as you can guess table 1 keeps the topics and table 2 contains the posts for topics.
Now I want to display last 10 topics on homepage with following data:
- name of topic,
- the id_user who posted last to the topic
and want to order them in a way that the topic which has the last post will be displayed at top.
is it posibble to make all with one query. Or is it a better way to handle it with php? Which one is faster? which one is reliable?
If it is posible to make it with one query how can I do this. I tried some querries but cant order it by last post
FROM forum_topics AS t
JOIN forum_posts AS p
ON p.id_topic = t.id_topic
AND p.post_date =
( SELECT MAX(post_date)
WHERE id_topic = t.id_topic )
BY p.post_date DESC
Thank you sooo much r937,
query works fine but I need to group by id_topic because I want to display each topic once.
When I try grouping it, result don't come up with id_user who posted last to the topic
I know it is possible to get last poster with a new query in php while statement but this means making the same thing with 11 queries instead of one. I think this is not the best way.
Another way I can think is to push redundant data to forum_topics. (a new field like last_poster_id and updating it for each post). But I know this also is not a good approach.