Page 1 of 1

Update Order

Posted: Thu Aug 23, 2007 4:33 pm
by SidewinderX
I'm trying to write a query to reorder data in my database. The data is received via GET in a comma delimited string. This is what we have to work with:
update.php?order=3,5,1,4,2,0,
This is what I came up with, but it wont for:

Code: Select all

<?php
mysql_connect('localhost', '', '');
mysql_select_db('dd');

$newOrder = $_GET['order'];
$finalOrder = explode(",", $newOrder);

for($i=0; $i < count($finalOrder); $i++) {
    mysql_query("UPDATE `layout` SET `order` = '".$finalOrder[$i]."' WHERE `order` = '".$i."'");
}
?>
DB ORDER: 0,1,2,3,4,5
NEW ORDER: 3,5,1,4,2,0

Loop 1
SET 3 = 0
DB ORDER: 3,1,2,3,4,5
NEW ORDER: 3,5,1,4,2,0

Loop 2
SET 5 = 1
DB ORDER: 3,5,2,3,4,5
NEW ORDER: 3,5,1,4,2,0

Loop 3
SET 1 = 2
DB ORDER: 3,5,1,3,4,5
NEW ORDER: 3,5,1,4,2,0

Loop 4
SET 4 = 3
DB ORDER: 4,5,1,4,4,5
NEW ORDER: 3,5,1,4,2,0

When it gets to the 4th loop there are two elements in the database that have their order set to three, how does it know which one to update? It don't, and this is my dilemma, how should I go about doing this?

Posted: Thu Aug 23, 2007 4:43 pm
by volka
You probably need an id field in the table layout, a value that doesn't change and identifies a record.
Then you can create a link like update.php?order[1]=3&order[2]=5&order[3]=1&order[4]=4&order[5]=2&order[6]=0

Code: Select all

foreach ($_GET['order'] as $key=>$value) {
  $query = 'UPDATE layout SET `order`=' . (int)$value . ' WHERE id=' . (int)$key;
 ...
}