Howdy guys,

You have one'hell'of'a'fancy place out here!

Though I have a problem. I got a database full of MySQL - 4.1.22 that is being ordered around by phpMyAdmin - 2.8.1. The database has entries containing portfolio projects in three different fields categories.

For every category I want to define an order or sequence. I created a variable for this called "volgorde".

Now whenever I add a project into the db - using a form to do that -, into a certain category, THAT category (only) is updated by a +1 for every entry already there that has the same or higher "volgorde" (sequence/order) as the inserted entry. (Basically, all three categories can have their own 1 to N lists for sequence)

So far so good that works!

Now when I MOVE an existing project that already has a "volgorde" up or down, the same trick has to happen, but when I move a project down, the projects that are "jumped over" do not go up.

The first query should -1 all entries that have "volgorde" bigger than the entry that is being moved, AND smaller or equal to where the moved entry is going. Example: rank 1 moves to rank 3: rank 2 and 3 should move up: -1.

The second query should +1 all entries that have "volgorde" smaller than the entry that is being moved, AND bigger or equal to where the moved entry is going. Example: rank 3 moves to rank 1: rank 1 and 2 should move down: +1.



I immediately agree that I'm quite n00bish at mySQL. The really frustrating part is that I know that this should be possible, I know my syntax is good enough for you to understand, but it's just not good enough for that good'old databases.

Also, this is an addition to an already existing framework that deals very well with the projects and all kind of video and images. The only functionality I missed was a way to deal with an order/sequence to be filled into the edit form; AND WORK!

It's difficult to know exactly what you're trying to do.

Are you trying to change the order of the actual database entries?
Or just the output that displays on your form?

If you're trying to change the order of the actual database entries don't do it. Read this link to see why. It's much easier to sort the output using commands such as ORDER BY etc.

I use the ORDER BY command. What I'm trying to do in the code (which has already changed to the code below) is making sure that the variable 'volgorde' which handles the order from 1 to n is updated whenever I change the order 'volgorde' of one of the entries.

An example: all entries have 'volgorde' to deal with the order.
Entry 1 moves to 3. Entries 3 and 2 should move up so that 2 becomes 1 and 3 becomes 2. This must be done in the code below.



What could be wrong in my code?

As far as i can see there are 2 steps involved.
Instead of doing them both and wondering why it doesnt work, how about:
dump the table/data
do step 1
dump the table/data
do step 2
dump the table/data

Then you can at least trace what is going on.

That query looks about right, except I think you want <= or >= in it, since in your example, you move 1 to 3, so 1 is lowest, and 3 is highest, however you want to match the previous 3 , and if you were moving 3 to 1, you'd want to match the previous '1' . Hopefully you have a separate index for each of these entries besides 'volgorde' that you can refer to them with.

It may help to echo out what $query looks like with actual data to make sure it is getting filled out correctly.

If I seriously wanted to not just reorder all the time but store that order as a fixed value in the database, here's how I'd do it.

Make my rank field a float. New records can be rank + 1.0. Now, let's say you want to move an item around. You essentially want to place it between two existing records. Assume I want to move my item to between ranks 2.0 and 3.0, I'd make the rank of the row I wanted to move 2.5. Say I want to move one to between 2.5 and 3.0, it's 2.75 and so on. The end is the end, just add +1. The top is 0 and whatever the top rank is, so putting something on the top is current top rank/2.

Hope this helps and makes sense.

That would work, untill you run out of precision.
The difference between 2.555555555555555555 and 2.5555555555555555575 might be to small for the database to put into a float, and then the mechanism fails.

A more robust solution would be to use multiples of 2, insert the in-between value and then, using a stored proc or something, renumber the rank.

That is how i implemented something similar anyway. (6 years ago on postgresql 7.0)

some of the values like $lowest don't return a value when I echo them =/