User defined ordering of DB rows

bigjonnyr

I have a feeling that there should be a really cool way of acheiving this, but I can't seem to crack it. I just thought I'd see if anyone else has come across this before and is willing to supply that spark of genius I so desperatly need!

Here's what I'm trying to do:

I have a fairly basic mySQL table as follows:

chapter_id INT AUTO_INCREMENT
chapter_sequence_id INT
chapter_title VARCHAR(255)
body LONGTEXT

I am printing the chapter titles out to the PHP page, hyperlinked with a chapter_id reference in the querystring that leads off to another page - fairly standard stuff.

But..

I would like the user to be able to change the order that the chapters appear on the page (independent of the chapter_id's which aren't necessarily consistent/contiguous).

I have 2 buttons to the left of each chapter on the page. One moves the chapter up a position and the other moves it down a position. The first chapter cannot be moved up a position (because its first!) and the last similarly cannot be moved any further down the list.

The numbers that represent the position of each chapter need to be kept contiguous within the table, as they will be used in another part of the application to link between the various chapters.

My intention is to store the "actual" position of the chapter in the second INT field in the DB table and use this to ORDER BY in my sql statement.

Does anyone have any bright ideas on how to handle the re-ordering of the records when the user clicks and up or down button?

Once I have the new order in an array (for example) it should be relatively easy to do an UPDATE statement on the table to change all the chapter_sequence_id's accordingly.

I suppose I just need to know the best way of re-ordering the records using PHP before UPDATING the DB table and re-loading the page.

Any ideas will be greatly appreciated!

[deleted]

First, re-loading the page does not have to include a database update, you could just do a dry-run untill a 'save order' option is selected.

A fast way would be to print a dropdown box after each chapter that contains all the positions that the chapters can have (as opposed to the up/down options)
On submission you check if the order is still valid (no chapters may have been selected for the same position). If it's valid, you can either save the new order to the database, or re-print the chapters in the new order to let the user verify.

Another way would be using javascript, you could make a selectbox and create functions to move chapters up and down, but that's going to take some fiddling, and won't work on all browsers.

bigjonnyr

Thanks for the input vincent.

I agree with you about the numerous trips to the database, and I like the idea of a dry run/save changes button.

As it happens, this instance of the application would have no more than 5 concurrent users, so the database hits wouldn't have been a major issue, but I will build it "future proof" anyway, and use the DB trips sparingly.

I have to say about the dropdown box thing, that I had considered something similar and decided that it would get really messy for the end user. If there were 50 chapters on the page, the user could get into real trouble trying to re-order them with pulldowns.

I am now starting to think along the lines of storing a comma seperated list of each chapters position (each one slightly modified), in the querystring of each link (up and down button). I could generate this dynamically from an array without too much hassle.

This array would then be fed back upon itself when the user clicked one of the arrows/hyperlinks. The save buttons querystring would always have the "latest" array/comma seperated list and an additional querystring var telling the application to write to the database!

This would allow the user to change the order as much as they wanted by just feeding an array back on itself. Only when they click the save button, does the new order get sent to the DB.

Whether or not this will work in practice has yet to be seen.