dragon_sa Posted November 28, 2010 Share Posted November 28, 2010 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. Quote Link to comment Share on other sites More sharing options...
dawsba Posted December 1, 2010 Share Posted December 1, 2010 /* 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 Quote Link to comment Share on other sites More sharing options...
dragon_sa Posted December 1, 2010 Author Share Posted December 1, 2010 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.