Search code examples
phpsqlforum

Retrieve latest post in each category - Forum

I'm building a forum, and I have ran into a few problems.

The basic database structure looks like this:

users
| user_id | username

categories
| category_id | category_name |

forum_posts
| post_id | ref_post_id (FK) | ref_category_id (FK) | ref_user_id (FK) | post_date |

If ref_post_id is 0 that means it's the main post of the thread that have a title. For answers to a thread ref_post_id equals the main post's post_id. I hope you understand.

How would I get the latest post in each category? Including the posts thread title, and the username from user table. Should I change my table structure and add a "latest_post_id" field to categories table or something?

Very greatful for your help. I know there are similar questions, but I'm also wondering about whether I should store latest_post_id and all that in categories table or have a huge query for retrieving everything on each page load.

EDIT 2: HERE IS MY CURRENT QUERY:

SELECT category_id, name, 
(SELECT COUNT(*) FROM forum_posts WHERE ref_category_id = category_id AND ref_post_id = 0) count_threads
(
SELECT title, ref_user_id, username FROM forum_posts
LEFT JOIN users ON user_id =     ref_user_id
WHERE latest_post_id = (SELECT MAX(latest_post_id) FROM forum_posts WHERE    ref_category_id = category_id LIMIT 1)
)
FROM forum_categories
Solution

  • if you have a creation date you need to get the one with the MAX date, if you don't you can use the MAX(post_id), but if you let user EDIT their post and you want to get the latest one created OR edited you should add a modification date to the database.

    to get the latest post:

    SELECT * FROM forum_posts p 
INNER JOIN users u ON p.ref_user_id=u.user_id
WHERE `post_id`=(SELECT max(`post_id`) FROM forum_posts WHERE ref_category_id=$value);

    If you are using a date, just use that field instead of post_id