[thelist] MySQL/PHP design question : order in a DB table.

Wed Jul 23 10:33:02 CDT 2003

Steps:
1. Build an array of the rows in the order they are now, value of array 
  elements must be unique (primary key):
select id from table order by `order`
while(list($tmp)=fetch_row()) $ordered_array[]=$tmp;

2. Change the order, either within the browser by using some 
form/javascript thingie (see below), or programaticly with array functions.

3. Loop the reordered array and update the order column for each row:
foreach($reordered_array as $order => $primary_key) {
	update table set order=$order where id=$primary_key;
}

Form/javascript thingie example:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<html>
    <head>
       <title>Reorder</title>
       <script language="JavaScript1.1">

           function up(){
               var upText,upValue,downText,downValue;
			  var selected=document.orderForm.list;
               if(selected){
                 if(selected.selectedIndex > 0 ){
                   upText=selected.options[selected.selectedIndex].text;
                   upValue=selected.options[selected.selectedIndex].value;
                   downText=selected.options[selected.selectedIndex - 
1].text;
                   downValue=selected.options[selected.selectedIndex - 
1].value;
                   selected.options[selected.selectedIndex].text=downText;
                   selected.options[selected.selectedIndex - 1].text=upText;
                   selected.options[selected.selectedIndex].value=downValue;
                   selected.options[selected.selectedIndex - 
1].value=upValue;
                   selected.options[selected.selectedIndex - 
1].selected=true;
                 }
               }
               return false;
           }

           function down(){
               var upText,upValue,downText,downValue;
   			  var selected=document.orderForm.list;
               if(selected){
                 if(selected.selectedIndex != selected.length - 1 && 
selected.selectedIndex !=  -1){
                   upText=selected.options[selected.selectedIndex + 1].text;
                   upValue=selected.options[selected.selectedIndex + 
1].value;
                   downText=selected.options[selected.selectedIndex].text;
                   downValue=selected.options[selected.selectedIndex].value;
                   selected.options[selected.selectedIndex + 
1].text=downText;
                   selected.options[selected.selectedIndex].text=upText;
                   selected.options[selected.selectedIndex + 
1].value=downValue;
                   selected.options[selected.selectedIndex].value=upValue;
                   selected.options[selected.selectedIndex + 
1].selected=true;
                 }
               }
               return false;
           }

		  function submitForm(form) {
		    var i;
		  	var loc = form.action;
			for (i = 0; i < form.list.length; i++) {
		    	loc += '&reordered_array[]=' + form.list.options[i].value;
			}
			document.location.href=loc;
		  	return false;
		  }
       </script>
    </head>
    <body  >
        <form name="orderForm" action="script.php" onsubmit="return 
submitForm(this)">
		   <table cellpadding="5" witdh="300">
                <col witdh="50%"><col witdh="50%">
			   <tr>
                    <td align="center">
    <?php
    $res=mysql_query('SELECT * FROM table ORDER BY `order`');
    ?>
      <select name="list" size="10" style="width: 300px;">
					   <?php
					   	while($a=mysql_fetch_assoc($res)) {
					   ?>
                            <option value="<?= $a['id'] ?>"><?= 
$a['text_to_show'] ?></option>
<?php } ?>
                        </select>
                    </td>
                    <td align="center">
                        <a href="" onClick="return up()"><img 
src="images/uparrow.png" width="16" height="16" border="0"></a><br>
                        <a href="" onClick="return down()"><img 
src="images/downarrow.png" width="16" height="16" border="0"></a><br><br>
                    </td>
                </tr>
			   <tr>
				<td colspan="2"><input type="button" value="Cancel" 
onClick="window.close();">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input 
type="submit" value="Save order"></td>
			   </tr>
            </table>
        </form>

    </body>
</html>

Ned Baldessin wrote:

> Hi,
> 
> I'm working on a site that has all it's page structure in a DB table :
> it's a hierarchy of parents and children that form a tree structure.
> 
> I'm trying to find the best design to be able to easily change the order
> in which the siblings are outputted. I need to be able to set an
> arbitrary order, for example, change this
> 
>     1 Aaa
>     2 Bbb
>     3 Ccc
> 
> into this :
> 
>     1 Bbb
>     2 Aaa
>     3 Ccc
> 
> The best I could think off is to create a column called 'ordered', and
> put a number in it for each row : 1, 2, 3, etc.
> The problem is each time I want to change the order (increment or
> decrement the number), I have to check all the other siblings to see if
> there is any overlap. 
> 
> Does anyone have a more elegant and simple design ?
> Thanks. 