Closed Thread Icon

Preserved Topic: PHP/MySQL re-order after insert (Page 1 of 1) Pages that link to <a href="http://ozoneasylum.com/backlink?for=12535" title="Pages that link to Preserved Topic: PHP/MySQL re-order after insert (Page 1 of 1)" rel="nofollow" >Preserved Topic: PHP/MySQL re-order after insert <span class="small">(Page 1 of 1)</span>\

 
Perfect Thunder
Paranoid (IV) Inmate

From: Milwaukee
Insane since: Oct 2001

posted posted 12-01-2002 21:04

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.

Lurch
Paranoid (IV) Inmate

From: Behind the Wheel
Insane since: Jan 2002

posted posted 12-01-2002 21:18

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--

Thumper
Paranoid (IV) Inmate

From: Deeetroit, MI. USA
Insane since: Mar 2002

posted posted 12-02-2002 09:43

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



Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 12-02-2002 15:12

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

Perfect Thunder
Paranoid (IV) Inmate

From: Milwaukee
Insane since: Oct 2001

posted posted 12-02-2002 19:01

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.

Thumper
Paranoid (IV) Inmate

From: Deeetroit, MI. USA
Insane since: Mar 2002

posted posted 12-02-2002 19:49

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).

Perfect Thunder
Paranoid (IV) Inmate

From: Milwaukee
Insane since: Oct 2001

posted posted 12-02-2002 20:25

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.

tgullett
Obsessive-Compulsive (I) Inmate

From: Lexington, Kentucky, USA
Insane since: Jun 2003

posted posted 06-26-2003 20:47
code:
// 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

Perfect Thunder
Paranoid (IV) Inmate

From: Milwaukee
Insane since: Oct 2001

posted posted 06-26-2003 21:13

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

Tyberius Prime
Paranoid (IV) Mad Scientist with Finglongers

From: Germany
Insane since: Sep 2001

posted posted 06-26-2003 21:13

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).

tgullett
Obsessive-Compulsive (I) Inmate

From: Lexington, Kentucky, USA
Insane since: Jun 2003

posted posted 06-26-2003 21:27
code:
// 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

tgullett
Obsessive-Compulsive (I) Inmate

From: Lexington, Kentucky, USA
Insane since: Jun 2003

posted posted 06-26-2003 21:29
code:
// 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

tgullett
Obsessive-Compulsive (I) Inmate

From: Lexington, Kentucky, USA
Insane since: Jun 2003

posted posted 06-26-2003 21:35

PS: Thank You Tyberius Prime for the Welcome!

Thomas Chase Gullett
tgullett@elinkdesign.com

tgullett
Obsessive-Compulsive (I) Inmate

From: Lexington, Kentucky, USA
Insane since: Jun 2003

posted posted 06-26-2003 22:06
code:
// 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).]

« BackwardsOnwards »

Show Forum Drop Down Menu