Number rows not column id

Question

    $query = mysql_query("
    SELECT * FROM comments
    ORDER BY comments.comment_date ASC");

    while ($row = mysql_fetch_assoc($query)) {
    .......
    }

How would I do to "number" the comments.

like this site

you know...

1.
2.
3.
4.
5.
...

Thanks

Answer 1

$i = 0;
while ($row = mysql_fetch_assoc($query)) {
   $i++;
   print $i; 
   .......
}

You can count in your cycle, so you get a consistent numbering not related to the database! =)

Answer 2

$query = mysql_query("
SELECT * FROM comments
ORDER BY comments.comment_date ASC");
$num = 1;
while ($row = mysql_fetch_assoc($query)) {
    echo $num++;
    .......
}

Answer 3

You could also use an orderer list (OL element).

Answer 4

Admittedly, my background is more in MS SQL than MySQL, but it sounds like you're looking for a way to accomplish what ROW_NUMBER() (msdn article) accomplishes in MS SQL 2005. Prior to 2005, one way to number result rows was accomplished by creating a Stored Procedure to return a table variable (or temporary table). So you'd create a temporary representation of the table, including a new column for RowNum and have the stored procedure return the new table. For example...

These are MS SQL Commands, but I assuming MySQL ones are similar

CREATE TABLE @TempComments
(
    RowNum smallint not null IDENTITY(1,1),
    ...other fields...
)

INSERT INTO @TempComments (all fields except RowNum)
SELECT * FROM comments

Have the stored procedure return @TempComments to your application and now you have a comments table with a sequential Row number for each row.

Looks like MySQL implemented Stored Procedures in version 5.0, so hopefully that's what you're using.

Hope that helps, others feel free to add the proper MySQL syntax.

Answer 5

If you don't want to use the HTML "Ordered Link" tag, and need the numbering from within MySQL: tis is a snippet from the MySQL forum (so I haven't tried it myself)

set @i = 0;
SELECT *, @i:=@i+1 as myrow  FROM comments
ORDER BY comments.comment_date ASC;