Custom order for Records...

moggy

Hi,

I have a simple Content Management System where the site owner can enter information about projects they have been involved in.

The CMS writes these to separate rows in a MySQL5 table.

The user wants to be able to choose the order that the projects are displayed in, so that his favourite projects can be at the top of the list.

Does anyone know how to do this? Ideally I'd like to let him move items up and down the list or to the top or bottom of the list.

Thanks!

bpat1434

Hmm... sounds easy enough. Kind of like "layering" with the z-index of objects in HTML....

Take an enormous number (100 for now). Now, each item will have a "rank" as to where it is. You start with 0, and continue counting items until you get to 100 (the more items, the higher the number).

Now you have to decide what order you'll use. Lower number = higher ranking OR higher number = higher ranking (most sensible). So his first item he wants is something. He moves it to the top of the list, you give it the rank of "100". Then when you do sorting, you do :

ORDER BY rank DESC

It's a basic idea. PHPNuke and other CMSes follow the same idea. It's just giving ranks to the page. Obviously, you'd need a number large enough to give you wiggle room for growth. So if you have 80 entries already, I'd start at 1000 so you can add a whole other 900 items before you really need to expand again.

moggy

Thanks, that all makes sense. But it leads to my next question - how do I let the user input this? Do I just give them four pull-down menus with 0-9 in each for the to input a number, and tell them the highest will be top of the list? Or is there a smart way to do this, i.e. with just a "move up" or "move down" button? I assume if there was it would have to change the "rank" value of all the other rows to compensate for this, and I guess this could get to be quite a large / slow query.

Thanks!

bogu

moggy wrote:
Do I just give them four pull-down menus with 0-9 in each for the to input a number, and tell them the highest will be top of the list? Or is there a smart way to do this, i.e. with just a "move up" or "move down" button?

Both ways would be something similar, more or less ...

moggy wrote:
I assume if there was it would have to change the "rank" value of all the other rows to compensate for this, and I guess this could get to be quite a large / slow query

u just need to add a new field in db table ("ranks") and this wouldn't influence your query, u will use another field for ordering ...

moggy

Thanks, but I still don't quite get it.

If I have five records as such:

Project A
Rank: 1

Project B
Rank: 2

Project C
Rank: 3

Project D
Rank: 4

Project E
Rank: 5

...and I want to move Project D to the top of the list, surely I need to change it's ranking value on the db to 1 and adjust the other rankings to fit round it? Otherwise there would be two rows with ranking 1?

Or if I'm using a separate table to hold the rankings, can I delete the row and insert it in a different place, using auto-increment to number the rankings? And will this make the other rankings compensate?

Sorry if I'm being thick ;-)

bogu

First of all leave the auto-increment alone, create another field for ranking...

If u have to move Project D at the begining, make it rank 1 and the rest that have lower rank that Project D incremente them by one ..

bpat1434

Example:

Proj. A - 90
Proj. B - 80
Proj. C - 70
Proj. D - 60

Move Project A below Project C but above Project D:
Proj. A - 65
Proj. B - 80
Proj. C - 70
Proj. D - 60

Order by query, ordering in descending order:
Proj. B - 80
Proj. C - 70
Proj. A - 65
Proj. D - 60

Understand? It might take some fancy work, and some real hard time thinking exactly how to implement, but that's just the general idea.

moggy

Great, thanks for all your help guys - I get the idea now!