homepage Welcome to WebmasterWorld Guest from 54.204.231.253
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




msg:4481034
 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....

 

Dijkgraaf




msg:4481082
 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




msg:4481084
 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

garwil




msg:4481264
 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