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.
