CODEWITHSUNDEEP
php
Remy
Remy

Reputation:

Number rows not column id

    $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

Upvotes: 0

Views: 178

Answers (4)

Brad Knowles
Brad Knowles

Reputation: 119

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.

Upvotes: 0

Gumbo
Gumbo

Reputation: 655489

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

Upvotes: 3

CTT
CTT

Reputation: 17641

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

Upvotes: 0

DFectuoso
DFectuoso

Reputation: 4897

$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! =)

Upvotes: 3

Related Questions