Cascade Update value in other records

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
leroyb
Forum Newbie
Posts: 2
Joined: Thu Oct 07, 2004 4:22 pm

Cascade Update value in other records

Post by leroyb »

Hello,

I have the following problem and I hope someone can help me out ....

I am developing some kind of web content management interface using php and mysql and to do this I have a form that calls up the content of a database field (the page content) and allows you to update it ... It all works pretty well, however I bumped against some problem .. when you edit a page or add a page you can give it a menu position ... (my left menu is created dynamically and the links to the various main pages are sorted according to the value in the field menu_pos in the table tbl_content, which contains the site content) ... but what do I need to do to update the menu_pos of the other pages? :?: ...
say I have five pages (homepage, about, services, links & contacts) with each a menu_pos from 0 to 4 in the same order as the pages .. and now I edit the page 'Services' and decide to 'promote' it to menu_pos 1 .... therefor the code will have to update the record for page 'About' and change its menu_pos value to 2 (else I have 2 records with menu_pos = 1) .. how do I do that??
Any ideas, pseudo-code , real code would be greatly appreciated.

Thanks,

kind regards,

Leroyb
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Before you change the position of the menu item you could have the code change the positions of the records that will affected by the move:

For example if you had 6 records: 1 2 3 4 5 6 and you decided to promote record 4 to position 1, you would have to increment the three records affected and change record 4's position to 1:

Code: Select all

UPDATE table SET position = position + 1 WHERE position >= 1 AND position < 4;
UPDATE table SET position = 1 WHERE ID = 4;
Now the records will be in the order 4 1 2 3 5 6.

Mac
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

and lookup how transactions work, because with concurrent users you WILL see weird things happening ;)
leroyb
Forum Newbie
Posts: 2
Joined: Thu Oct 07, 2004 4:22 pm

Post by leroyb »

Thanks for this! much appreciated .. when it comes to the transactions, I will indeed look into it, however there will be very very few admins on the site so I should be ok with it however it would be nice to make things 'future-proof' ...

thanks again,

leroyb
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

One way to prevent some issues would be to put a flag somewhere that says positions are being updated which prevents other admins from starting their own update until this one is complete. It's not transactions but it may be somewhere to start.

Mac
Post Reply