Reordering System

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
Bruno De Barros
Forum Commoner
Posts: 82
Joined: Mon May 12, 2008 8:41 am
Location: Ireland

Reordering System

Post by Bruno De Barros »

For quite a while now I've been dwelling with this problem:
How to provide the ability to get a list of items and reorder them at your will?
Let me explain. Imagine you have a database table with categories. You want to provide them in different orders on the front-end. For example, you want to allow the administrators to reorder the order that the categories are shown in the navigation bar, but you also want to allow them to reorder how they will be shown in a dropdown jumplist. Not the best example, I know, but bear with me. :)

Right now, my idea for this example is:
The Categories Table (ID, NAME, DESCRIPTION)
An Order Items Table (ORDER_IDENTIFIER, ARG, ORDER)

And I'd have for example:
(ID, NAME, DESCRIPTION)
1 | Category 1 | Just a test category.
2 | Category 2 | With the ordering, this should be in first place
3 | Category 3 | This goes to the middle.

(ORDER_IDENTIFIER, ARG, ORDER)
tabs | 1 | 3
tabs | 2 | 1
tabs | 3 | 2
dropdown | 1 | 2
dropdown | 2 | 3
dropdown | 3 | 1

I hope the example is clear enough. Basically, I have 3 categories, but I have two ways to order them. I can order them for display as tabs in the navigation bar, or for the dropdown list in the website.

Now I use a JOIN in which when the ARG of the order is equal to the ID of the category and the ORDER_IDENTIFIER is equal to what I'm trying to get (for example, let's say I'm trying to get the tabs), and then when they're joined, I know the ORDER of that category.

What do you think of this idea is what I'm wondering. I know that I could just make a TAB_ORDER and a DROPDOWN_ORDER field in the Categories table, but I want to create an ordering system that is completely abstracted from whatever I'm trying to order. For example, in that case, all it would take would be creating three extra order items:
top3 | 1 | 1
top3 | 2 | 3
top3 | 3 | 2

And now I just get the categories' orders for the top3, and I have their new order. I don't know if I'm making myself clear.

So far, the big disadvantages of this method that I could find are:
-> Without a record in the order items table, I can't obtain the categories. If I add a Category 4, for example, without creating any order items for it, it won't show up when I get Category 4. I really have no idea how to sort this one out.
-> It's a JOIN statement, obviously. I was thinking that maybe a ORDERS field could be made which kept the orders for a certain category in an Array. (eg. serialize a PHP array and store it in an ORDERS field). But that would not only make the DB compatible only with PHP, but it would also mean serializing/unserializing the orders every time I want to get them. Or I could instead make my own format for the orders, but it would still not be good, because I'd be making it exclusive now to my application. So the orders table seemed to me like the best way to go about doing this.
What do you think?
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Reordering System

Post by allspiritseve »

I think you're on the right track. I would be tempted to make another table that held locations (tabs, dropdown) and use the table with the sorting field as a relationship table. That way, adding a category won't make the category show up anywhere, but if you give it a location (by adding a row in the relationship table) then it will. You could set a default of 0 for the sort, so everything would be initially ordered by insert time.

On the front end, I'd recommend jQuery sortable for rearranging items in a list:

http://docs.jquery.com/UI/Sortable

You should be able to tie that to the database with a quick ajax call.
Bruno De Barros
Forum Commoner
Posts: 82
Joined: Mon May 12, 2008 8:41 am
Location: Ireland

Re: Reordering System

Post by Bruno De Barros »

@allspiritsteve that sounds interesting. Wouldn't that mean that I'd have to create a record in the relationships table for each of the locations? That would be the same thing as what I had, with the exception that now the locations would be separated. Maybe with an extra field on the table that held all the locations, which would indicate what table they belonged to.

For example, I could have
tags
dropdown
top3
ALL of them with a field that would indicate that those locations are only for categories. That way, when creating a category, I'd just have to create an order item for each of the locations for categories, to make sure they'd automatically be displayed.

EDIT: Currently I was looking at a DragNDrop plugin, let me see if I can find it... Yep.
http://www.isocra.com/2008/02/table-dra ... ry-plugin/

It seems quite good, and it's easy to make it work with PHP to store the results.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Reordering System

Post by allspiritseve »

Bruno De Barros wrote:Wouldn't that mean that I'd have to create a record in the relationships table for each of the locations?
No, it'd mean that you'd have to create a record in the relationship table for each category+location. It is very similar to what you had, but more normalized... and because the location is in it's own separate table, you can attach other content to it as well.
Bruno De Barros wrote:EDIT: Currently I was looking at a DragNDrop plugin, let me see if I can find it... Yep.
http://www.isocra.com/2008/02/table-dra ... ry-plugin/

It seems quite good, and it's easy to make it work with PHP to store the results.
Well, sure, if you use tables for layout then that works great. I prefer lists... either way, JQuery Sortable uses Draggable and Droppable just like that plugin does.
Bruno De Barros
Forum Commoner
Posts: 82
Joined: Mon May 12, 2008 8:41 am
Location: Ireland

Re: Reordering System

Post by Bruno De Barros »

allspiritseve wrote:No, it'd mean that you'd have to create a record in the relationship table for each category+location. It is very similar to what you had, but more normalized... and because the location is in it's own separate table, you can attach other content to it as well.
Yes, that's what I meant, sorry :P I meant to say that for each category I'd have to create a record that'd link the category+location, so if I had 5 different locations for one table, that means that I'd have to insert 5 new order items for each category I created.
allspiritseve wrote: Well, sure, if you use tables for layout then that works great. I prefer lists... either way, JQuery Sortable uses Draggable and Droppable just like that plugin does.
Mhm, I see what you mean, lists would be good as well. Actually, I'd say lists might be an even better way to do it, if you just want the ordering. But if you want to add some extra features (I'm thinking datagrid functionality), tables could be used as well. I'm probably gonna develop both of the solutions, just so that the system already provides a way to reorder everything without, sort of like example usage, or something. :)
credit123
Forum Newbie
Posts: 1
Joined: Tue Apr 07, 2009 8:56 pm

Re: Reordering System

Post by credit123 »

Oh! very good posting. Thank you!
maison de credit
Post Reply