homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Mysql; Insert / Update a record - re-order a priority no.
Re-ordering a list of priorities in a table

Msg#: 4481032 posted 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....



WebmasterWorld Senior Member 5+ Year Member

Msg#: 4481032 posted 9:19 pm on Aug 1, 2012 (gmt 0)

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

brotherhood of LAN

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

Msg#: 4481032 posted 9:36 pm on Aug 1, 2012 (gmt 0)

And if rank is a UNIQUE or PRIMARY key you will need to do the update in descending order


Msg#: 4481032 posted 2:15 pm on Aug 2, 2012 (gmt 0)

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

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved