I have the posts in my message board selected via a topic ID and ordered by their datetime. Eg:

SELECT * FROM forum WHERE topic_id = 9 ORDER BY datetime ASC

On 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.

You can't do that, a query returns a static block of rows comprising of the columns requested.

To add sequential numbering of the rows you will either have to write a stored procedure to create the recordset or add the numbering as you output the records to the user agent.

I've written a query that generates a post number based on the MAX datetime, though.

SELECT post_id FROM forum 
LEFT JOIN (SELECT forum.topic_id, count(*) AS post_id FROM forum 
LEFT JOIN (SELECT topic_id, MAX(datetime) AS datetime FROM forum WHERE user_id = 'MYID' GROUP BY forum.topic_id) forum2 ON forum2.topic_id = forum.topic_id 
WHERE forum.datetime <= forum2.datetime GROUP BY forum.topic_id) f ON f.topic_id = forum.topic_id

. . . and this gives me the correct post ID for the most recent post in a topic by a particular user (whoever is defined by 'MYID'). It's inelegant but it works and allows me to compare two instances of the datetime field from a single table. I've been trying to tweak it so that the second LEFT JOIN gives me the datetime of the post that I'm querying rather than the MAX datetime, but I think that the problem is my ON clause.

it's also very inefficient.

I hope your system doesn't have many concurrent users to deal with.

Is it less efficient than running another query completely for each row of my initial output? Eg:

while ($result = mysql_fetch_array($myOriginalQuery)) {
  $queryPostNumber = mysql_query("SELECT count(*) FROM forum WHERE datetime <= ."$result["datetime"];

Originally Posted by Kent O'Matic

Is it less efficient than running another query completely for each row of my initial output? Eg:

while ($result = mysql_fetch_array($myOriginalQuery)) {
  $queryPostNumber = mysql_query("SELECT count(*) FROM forum WHERE datetime <= ."$result["datetime"];

The latter will rapidly become the less efficient as number of posts rises.

However, take a step back - is it important to you to have the sequential numbering ofposts or is it just an anchor point? If the latter, apply a post id on insertion to the table and use that in the anchor.

SELECT
  @row := @row + 1 AS row,
  f.*
FROM
  forum f,
  (SELECT @row := 0) r
WHERE
  f.topic_id = 9
ORDER BY
  f.datetime ASC

Nice and in a single DB pass as well.

I'd better do some brushing up on MySQL

Originally Posted by mgraphic

SELECT
  @row := @row + 1 AS row,
  f.*
FROM
  forum f,
  (SELECT @row := 0) r
WHERE
  f.topic_id = 9
ORDER BY
  f.datetime ASC

I'll definitely give this a try, but I've done something similar (can't remember HOW similar, though. It may have been exactly that) and it spat out incorrect values for 'row.' Regardless of what I used for my ORDER BY clause it seemed to be assigning row numbers as if I were always ordering it by the index.

Thank you, either way.