List sorting challenge

dercof

I'm a completely self taught, novice PHP coder having trouble determing how to approach coding a portion of my project. Any feedback or code snipets would be extremely helpful. I apologize if this is the wrong forum for this type of help.

The idea is to allow users to create their own list of topics, from an inventory of topics using an "add to list" button.

The user would then be able to sort their topics via a drop down list prepopulated with values 1 through the "total number of items on their list". Once the new value is selected for a topic on the list, the page would refresh. The topic would appear in its new position, and the remaining list items' sort order would be adjusted accordingly. All new data values would be committed to the DB as the page refreshed.

To illustrate this idea, I have uploaded a page comp to my website at the following URL. http://www.derekcoffer.com/list_help.jpg

Thank you,
Derek Coffer

drawmack

three tables users, items, users2items

In the users2items table store

itm_id -> points to the item
usr_id -> points to the user
place -> int (placement in the list)

then when you select the items order by place.

dercof

Thanks for the feedback, but unfortunately it's a bit more complicated. It's also hard to explain. So I apologize, and promise not to post any more explanations in the forum after this one.

I need the user to "redefine" the sort by using a drop down box.

If an item is in position 4, and they want it in position 2 they would click on the drop down and select '2'. The item in position 4 would then take on the new sort value of 2, its position in the list would change to position 2, and the item previously in position 2 would move to 3, and 3 would move to 4.

The reverse would hold true. If they wanted to move item in position 1 to position 3, the item in position 2 would move up to position 1, the item in position 3 would move to position 2, and the item in position 1 would move down to position 3.

But all of this would happen after selecting the new sort value from the drop down for ONE of the list items.

I understand that I should use order by to display the sort, but how do I update the changes to the sort value for each item in the list, just by having the user change the value for one?

The alternative to this would function like Netflix's rental list queue. But in their implementation, the user enters in a sort value for each item in the list.

drawmack

Oh okay, sorry I misunderstood you. There are a couple of steps to this. First use the structure I presented above, or something similar. However, do not index the database such that duplicate order numbers for the same user are not possible. Then just do something like this:

Scenario:
Person a has four items on their list item 1, item 2 item 3 and item 4, which are in order. They wish to make it like this item1, item 4, item 3, item 2. All they do is change the number next to item 4 from 4 to 2. The code on the backend would do something like this.

(In pseudo code)

1) Build a list of all the changes
2) Sort the changes*
3) if a change is less (i.e. 5 -> 3)
    3.a) for(order = (old_order-1);order >= new_order;order--)
        3.a.i) update order++
    3.b) end for
    3.c) update old_order -> new_order (i.e. 5 = 3)
4) if a change is greater (i.e. 3 -> 5)
    4.a) for(order = (old_order+1);order <= new_order;order++)
        4.a.i) update order--
    4.b) end for
    4.c) update old_order -> new_order (i.e. 3 = 5)

In step 2 it says to sort the changes. This is where a business rule desicion needs to be made and well documented. Sorting them at this point will affect they way they are applied to the system and therefor will affect the quing process. Exactly how this affects the process I leave to the reader as an exercise.

NOTE: This is a very basic system and can be greatly approved upon.

dotty

Hi,

This is the code I've been trying to get to work on the same problem. I am using input boxes instead of lists.

$select="SELECT page_id, pagetitle, priority FROM pages WHERE parentid = ' . $_GET[parentid] . ' and status = 'published' ORDER BY priority ASC";
$get=mysql_query($select) or die(mysql_error());
echo "<html>\r<body>\r<head>\r<title>set priority</title></head>\r<form name='priority' action='pagesort.php' method='POST'>\r";
while($result=mysql_fetch_array($get)){
echo $result['pagetitle']."<br>";//title of the article
echo " <input type='text' name='priority[]' value=".$result['priority'].">\r";//priority with current value
echo "<input type='hidden'name='page_id[]' value=".$result['page_id']."><br>\r";//hidden current value of article id
}
echo "<input type='submit' name='submit' value='update'>\r</form>\r</body>\r</html>";

//When submitted:

for($i=0;$i<sizeof($page_id);$i++){
$update="UPDATE pages SET priority=".$priority[$i]." WHERE page_id=".$page_id[$i]."";
mysql_query($update) or die(mysql_error());
echo "<br>".$update;
}

... but it doesn't update - and I can't see why not.

//I added this bit to see the values echo - and they do
$priority=$_GET['priority'];
$page_id=$_GET['page_id'];

so if the values are being created - why are they not updating themselves in the database?