0

I'm trying to use the answer here : Move node in nested set

To move a node in a mysql nested set db it appears to work ok except the moving node is set to a negative value and is never changed to the new lft & rgt values. It appears as if Step 4 is not doing anything at all. I've changed the script to match my structure and removed the update parentID in step 4 because I don't store the parentID in my structure.

Can someone tell me why this is not working? here is my code :

// id of moving node 
$iItemId = 13;

// left position of moving node
$iItemPosLeft = 19;

// right position of moving node
$iItemPosRight = 20;

// id of new parent node (where moving node should be moved)
$iParentId = 8;

// right position of new parent node (where moving node should be moved)
$iParentPosRight = 15;

// 'size' of moving node (including all its sub-nodes)
$iSize = $iItemPosRight - $iItemPosLeft + 1;

$sql = array(

// step 1: temporarily "remove" moving node

'UPDATE `RJF_storeNestedCategory`
SET `lft` = 0-(`lft`), `rgt` = 0-(`rgt`)
WHERE `lft` >= "'.$iItemPosLeft.'" AND `rgt` <= "'.$iItemPosRight.'"',

// step 2: decrease left and/or right position values of currently 'lower' items (and     parents)

'UPDATE `RJF_storeNestedCategory`
SET `lft` = `lft` - '.$iSize.'
WHERE `lft` > "'.$iItemPosRight.'"',
'UPDATE `RJF_storeNestedCategory`
SET `rgt` = `rgt` - '.$iSize.'
WHERE `rgt` > "'.$iItemPosRight.'"',

// step 3: increase left and/or right position values of future 'lower' items (and parents)

'UPDATE `RJF_storeNestedCategory`
SET `lft` = `lft` + '.$iSize.'
WHERE `lft` >= "'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iSize : $iParentPosRight).'"',
'UPDATE `RJF_storeNestedCategory`
SET `rgt` = `rgt` + '.$iSize.'
WHERE `rgt` >= "'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iSize : $iParentPosRight).'"',

// step 4: move node (ant it's subnodes) and update it's parent item id

'UPDATE `RJF_storeNestedCategory`
SET
    `lft` = 0-(`lft`)+'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iItemPosRight - 1 : $iParentPosRight - $iItemPosRight - 1 + $iSize).',
    `rgt` = 0-(`rgt`)+'.($iParentPosRight > $iItemPosRight ? $iParentPosRight - $iItemPosRight - 1 : $iParentPosRight - $iItemPosRight - 1 + $iSize).'
WHERE `lft` <= "'.(0-$iItemPosLeft).'" AND i.`rgt` >= "'.(0-$iItemPosRight).'"');


foreach($sql as $sqlQuery){
  mysql_query($sqlQuery);
}

I'm also interested if anyone knows how to use this to move a node left or right within its existing parent.

Community
  • 1
  • 1
Early73
  • 473
  • 3
  • 7
  • 23
  • Found the problem with this there was a rogue i. in the last query should have read: WHERE `lft` <= "'.(0-$iItemPosLeft).'" AND `rgt` >= "'.(0-$iItemPosRight).'"'); – Early73 Jun 12 '11 at 18:52
  • Still want to know if anyone can help me use this to move a node left or right inside the current parent though. Thanks! – Early73 Jun 12 '11 at 18:53
  • To use the same query to move a node left or right just substitute the left value of the node you want the moving node to appear before as the $iParentPosRight value. – Early73 Jun 12 '11 at 19:11

1 Answers1

0

I removed the accidental i. and the query did exactly what I was looking for. To use the same query to move a node left or right just substitute the left value of the node you want the moving node to appear before as the $iParentPosRight value.

Early73
  • 473
  • 3
  • 7
  • 23