Welcome to WebmasterWorld Guest from 54.211.136.250

Forum Moderators: open

Message Too Old, No Replies

Mysql; Insert / Update a record - re-order a priority no.

Re-ordering a list of priorities in a table

   
6:02 pm on Aug 1, 2012 (gmt 0)



Hi all,

Hope someone can point me in the right direction.

I have a table with the following fields:

venue_id (int 20)
rank (int 20)

I have 10 records and each has a different "rank" like 1,2,3,4,5,6,7,8,9,10 etc. When the user views these records they are presented by "rank ASC". All cool in adding the records and viewing them as it stands BUT here comes the issue I am facing.
If I add an additional record and I want that new record to have a "rank" of 5 then I am wanting all the existing records with a rank of 5 or below 5 i.e. existing number 5 as well as 6-10 to be changed ON SUBMIT. So then the new record gets inserted as 5 and the old number 5 becomes 6 and 6 becomes 7 and so forth... Is this possible?


thanks in advance....
9:19 pm on Aug 1, 2012 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



You would first have to do an update before you insert the new one.
e.g UPDATE ranktable SET rank = rank + 1 WHERE RANK >= rankofnewrecord
9:36 pm on Aug 1, 2012 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



And if rank is a UNIQUE or PRIMARY key you will need to do the update in descending order
2:15 pm on Aug 2, 2012 (gmt 0)



Many thanks Dijkgraaf and brotherhood of LAN for your advice which should do it..