Row numbering help???

[New Coder]

Hello All,

 

I have a comments table:

 

ID int autoincrement,

Name varchar(50),

Comments varchar(2000),

Safe char(1) default (N).

 

Select * from comments;

 

ID      Name                Comment            Safe

1 Joe Bloggs Comment 1 Y

2 John Doe            Comment 2 N

3 Jane Doe Comment 3 Y

4 Joe Bloggs Comment 4 Y

5 John Doe Comment 5 Y

6 Joe Bloggs Comment 6 Y

 

I am using the following query to output 4 random site comments that has been approved and set as safe.

 

SELECT * FROM tbl_dyk where safe=’Y’ ORDER BY RAND() limit 4

 

ID      Name                  Comment          Safe

1 Joe Bloggs Comment 1 Y

3 Jane Doe Comment 3 Y

4 Joe Bloggs Comment 4 Y

6 Joe Bloggs Comment 6 Y

 

Which works a treat.

 

What I also need is to assign the rows a new number from 1-4 like so:

 

ID      Name                  Comment          Safe        Row_num

1 Joe Bloggs Comment 1 Y 1

3 Jane Doe Comment 3 Y 2

4 Joe Bloggs Comment 4 Y 3

6 Joe Bloggs Comment 6 Y 4

 

Is this possible?

Many Thanks

 

[trq]

Yes its possible, but when you say assign do you mean you want to save these numbers back into the database or just use them for display reasons?

[New Coder]

Sorry, Yes I just want to use them for display reasons.

[trq]

Simple then, just increment a number as you iterate through your loop. eg;

 

$i = 1;
while ($row = mysql_fetch_assoc($result)) {
  echo $row['ID'] . ' ' . $row['Name'] . ' ' .  $row['Comment'] . ' ' . $row['Safe'] . $i . '<br />';
  $i++;
}

[New Coder]

Spot on, many thanks.