Mysql select Row number?

[smonkcaptain]

Hey all,

 

I'm wanting to shows users the position of an entry in the database by selecting the the row number of that entry and echoing it.

 

For example, 10 people insert information into a database.

 

On that members page, i want to echo something like this:

 

<?php

$rownumber=//WHAT I CANT DO!

echo 'Your information is current in position'.$rownumber.', it will be reviewed shortly.';

?>

[Pikachu2000]

What if the row number doesn't coincide with the actual position? (I'm assuming this is perhaps a queue for approval, or something like that).

[smonkcaptain]

Yes, it is for approval of photo's.

 

I thought about getting the row number of the id, however, if the information/photo is approved or declined, the row will then be deleted, so i want the row number to be updated.

 

So:

 

name example

jim      ex1

bob      ex3

henry  ex2

 

at the moment 'Bobs' entry is position 2 in the queue, however if 'Jims' entry is either approved or declined, the row will be deleted, and bobs entry will now become position 1 in the queue.

 

Hope that clears things up a little better.

[Pikachu2000]

I would query the DB for those that are pending approval, and figure out the position in the queue for the user programatically, rather than trying to update a field in each record every time any record changes.

[Zane]

Manipulating an id/index isn't good practice at all.  Unless of course you have customized indeces like A09485, A09525, etc of course.  Having your table's index/primary key set to auto-increment helps keep your database normalized.

 

As far as keeping a records position goes, you'll need another field for it.  Then, before you ever delete a record by id, you first select that records position (saving it in a variable) , delete the record and then do an update that looks something like

 

UPDATE table SET position = position -1 WHERE position > {variable}

 

Also, I'd take pikachu's suggestion and create another field for status (pending, approved, etc)