Numbering posts based on datetime in MySQL
SELECT * FROM forum WHERE topic_id = 9 ORDER BY datetime ASCOn another page I want to create a link that will point to any specific post in a topic. Eg:
http://www.example.com/forum/viewtopic.php?id=9#post-3. . . which of course would be the third post in the topic. However, I’m having trouble getting that post number via a MySQL query. What I’ve tried is counting the number of posts that are older than the one that I’m trying to query. Eg:
SELECT forum.*, forum2.post_id FROM forum LEFT JOIN (SELECT COUNT(*) AS post_id FROM forum AS f2 WHERE f2.datetime <= forum.datetime) forum2 ON forum.id = forum2.id. . . or something like that. The problem is that I of course can’t compare the datetime in my LEFT JOIN to the datetime in my main SELECT, so I can’t determine the number of posts that are older than the one I’m trying to query.
Is there any way to do a COUNT() outside of a LEFT JOIN but still give it a specific condition like in my WHERE clause? Something like:
SELECT *, COUNT(SELECT * FROM forum AS forum2 WHERE forum2.datetime <= forum.datetime) FROM forum. . . which obviously doesn’t work, but is there anything along those lines?
Thanks.
View full post on Tycoon Talk
Based, datetime, mySQL, Numbering, posts
RSS Site Feed