Page 1 of 1

Cascade Update value in other records

Posted: Thu Oct 07, 2004 4:36 pm
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

Posted: Fri Oct 08, 2004 3:32 am
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

Posted: Fri Oct 08, 2004 7:44 am
by timvw
and lookup how transactions work, because with concurrent users you WILL see weird things happening ;)

Posted: Fri Oct 08, 2004 12:19 pm
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

Posted: Sat Oct 09, 2004 5:32 am
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