Apply row number to a field of a record

[luciform]

Hi,

 

I've discovered I can use the query below to give the row number of each record of a database table:

 

SELECT @row := @row + 1 as row, t.*
FROM tbl_name t, (SELECT @row := 0) r

 

In phpmyadmin, this query creates a new field on the table called 'row', and assigns a value of '1' to the first record, '2' to the second record, and so on. The problem I have is that this new field is temporary, i.e. if I execute another statement, it will disappear.

 

In my table I have a field called 'index'. What I want to be able to do is to set this 'index' field of all records to be equal to that record's row number. I'm guessing what I need is a subquery of some kind, something like:

 

UPDATE tbl_name SET index = ??rownumber?? WHERE (____)

 

I need something like that to change a specified record, as I can then use a PHP loop to do that for each row in turn. What I don't know is what should go in the ____ section. I'm guessing it uses some of the syntax from the statement I printed at the top? I've played around with it in phpmyadmin's SQL console but get various errors.

 

The ??rownumber?? can be a variable in PHP that starts at 1 and increments each time the loop iterates.

 

I realise if I set this 'index' field to auto-increment it would have the same effect, but elsewhere in my PHP script I am deleting records from the table and adding new ones, and that plays havoc with the auto-increment, so I want to be able to reassign the index to each record using that UPDATE statement.

 

Any ideas? I hope that makes sense.

 

Thanks.

[smerny]

I would think that this would cause more havoc than using an auto increment. what is wrong with having a gap in numbers if something gets deleted? they will still sort the same and such. that is the way this forum (and most sites) work.

 

i'm not sure what the context is in your case... but if you have something that uses record with the index 15 for example, and the record with index 12 gets deleted... the record that was once 15 is now 14... if you try to pull 15 you'll now get whatever was at 16. is this the desired affect?

 

if so... i suppose deleting the auto-incrementing index field and re-adding an auto-incrementing index field would have the result you want?

[mikosiko]

@luciform : - Just use the auto-increment, the way that it behave is the right way.

                  - You better rename your "index" field... "index is a MYSQL reserved word... otherwise you will need to enclose it in backticks every time you need to use it.

[luciform]

Thanks for your advice. I've changed some code and managed to use the auto-increment for everything I need. I also renamed the 'index' field to 'row_number' to solve the conflict with the reserved word.