User defined sort order?

[petewilliams]

I'm currently creating an image gallery for a friend to display a portfolio of his artwork. He's not very web savvy so I'm making him a CMS to go with it.

One thing he wants to be able to do is re-order the display order of his images, and I'm having trouble figuring out how to do it.

My best idea is to add a sort_order to my database like so:


img_id sort_order
1 2
2 3
3 1


So when the page was displayed, it would display the images in the order 3, 1, 2.

However, I'm not entirely sure how to implement this, particularly when it comes to actually re-ordering the images.

I've been searching for about an hour but haven't found anything helpful yet, does anyone have any ideas?

Thanks,

Pete

[chazzy]

I'm not sure I follow. There's 2 approaches i would think - sort_order exists in the same table as the img, or a separate table. Your query would then look like this...


select * from img
where....
order by sort_order ASC|DESC


Or.

select i.* from img i
inner join sort_order so
on so.img_id = i.img_id
order by so.sort_order ASC|DESC

[petewilliams]

Hi Chazzy, sorry I didn't make myself clear.

I understand how to SELECT the image IDs from the database and order them by the sort order, that's straightforward enough.

The bit I'm having trouble with is let's say my friend wants to move an image up in the sort order. I'd have a link he could click, and that would somehow have to swap the sort order of that image with the one that is currently above it.

So, say I have three images, one.jpg, two.jpg and three.jpg, and they have sort orders of 1, 2 and 3 respectively. My friend wants to change it so that they display in the order of one, three, two, so he effectively needs to swap the sort orders of two and three.

I'm not sure how to structure an SQL query that says:

'UPDATE table SET sort_order = sort order of image above this one WHERE img_id = 3 AND sort_order = sort order of image below this one WHERE img_id = 2'

It's also not as easy as simply incrementing one and decrementing another because there might be missing values in the sort_oder column after deleting records. That isn't a problem when I come to use ORDER BY because it'll still sort it in the proper order.

I hope that makes it a bit clearer, I'm having trouble verbalising it!

Pete

[aj_nsc]

I just finished (about a month ago) designing an AJAX powered CMS which allows users to do something similar, except it is the display order of the links on the page instead of the display order of images (your case) that they can alter. I accomplished it using two queries.

Say your friend wants to swap images that are ordered 2 and 3. I would have two links next to each image (Move Up and Move Down, of course, where applicable, obviously the image ordered 3 cannot be moved down).

So let's say he clicks on Move Up next to image in position two 2, I would send 2 queries - both based on the id of the image


update `(tablename)` set sort_order = '1' where img_id = '1' limit 1;
update `(tablename)` set sort_order = '2' where img_id = '3' limit 1;

[petewilliams]

Thanks aj, I see what you mean but I can't see how it would work if records were deleted in between two images that I wanted to swap. Their sort orders might be 2 and 5 for example, assuming the images with sort orders 3 and 4 had been deleted. This means that if I increment one and decrement the other I am left with orders of 3 and 4, which are still the wrong way around since I want the first one to be larger than the second.

The only way I can think of getting around this would be to regenerate the sort orders whenever a record is deleted from the database, to ensure that there are no gaps in the sort order.

Thinking about it that might be the best way, and not too tricky to do...

Pete

[aj_nsc]

Exactly pete. That's what I had to do, whenever you delete a record, grab the sort order from that record, and apply it to the next record that has a sort order greater than it, and then apply that sort order plus one to the next record that has a sort order greater than it - you can do it in a loop. Here's an example with some php code, if you don't mind

(this is just an example on how to run the queries, I didn't include all the error routine (i.e. die) statements that I should have here)

<?php
$sql = "select sort_order from `(tablename)` where img_id = '(deleted img id)' limit 1";
$deletedSortOrder = mysql_result(mysql_query($sql));

$sql = "update `(tablename)` set sort_order = sort_order-1 where sort_order > ".$deletedSortOrder;
mysql_query($sql);
?>


If you run that kind of routine everytime, you will never have to worry about missing a sort_order, it'll always stay contiguous.

[chazzy]

Pete,

The issue you're describing is a very common issue when it comes to any client/server application development (since many users can be modifying many records at a given time). The 2 most popular ways of checking on this issue (making sure a user doesn't modify a record another user has modified) both reside in the application, rather than the database (though a trigger to handle it wouldn't be too difficult either).

1. Create a checkout/checkin system, where only a single user can have an item "checked out" at a given time.

2. Keep track of a last modified date/time. If the last modified time currently of the record is different than the last modified time in the database, kick it back as an error.

Method #1 is more fool proof than #2, but has the expense of being extremely complicated for a small application. #2 is the default behavior of most persistence frameworks.

You also have to realize that for any change you're making, you're not just updating one item. Let's say you have 1,2,3,4,5 as both item id and sort order. You then move 5 to behind 2. YOu need to issue the following update statements, since you really just modified 3 items (even though only 1 moved).


update imgs SET sort_order = 3 where id = 5;
update imgs SET sort_order = 4 where id = 3;
update imgs SET sort_order = 5 where id = 4;


I hope that this helps, conceptually.

[aj_nsc]

Exactly right about the updating 3 queries which is why I suggest a simple Move Up/Move Down system where everything can be handled with the 2 queries I provided above.

[chazzy]

So... you're limiting the capabilities of the application because it's hard to code?

[petewilliams]

Yeah I think I'm going to stick with a simple move up/down link, which will only require two updates to swap the record sort orders. The site will only have a few images on and he won't be changing the order very often so it's not all that important for me to add the ability to move a record more than one place at a time, plus it makes the interface easier to code.

Is there a simple command to swap the values in two rows or am I going to have to do it in multiple queries?

Thanks,

Pete

[aj_nsc]

i've googled it and i've only come up with that for an update query to update different records with different data then you need different queries. I could be corrected, but I couldn't find anything.

[petewilliams]

Ok thanks, looks like it might take me a few queries to get it all done but it's not really an issue because he'll only be making the odd change here and there!

Thanks for all your help guys.

Pete