[RESOLVED] Sequential numbers

jbachris

Adding rank field in database.

I have a query that gets all the news stories from my table by date. What I'd like to do is on each row insert an entry into the field rank that starts with 1 for the first row, then goes in sequential order. So the first would would be 1, the second would be 2, etc, through all the rows.

Here is the code I have so far:

<?

require ( "conf.php" );

$stories_rs = $conn->Execute ( "SELECT * FROM story ORDER BY date DESC" ) or die ( $conn->ErrorMsg() );

while ( ! $stories_rs->EOF ) {

$sql = "UPDATE story SET rank = $rank WHERE storyID = $storyID";
$add_rank_rs = $conn->Execute($sql) or die ( $conn->ErrorMsg() );

$stories_rs->MoveNext(); }

?>

I want to replace $rank with the numbers...

laserlight

Why not insert the current datetime? That way you do not need to worry about what happens if an entry is deleted.

jbachris

laserlight wrote:
Why not insert the current datetime? That way you do not need to worry about what happens if an entry is deleted.

Because I want to enter sequential numbers...

laserlight

Because I want to enter sequential numbers...

Once you order by the datetime, numbering sequentially is easy, even if there were deletions.

If you really insist on numbering sequentially in the database itself, then autoincrement could be an option, but there may not be a guarantee that the order will be sequential, and then there is the problem of deletions.

jbachris

Another example is if I want to rate things, 1-20, I'd like to rate them and store that rating in the database, where the unique ID or timestamp wouldn't work...

If I have 20 entries in the database, and want to rank them by something else other than date, how could I do that?

laserlight

You could just provide an arbitrary numeric field by which to order the rows and present this to the user in a form. You could enforce a UNIQUE constraint if necessary.

jbachris

I have news stories, every time they're viewed it updates the db to keep track of the views they have.

What I'm trying to do is once a week run a query that ranks the top 20 viewed stories. I want to stor the rank (1-20, with one being for the most viewed story) of the story in the DB, then also store the previous week's rank, and when exporting the results I'd like to show This Weeks Rank | Last Weeks Rank | Story

jbachris

I figured it out, using $i=1 outside the loop, then using $i++; inside the loop 🙂