Update Order
Posted: Thu Aug 23, 2007 4:33 pm
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:
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?
This is what I came up with, but it wont for:update.php?order=3,5,1,4,2,0,
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."'");
}
?>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?