Updating Mysql

[runthis]

My problem is, i want to update the database by renumbering all fields, heres what i got

 

 

$unique
$q=mysql_query("SELECT * FROM table WHERE something='$unique'");
$p=0;
while($r=mysql_fetch_array($q)){
$p++;
print $p;
mysql_query("UPDATE table SET field='$p' WHERE something='$unique'");
}

 

Obviously this script creates a loop where 'field' gets updated as many times as there are rows and ends up updating all 'field's to the same value

What i want it to do is update each field to reorder that field so if the fields in order were 1,2,5,6 it would change them to 1,2,3,4.

 

[Pikachu2000]

Before I suggest anything, is this field used as an index, and what is the reasoning behind wanting to do this?

[runthis]

i have a primary key set to auto increment but thats for the id not for the field i want to update, let me try to give you a better understanding

 

DATABASE

 

IDNameField2update

234john1

235john2

236john3

 

if i deleted id:235 it would look like

 

IDNameField2update

234john1

236john3

 

I want to reorder 'john' to look like

 

IDNameField2update

234john1

236john2

 

Where im changing the 'Field2update'

[ChrisA]

You need to limit your update to the particular row you're considering in the current cycle of the loop. 

 

$unique
$q=mysql_query("SELECT * FROM table WHERE something='$unique' ORDER BY Field2Update ASC");
$p=0;
while($r=mysql_fetch_array($q)){
$p++;
print $p;
mysql_query("UPDATE table SET Field2Update ='$p' WHERE something='$unique' AND Field2Update = '".$r['Field2Update']."'");
}

 

I've also added 'ORDER BY Field2Update ASC' to your select query to ensure your rows come out in the order you are expecting them.  I've assumed the value for Field2Update is available from the select query as $r['Field2Update'].

[runthis]

i feel dumb for not thinking of that first, lemme give it a go

[runthis]

Works like a charm, thanks for making me feel dumb

 

RESOLVED