[RESOLVED] Renaming with progressive numbers

[iantimmy123]

I have programme and I am making some tools to sort out errors. I have a table which sometimes (due to my programming) numbers have been changed by accident. I now need to change them but do not want to do it manually.

Basically the rows look something like this

Name, Age, Ref.
Matt, 23, 1
Luke, 25, 2
James, 30, 3
Mark, 20, 3 (this should be 4 but has been changed to 3 and so isnt a unique ref number)

The ref numbers should all be unique and numbered from 1 up but it has all messed up and I want to change all the ref numbers to 1,2,3... etc. It doesnt matter who has which number but I need to change lots of them so that the ref column counts from 1 up. Does anyone have any ideas. I though of using a foreach loop with a mysql query in but unsure. Appreciate any help. Thanks.

[Drakla]

Create a new table with a primary key that is auto increment as the ref with the same fields as your other table. Then run an insert / select into the new table leaving out the reference column, something like

INSERT INTO newtable (name, age, whatever) SELECT (name, age, whatever) FROM oldtable;

Then delete the old table and rename the new table.

[esukf]

Since you don't need the numbers in any specific order, add a primary key/ auto increment column in the table then delete your ref column.

[iantimmy123]

Right ok, i'll try and programme that lol.

Thanks for the help

[Installer]

This should work, although the previous two suggestions are both better (slow, maybe, but for a one-time job...):

$result = mysql_query("SELECT name FROM my_table");
while ($row = mysql_fetch_assoc($result) {
    $arr = $row['name'];
}

$counter = 1;
foreach ($arr as $name) {
    mysql_query("UPDATE my_table SET ref = " . $counter++ . " WHERE name = '" . $name . "'");
}

[iantimmy123]

Thanks all appreciate the help. I'll have a look