[SOLVED] Reordering items in a table

[knowj]

I have been playing about with jquery and have got a drag and drop reordering script working (client side) I now need to somehow work this into the database environment.

 

example table

p_id, p_name, p_order
8    , john      , 1
16    , fred       , 2
2    , james    , 3
5    , james    , 4
11    , james    , 5
10    , james    , 6

 

if i was to move john to position 3 i would get an array that looks like

$matches[0] = Array
(
    [0] => 2
    [1] => 3
    [2] => 1
    [3] => 4
    [4] => 5
    [5] => 6
)

 

The logic is where I'm stuck how can i now convert the order into the new order?  ???

 

Thanks in advance

J

 

[F1Fan]

I created a similar script. Try modding this to your needs:

 

<?php
function MoveDir2($empnum,$row,$dir){
global $db;
if ($dir=="up") $new = $row-1;
else $new = $row+1;
$query = "SELECT sort_order, empnum, title, description, image, feature, action FROM custom_leftmenu WHERE " .
		"empnum = $empnum ORDER BY sort_order";
$items = $db->getAssoc($query);
// Here's what should work for ya:
$move1 = $items[$new];
$move2 = $items[$row];
$items[$row] = $move1;
$items[$new] = $move2;
ksort($items);
// to here...
$q = "DELETE FROM custom_leftmenu WHERE empnum = $empnum; ";
$count = 1;
foreach ($items as $row){
	$q .= "INSERT INTO custom_leftmenu (sort_order, empnum, title, description, image, feature, action) " .
			"VALUES ($count, $empnum, '{$row['title']}', '{$row['description']}', '{$row['image']}', " .
			"'{$row['feature']}', '{$row['action']}'); ";
	$count++;
}
return pg_query($q);
}
?>

[knowj]

Apologies its giving me the new order of the p_id so in theory i should be able to go through assigning new ID's based on that.

 

$matches[0] = Array
(
    [0] => 5
    [1] => 54
    [2] => 27
    [3] => 15
    [4] => 23
)

 

 

foreach ($match[0] as $name => $name)
{
$query = "update `names` set `order`='$name' where `id`='$value'";
$this->execute($query);
}

 

the only place i can see this being problematic now is if the ordering is done across multiple pages the current method would only work for a single page ordering system.

[F1Fan]

I have another one that works on a row and column system. Basically, we have a "dashboard" that is the home page on our intranet. It has tons (potentially) of items on it, and this other script allows them to move items from one column to another, up a row, or down a row. Let me know if you want it.

[knowj]

thanks for the offer but i will be fine from here on in. labeling my arrays from 0-6 in the test environment just put me into confusion. Now i have the logic sorted I can program away.

 

Cheers for the help

[akitchin]

if you're trying to physically re-order using p_order, you simply need to follow a series of calculations:

 

1. either increment all p_orders above and equal to the target p_order (if the item is currently ABOVE the target p_order), or decrement all p_orders below and equal to the target p_order (if the item is currently BELOW the target p_order)

2. change the item to the target p_order.

 

then order your query by p_order.