Page 1 of 1
Moving up the sort order
Posted: Fri Nov 19, 2004 10:22 am
by bdeonline
Code: Select all
<?php
$result2 = mysql_query("SELECT pSortOrder FROM skin_pages WHERE pId='{$_POST['pId']}'");
$row2 = mysql_fetch_object($result2);
$sOrder = $row2->pSortOrder;
mysql_query("DELETE FROM skin_pages WHERE pId='{$_POST['pId']}'");
$i = $sOrder;
$result = mysql_query("SELECT pId FROM skin_pages ORDER BY pSortOrder");
while($row = mysql_fetch_array($result)) {
mysql_query("UPDATE skin_pages SET pSortOrder='$i' WHERE pId='$row[$i]'");
$i++;
}
?>
I am trying to make a delete button that when pressed will move all the sortorder numbers below it up one. So for instance I delete the id with sort number 3 the ones below it 4 5 6 will move up one. 4 will now have sort order of 3, 5/4, and 6/5 and so on.
Posted: Fri Nov 19, 2004 10:54 am
by peni
your need is terrible. reorganize the logic and forget this continuous numeration or leave it and see your server painfully dying with a table of 400.000 lines and some visitors trying to use this application at the same time.
Posted: Fri Nov 19, 2004 10:55 am
by Wayne
you shouldn't need to loop through the records, you could run 1 sql statement to update all records with a pId > than the deleted page ... try ...
Code: Select all
mysql_query("UPDATE skin_pages SET pSortOrder= pSortOrder-1 WHERE pId>'" . $_POST['pId'] . "'");
Posted: Fri Nov 19, 2004 11:12 am
by timvw
first: if you delete a row, you don't have to change the sortorder... because it will remain the same
second: you need to lookup what transactions are, or how to simulate them using mysql, otherwise you'll see that the ordering goes foobar when ppl delete rows at the same time...
Re: Moving up the sort order
Posted: Fri Nov 19, 2004 11:51 am
by BDKR
bdeonline wrote:Code: Select all
<?php
$result2 = mysql_query("SELECT pSortOrder FROM skin_pages WHERE pId='{$_POST['pId']}'");
$row2 = mysql_fetch_object($result2);
$sOrder = $row2->pSortOrder;
mysql_query("DELETE FROM skin_pages WHERE pId='{$_POST['pId']}'");
$i = $sOrder;
$result = mysql_query("SELECT pId FROM skin_pages ORDER BY pSortOrder");
while($row = mysql_fetch_array($result)) {
mysql_query("UPDATE skin_pages SET pSortOrder='$i' WHERE pId='$row[$i]'");
$i++;
}
?>
I am trying to make a delete button that when pressed will move all the sortorder numbers below it up one. So for instance I delete the id with sort number 3 the ones below it 4 5 6 will move up one. 4 will now have sort order of 3, 5/4, and 6/5 and so on.
How about ...
Code: Select all
$SQL='UPDATE skin_pages SET pSortOrder=(pSortOrder-1) WHERE pld > '.$_POST['pld'];
I suspect that would do it. No looping involved.