[thelist] MySQL/PHP design question : order in a DB table.
Marek Kilimajer
kilimajer at webglobe.sk
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();"> <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.
More information about the thelist
mailing list