Moving up the sort order

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bdeonline
Forum Commoner
Posts: 42
Joined: Sun Jul 18, 2004 10:45 am

Moving up the sort order

Post 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.
peni
Forum Commoner
Posts: 34
Joined: Thu Nov 18, 2004 1:15 pm

Post 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.
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post 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'] . "'");
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Re: Moving up the sort order

Post 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.
Post Reply