Preserved Topic: PHP/MySQL re-order after insert (Page 1 of 1)

I'm writing a script that will display items in a specific order. The client has to be able to change the display order, or insert items midway down the list (not just at the bottom.) Right now, I have a column in my table called something like "position_index" -- this contains an arbitrary unique value. When I query the database, I order by this position_index value, then output the rows in that order.

In order to move an item within the list, however, I have to do some shuffling and sorting. It's not a problem to push values around in an array -- that's Programming 101. Trouble is, using the methods I'm familiar with (looping and reassigning values), it would take a ton of update queries to change the position_index value for every relevant row. For instance, if I had a hundred items, and inserted a new item that needed to be in the #2 position, I'd have to perform 99 update queries to move the other items down.

I've done a little Google-fishing, and haven't run into an answer. I don't need to reorder the table rows themselves -- I'm just looking for an efficient way of "pushing" the position_index values around whenever I move or insert an item. It really seems to me that there ought to be a simple SQL way of doing it, since it's can't be that rare of a need. The MySQL manual isn't forthcoming, though.

If something leaps to mind, please give me a clue.

I haven't had a chance to think about it much, but what leapt to mind was this...

in your position_index field, use an auto_increment or something, incrementing at like 1000 or something. then you'll get a table like this:

position_index .....
1000

2000

3000

4000

5000

When you add a new item right away it will go to the end of the list, but you could update the table and change the item's position_index to half-way between the two rows you want it between.. for example, you now want the fourth row between the 2nd and 3rd..

position_index
1000

2000

2500

3000

5000

now your select query can just be ordered by position_index

--Lurch--

