Updating multiple rows to sort them

[dbzfyam]

I have a table (see below) which I use to store the categories of our site. I use the row called "cat_sort" to display those categories in the order the user chooses in the admin panel. The problem is, I have no idea how I can update those rows when the user decides to add a category to the list (so for example, they want to add a category after "IPC Products" (4). How can I update the rest of the rows so the cat_sort column doesn't have double numbers? Can anyone help me with this?

Thank you in advance,
Stefan

CREATE TABLE `mp_products_subcategories` (
 `ID` int(20) NOT NULL auto_increment,
 `cat_parent` int(20) NOT NULL default '0',
 `cat_name` varchar(50) collate latin1_general_ci NOT NULL,
 `cat_enabled` tinyint(1) NOT NULL default '1',
 `cat_link` varchar(150) collate latin1_general_ci NOT NULL default '',
 `cat_image` varchar(100) collate latin1_general_ci NOT NULL default '',
 `cat_sort` tinyint(3) NOT NULL default '0',
 `h_menu_enabled` int(10) NOT NULL default '0',
 `show_cat` tinyint(1) NOT NULL default '1',
 `cat_description` text collate latin1_general_ci NOT NULL,
 `orderinfo` text collate latin1_general_ci NOT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `cat_name` (`cat_name`)
 ) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=26 ;

INSERT INTO `mp_products_subcategories` (`ID`, `cat_parent`, `cat_name`, `cat_enabled`, `cat_link`, `cat_image`, `cat_sort`, `h_menu_enabled`, `show_cat`, `cat_description`, `orderinfo`) VALUES 
('1', '3', 'IPC Products', '1', 'products.php?page=list&cat=1&name=IPC Products', 'data/IPCProducts/349s.jpg', '4', '0', '1', '', ''),
('2', '3', 'Complete Systems', '1', 'products.php?page=list&cat=2&name=Complete Systems', 'data/CompleteSystems/257b.PNG', '3', '0', '1', '', ''),
('3', '3', 'Chassis', '1', 'products.php?page=list&cat=3&name=Chassis', 'data/Chassis/img45b6c53604e37.gif', '2', '0', '1', 'Test', ''),
('9', '3', 'Index Products', '1', 'products.php?incfile=admin/indexProducts.php', '', '1', '0', '0', '', ''),
('22', '3', 'Power Supplies', '1', 'products.php?page=list&cat=22&name=Power Supplies', '', '5', '0', '1', '', ''),
('23', '3', 'Peripherals', '1', 'products.php?page=list&cat=23&name=Preferables', '', '6', '0', '1', '', '');

[cameraman]

You could do something like:

UPDATE mp_products_subcategories SET cat_sort=cat_sort+1 WHERE cat_sort>4

then insert the new record.

[dbzfyam]

Thank you for the help, cameraman! I just tried it and it works perfectly. I didn't even know that was possible in a query. Simple but effective :). Thanks again!