Move Up and Down and dealing with deleted entries

[dragon_sa]

I have a mysql table like so

 

ID  OrderID  Name  Image      CatalogID

1  1            test    pic1.jpg    1

2  2            test2  pic2.jpg    1

3  1            test3  pic3.jpg    2

4  1            test4  pic4.jpg    3

5  1            test5  pic5.jpg    4

6  2            test6  pic6.jpg    4

7  3            test7  pic7.jpg    4

 

etc etc

 

(1) I want to use up and down buttons so that the OrderID can be modified to reflect the users order preference, how would I code it so it changes all the OrderID values in the table as  necessary to produce the correct order using the buttons, the only affected items each time would be the ones in the same CatalogID group.

 

(2) How do I re number the OrderID when an item is deleted so that it moves all the items below it in the same CatalogID group up one value to still reflect the correct order and not skip OrderID values.

[dawsba]

/*
    DATABASE CONNECTION INFO
  */
  define("TABLE", "tbl_order");
  
  function q($query,$assoc=1) {
   $r = @mysql_query($query);
   if( mysql_errno() ) {
   return FALSE;
   } 
   if( strtolower(substr($query,0,6)) != 'select' ) return array(mysql_affected_rows(),mysql_insert_id());
   $count = @mysql_num_rows($r);
   if( !$count ) return 0;
   if( $count == 1 ) {
       if( $assoc ) $f = mysql_fetch_assoc($r);
       else $f = mysql_fetch_row($r);
       mysql_free_result($r);
       if( count($f) == 1 ) {
           list($key) = array_keys($f);    
           return $f[$key];
       } else {
           $all = array();
           $all[] = $f;
           return $all;
       }
   } else {
       $all = array();
       for( $i = 0; $i < $count; $i++ ) {
           if( $assoc ) $f = mysql_fetch_assoc($r);
           else $f = mysql_fetch_row($r);
           $all[] = $f;
       }
       @mysql_free_result($r);
       return $all;
    }
  }
  function changeStatus($id,$type)
  {
    $sqlw = "WHERE `id` = '".$id."'";
    $sqlx = "FROM `".TABLE."` ".$sqlw;
    $sqlu = 'UPDATE `".TABLE."` SET `OrderID`=`OrderID`';
    if(q("SELECT COUNT(*) ".$sqlx)==1)
    {
      $Oid = q("SELECT `OrderID` ".$sqlx." LIMIT 1");
      $catid = q("SELECT `CatalogID` ".$sqlx." LIMIT 1");
      switch($type)
      {
        case 1:
          if($Oid>1)
          {
            q("-1 ".$sqlw." LIMIT 1");
            q($sqlu."+1 WHERE `CatalogID` = '".$catid."' AND `id` != '".$id."' AND `OrderID` >= '".$id."'");
          }
        break;
        case 2:
          q($sqlu."+1 ".$sqlw." LIMIT 1");
          q($sqlu."-1 WHERE `CatalogID` = '".$catid."' AND `id` != '".$id."' AND `OrderID` >= '".$id."' AND `OrderID` != 1");
        break;
        case 3:
          q("DELETE ".$sqlx." LIMIT 1");
          q($sqlu."-1 WHERE `CatalogID` = '".$catid."' AND `id` != '".$id."' AND `OrderID` >= '".$id."' AND `OrderID` != 1");
        break;
      }
    }
  }
/*
status types
1 = up
2 = down
4 delete

function changeStatus syntax
changeStatus($id,$type) //$id is the row id, $type is status type
*/

changeStatus(2,1);

 

I havnt had chance to test but should do the job for you have fun

[dragon_sa]

Thank you very much for your reply I ended out working out some logic here is what I dis

for move up and down

 

// action move up
if ($move=='up') { 
	// set new order value
	$newOrder=$catalogOrder-1;
	// change catalog already in new postion down
	$sqldown="UPDATE catalog SET catOrder='$catalogOrder' WHERE catOrder='$newOrder' AND collectionID='$collectID'";
	$resultdown=mysql_query($sqldown);
	// set current catalog up 1
	$sqlup="UPDATE catalog SET catOrder='$newOrder' WHERE catalogID='$catalogID' AND collectionID='$collectID'";
	$resultup=mysql_query($sqlup);
	}
// action move down
if ($move=='down') {
	// set new order value
	$newOrder=$catalogOrder+1;
	// change catalog already in new postion up
	$sqlup="UPDATE catalog SET catOrder='$catalogOrder' WHERE catOrder='$newOrder' AND collectionID='$collectID'";
	$resultup=mysql_query($sqlup);
	// set current catalog down 1
	$sqldown="UPDATE catalog SET catOrder='$newOrder' WHERE catalogID='$catalogID' AND collectionID='$collectID'";
	$resultdown=mysql_query($sqldown);
	}

 

and for delete

 

// update order of catalogs
$ordsql="UPDATE catalog SET catOrder=(catOrder-1) WHERE catOrder > '$catNum' AND collectionID='$catCollID'";
$resultorder = mysql_query($ordsql);

and this appears to work a treat