UPDATE in MySQL suggestions

Catharsis

Hi there,

This is a first post for me, I apologise if this has been asked already but I couldnt find anything in the search.

I have a "Priority" field in a table which is numeric. Now I want to be able to increase and decrease the value of this field and not have any duplicate numbers.

So in theory if I have 2 records, 1 with prority 1 and one with priority 2 and I move the priority 2 one up, thus making it now a priority 1.

I know that I have to also edit record 1 because that would then become priority 2.

However if say I have 30 records and I make record 30 with priority 30 now priority 1 I surely have to update all 30 records to ensure there are no duplicates.

Can anyone suggest an easy way to do this, everything I think of seams a bit over-complex 🙂

Roger_Ramjet

See my solution to this problem in this thread . It is the simplest approach in the long run. I diud also try to solve the same problem with php, and posted in the code critique forum where everyone had a far better solution than mine. But doing it with sql queries is the best approach in the end as using php would require you to still use queries to update the table even after you had resorted the list.

Catharsis

Thanks for the suggeestions Roger 🙂

I was thinking about this whilst cycling home last night (how sad am I?) I got a similar idea through my head as your suggestion.

See you have to rely on the priority list being unique withough constricting it in the DB which mean you have to check all records on input (no problem there) and when you move it.

So assuming the data is in this state we can easily do what I want with a choice of statements.

If I'm moving a record up or down one place then I only need to update 2 records. The one moving up one place (from 25 to 24 for example) and the record that is currently 24 down to 25.

With that in mind its a simple case of two updates

UPDATE table SET priority = 25 where priority = 24;

and

UPDATE table SET priority = 24 where urn = $urn;

Taking this further to put it up more than one place at a time I have to do a slightly bigger update and again I can do that in 2 updates. Again the record Im moving is going up in priority.

UPDATE table SET priority = priority + 1 WHERE priority >= $priority_value;

and

UPDATE table SET priority = $priority_value WHERE urn = $urn;

Hope that makes some sense 🙂

Any more suggestions let me know.

Roger_Ramjet

It is not quite as simple as you suggest which is why my solution is more complex than you think you need. This problem comes with inbuilt booby-traps which I'm sure you'll realise when you think about it some more.

Catharsis

I must say I do like the way you detect weather you are moving the item up or down in the list 🙂

I'll borrow a bit of that code if you dont mind. Im cbeing awkward because as well as wanting to move the items up individually I'd like to do a whole bunch at any one time as well.

Learn to walk before you can run though 🙂

I'll get the single move up or down 1 place working first, then a single form based move where the user inputs what number they want and then a multiple move I think 🙂

Roger_Ramjet

Nope. You just listed some of the booby-traps.

My solution makes 1 move and reloads the page with the new order. Try it with multiples and you have a complexity problem that is just not required.

Forget the move up or down 1 place. A general purpose solution handles that as well, so it is redundant.

Major booby trap is your forgetting that those above or below the movement range stay the same. The query you outline would move everything above or below, so you'll end up with gaps in the sequence. OK in theory it will still work, try it for 20 re-orders and see what you end up with. Even if your code can handle it, users won't know what is going on.

The chap I coded my solution for has it up and running live with the final script on that thread. You can lift my code as-is from it and all you'll need to change are table and field names in the queries, and on the html output statements.

Catharsis

Yep I understand totally.

I have the single move up and down 1 place at a time now working.

There is a website I use for DVD Rental in the UK (LoveFilm) that handles mass priority update really well and I want my script to work like that.

As far as I can tell they if you listed all items in the form as priority 1 it takes the last one that you listed as priority 1 as the most important and thenh works backwards.

Ive never been a fan of telling the user they are essentially thick and they cant do that. We can just assume most users are that silly 😉

Oh if you have a look at LoveFilm and want to sign up let me know, if I drop you an invitation you get 4 weeks trial instead of 2 and a discount on your first paid month 🙂