Php > Database Related

MySQL Manual Ordering Tool

MySQL Manual Ordering Tool

<? 


#Edit these Variables when first setting this up 
## 
$up_image = "images/up.gif"; 
$down_image = "images/down.gif"; 



#This next section should be placed in the first column of the table   
#that holds the data.  Make sure that you put any additional needed hidden 
#fields that hold the data that you need so you see the correct listings 
#(in a new order). 
 
#print "<td width=23 nowrap><form name=updown action=NAME_OF_SCRIPT method=POST>"; 
#print "<input type=hidden name=crn value='RECORD_CRN'>"; 
#print "<input type=hidden name=action value='move'>"; 
#print "<input type=hidden name=order value='RECORD_PORDER'>"; 
#if($x) 
#    print "<input type=image src=$up_image name=up value=up border=0>"; 
#if(($x+1) != NUMBER_OF_RECORDS) 
#    print "<input type=image src=$down_image name=down value=down border=0>"; 
#print "</td></form>"; 
#$x++; 

#At the start of your script have a conditional clause that checks to see if 
#$action == "move" (on line 25 above).  If so then call the function with   
#the indicated arguements: 

#if($up_x || $up_y) 
#    move(TABLE_NAME, RECORD_P_ORDER, RECORD_CRN, 1); 
#else 
#    move(TABLE_NAME, RECORD_P_ORDER, RECORD_CRN, 0); 


 
#Here is the line you need to add to the tables that will be ordered 
#If you add this to an existing database you need to populate the p_order 
#field for every record before this will work. 

#p_order             INT(10) NOT NULL 




#Make sure you order your main statement by p_order! 

#$stmt = "SELECT * FROM table ORDER BY p_order"; 


 
#Make sure when you are adding a new record to the table that you insert it 
#with the order.  You use the function getNumElements() to get the largest 
#number then you add one. 

#$number = getNumElements("pricing") + 1; 



function getNumElements($table) 
    { 
    $stmt = "SELECT MAX(p_order) as num FROM $table"; 
    $result = mysql_query($stmt); 
    $obj = mysql_fetch_object($result); 
    return $obj->num; 
    } 


function move($table, $order, $crn, $up) 
    { 
      
    function checkOrder($table, $order, $updown) 
        { 
        if($updown == "up") 
            $stmt = "select * from $table where p_order < '$order' ORDER BY p_order DESC LIMIT 1"; 
        else 
            $stmt = "select * from $table where p_order > '$order'  ORDER BY p_order LIMIT 1"; 
        $result = mysql_query($stmt); 
        mysql_error(); 
        $obj = mysql_fetch_object($result); 
        $number = mysql_num_rows($result); 
        if($number) 
            return $obj->crn."|".$obj->p_order; 
        else 
            return; 
        }      
              
    if($up) 
        { 
        list($move_crn, $new_order) = split("\|", checkOrder($table, $order, "up")); 
        $move_stmt = "UPDATE $table set p_order='$new_order' WHERE crn='$crn'"; 
        $move2_stmt =  "UPDATE $table set p_order='$order' WHERE crn='$move_crn'"; 
        mysql_query($move_stmt); 
        mysql_error(); 
        mysql_query($move2_stmt); 
        mysql_error(); 
        } 
    else 
        { 
        list($move_crn, $new_order) = split("\|", checkOrder($table, $order, "down")); 
        $move_stmt = "UPDATE $table set p_order='$new_order' WHERE crn='$crn'"; 
        $move2_stmt =  "UPDATE $table set p_order='$order' WHERE crn='$move_crn'"; 
        mysql_query($move_stmt); 
        mysql_error(); 
        mysql_query($move2_stmt); 
        mysql_error(); 
        } 
    } 

?>