PT, I did something like this for some sub-level listings on a script. All I did was create a field in the table called "parent". In a user submitted form I specified whether the item I was adding was a sub-item of something else (thus it's placement when querying). If it was, it inputted the root level item's ID into the "parent" field.

I do not have the actual code on hand, but it was something to the effect of a double while loop where the second while loop found whether or not the record was a subitem based on the value of its "parent" field (as in finding items whose (parent > '0' and parent = the previous 'id')). Being that the "parent" field will never exceed the value of any given "id", it makes for a useful (clean) repositioning script. Hope you can do something with this...

out of the top of my head,
visit http://www.undu.com ,
and check out their first article, it's a delphi page, but talks about just your problem.

I think the proposed solution was to order by a floating point value...

It seems like the "prevailing" solution is the one offered by Lurch and Tyberius: Make the position index in such a way that it's easy to insert values in between them. I'm pretty likely to end up doing that.

Thumper, I'm not sure I grasp your idea entirely. I start with a record like this:

ID: 1
Parent: null
Data: whatever

Then every subsequent record has a "parent" -- so if I insert a new record that's meant to be displayed below Record 1, it would look like this:

ID: 2
Parent: 1
Data: whatever

Then, if I want to insert a new value below Record 1, so that Record 2 is displayed third in the list, I make it like so --

ID: 3
Parent: 1
Data: whatever

-- and I change Record 2 to --

ID: 2
Parent: 3
Data: whatever

-- and then, the parent-child chain that led off of Record 2 is uninterrupted. So I make two changes, and the insert operation is complete.

To put it another way,

1 --> 2 --> 4 --> 5

If I insert 3 such that 2 --> 3 and 3 --> 4, then

1 --> 2 --> 3 --> 4 --> 5

And only two "connections" need to be changed!

Sorry for the long write-up, but I was figuring it out as I typed. Thumper, that's actually a damned good solution! The database records themselves won't be easily decipherable, but the client will be interacting with the application only through a web front-end.

I'll try to implement it, and if I have any trouble, I'll come back here. And when I get it done, I'll post my code here and start an FAQ on it. Not sure it's "frequently" asked around here, but apparently Google has been bringing up Ozone FAQ pages, so it might help somebody.

PT, you are on the right track. I am sorry I don't have the code for it. Basically during your query, you hold off actually printing any records whose parent is greater than '0' (or NULL as you have it) until you have found who its "parent" is. As the query goes, you are looking for the root's "child" (if any) before moving on to the next loop (next root).

Sure; after reading the query result into an array, it shouldn't be a problem to use PHP's array functions to do what I need to.

// THIS IS THE ACTION WHEN INSERTING



if ($arrange != '--end--') {



	if ($arrange != '--first--') {



	$arrange_query = mysql_query("SELECT news_id, order_id FROM news WHERE top_story='Y' AND order_id > $arrange");



	$arrange_count = mysql_num_rows($arrange_query);

	

	if ($arrange_count == 0) {



	}else{



		while(list($news_id, $order_id) = mysql_fetch_row($arrange_query)) {



		$order_id++;



		$arrange_query_update = mysql_query("UPDATE news SET order_id=$order_id WHERE news_id='$news_id'");



		}



		$order_id = $arrange + 1;



	}



	}else{



		$arrange_query = mysql_query("SELECT news_id, order_id FROM news WHERE top_story='Y' ORDER BY order_id");



		$arrange_count = mysql_num_rows($arrange_query);



		if ($arrange_count == 0) {



		}else{



			while(list($news_id, $order_id) = mysql_fetch_row($arrange_query)) {



			$order_id++;



			$arrange_query_update = mysql_query("UPDATE news SET order_id=$order_id WHERE news_id='$news_id'");



			}



			$order_id = 1;



		}

	}



}else{



	$arrange_query = mysql_query("SELECT DISTINCT order_id FROM news WHERE top_story='Y' ORDER BY order_id DESC");



	list($order_id) = mysql_fetch_row($arrange_query);



	$order_id++;

}





$result = mysql_query("INSERT INTO `news` (`news_id`, `order_id`, `top_story`, `date_created`, `date`, `spot_title`, `title`, `article`, `img_one`, `img_two`) VALUES ('', '$order_id', '$top_story', NOW(), '$year$month$day', '$spot_title', '$title', '$article', '$pic_one_id', '$pic_two_id')");





// THE PART FOR THE FORM



<?

		$arrange_now = mysql_query("SELECT news_id, order_id, title FROM news WHERE top_story='Y' ORDER BY order_id");



		$arrange_count = mysql_num_rows($arrange_now);



		if ($arrange_count == 0) {



		}else{



			while(list($news_id, $order_id, $title) = mysql_fetch_row($arrange_now)) {



			?><option value="<?=$order_id?>" <? if ($arrange == $order_id) { echo "selected"; } ?>>After <?=$title?></option><?



			}



			$order_id = 1;



		}



?>

I am working on the update part of it...

Any questions email me at tgullett@elinkdesign.com

-TCG

Thomas Chase Gullett
tgullett@elinkdesign.com

I've long since got a PHP/MySQL linked list that works just fine for my situation, but I'm curious to see where you're going with what you've got. Right now, you're just giving each record an index equal to the previous index plus one. This is fine when initially building your list, but how do you plan to insert an item halfway through?

Cell 1250 :: alanmacdougall.com :: Illustrator tips

welcome to the asylum, tgullett.

Without reading your code to closely,
I see at least one major drawback against the 'floating point' (or one could probably also use arbitary precision numbers, if there was a need!)
proposal that's about half a year(!) old now,
your code does a *lot* of update queries.
That will put a heavy strain on your database server, and probably will also run into timeout once you got a largish amount of rows.

Don't see the problem with adding it at a certain location with the floating points either.
Just define start = 0.0, last = count(),
and for everything else, you built an average via (before + after) / 2 to insert it.
Only special case is at the begining, where you need one (1) additional update to alter the previous first entry's position (which is being bulid by doing (pos(element 3) /2).

// THIS IS DELETING

		$arrange_query = mysql_query("SELECT news_id, order_id FROM news WHERE top_story='Y' AND order_id > $arrange");



		$arrange_count = mysql_num_rows($arrange_query);

	

		if ($arrange_count == 0) {



		}else{



			while(list($news_id, $order_id) = mysql_fetch_row($arrange_query)) {



			$order_id--;



			$arrange_query_update = mysql_query("UPDATE news SET order_id=$order_id WHERE news_id='$news_id'");



			}

	

		}



		$qDELETE = mysql_query("DELETE FROM news WHERE news_id='$id'") or die("ERROR: could not query backend database.");

*NOTE: You must carry over the arrange id through the POST vars


I am aware of the number of update queries. I am trying to solve this problem with the ?Goodly SQL statement? that would solve this strain on the server, and balance it more utilizing the database to do the work.

Let's make it better!

Thomas Chase Gullett
tgullett@elinkdesign.com

// THIS IS THE UPDATE ACTION



if ($arrange != '--none--') {



if ($arrange != '--end--') {



	if ($arrange != '--first--') {



	$arrange_query = mysql_query("SELECT news_id, order_id FROM news WHERE top_story='Y' AND order_id > $arrange");



	$arrange_count = mysql_num_rows($arrange_query);

	

	if ($arrange_count == 0) {



	}else{



		while(list($news_id, $order_id) = mysql_fetch_row($arrange_query)) {



		$order_id++;



		$arrange_query_update = mysql_query("UPDATE news SET order_id=$order_id WHERE news_id='$news_id'");



		}



		$order_id = $arrange + 1;



	}



	}else{



		$arrange_query = mysql_query("SELECT news_id, order_id FROM news WHERE top_story='Y' ORDER BY order_id");



		$arrange_count = mysql_num_rows($arrange_query);



		if ($arrange_count == 0) {



		}else{



			while(list($news_id, $order_id) = mysql_fetch_row($arrange_query)) {



			$order_id++;



			$arrange_query_update = mysql_query("UPDATE news SET order_id=$order_id WHERE news_id='$news_id'");



			}



			$order_id = 1;



		}

	}



}else{



	$arrange_query = mysql_query("SELECT DISTINCT order_id FROM news WHERE top_story='Y' ORDER BY order_id DESC");



	list($order_id) = mysql_fetch_row($arrange_query);



	$order_id++;



}





$qUPDATE = mysql_query("UPDATE news SET order_id='$order_id', top_story='$top_story', date='$year$month$day', spot_title='$spot_title', title='$title', article='$article', img_one='$pic_one_id', img_two='$pic_two_id' WHERE news_id='$id'") or die("ERROR: Could not query backend database."); 



}else{



$qUPDATE = mysql_query("UPDATE news SET top_story='$top_story', date='$year$month$day', spot_title='$spot_title', title='$title', article='$article', img_one='$pic_one_id', img_two='$pic_two_id' WHERE news_id='$id'") or die("ERROR: Could not query backend database."); 



}



// THIS IS THE EDIT FORM



		<select name="arrange" style="font-family:verdana; font-size:10px; border-top-width:1px; border-left-width:1px;">

		 <option value="--none--" selected>Preserve Order</option>

   		 <option value="--end--" <? if ($arrange == "--end--") { echo "selected"; } ?>>At end of list</option>

  		 <option value="--first--" <? if ($arrange == "--first--") { echo "selected"; } ?>>At beginning of list</option>

<?

		$arrange_now = mysql_query("SELECT news_id, order_id, title FROM news WHERE top_story='Y' ORDER BY order_id");



		$arrange_count = mysql_num_rows($arrange_now);



		if ($arrange_count == 0) {



		}else{



			while(list($news_id, $order_id, $title) = mysql_fetch_row($arrange_now)) {



			?><option value="<?=$order_id?>" <? if ($arrange == $order_id) { echo "selected"; } ?>>After <?=$title?></option><?



			}



			$order_id = 1;



		}



?>

 		</select>

Thomas Chase Gullett
tgullett@elinkdesign.com

PS: Thank You Tyberius Prime for the Welcome!

Thomas Chase Gullett
tgullett@elinkdesign.com

// THIS IS THE ACTION WHEN INSERTING * Modified



if ($arrange != '--end--') {



	if ($arrange != '--first--') {



		$aarange_query_update = mysql_query("UPDATE news SET order_id=order_id+1 WHERE top_story='Y' AND order_id > $arrange");

		$order_id = $arrange +1;



	}else{



		$aarange_query_update = mysql_query("UPDATE news SET order_id=order_id+1 WHERE top_story='Y'");



		$order_id = 1;

	}



}else{



	$arrange_query = mysql_query("SELECT DISTINCT order_id FROM news WHERE top_story='Y' ORDER BY order_id DESC");



	list($order_id) = mysql_fetch_row($arrange_query);



	$order_id++;

}





$result = mysql_query("INSERT INTO `news` (`news_id`, `order_id`, `top_story`, `date_created`, `date`, `spot_title`, `title`, `article`, `img_one`, `img_two`) VALUES ('', '$order_id', '$top_story', NOW(), '$year$month$day', '$spot_title', '$title', '$article', '$pic_one_id', '$pic_two_id')");

I think this will solve the problem...

-TCG

Thomas Chase Gullett
tgullett@elinkdesign.com

[This message has been edited by tgullett (edited 06-26-2003).]