[SOLVED] Adding an Order Field

[gevans]

Hey guys,

 

I'm updating a CMS to allow for re-ordering content. My first step is to add the `order` field to my db table.

 

After that's done I want to run a query to fill in the new field.

 

The table after adding the order field;

CREATE TABLE IF NOT EXISTS `pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(44) NOT NULL,
  `url` varchar(44) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `order` tinyint(1) NOT NULL,
  `active` varchar(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=89 ;

 

For each `parent_id` I want to increment the value in `order`. So for the 6 pages with a `parent_id` of 2 I want the `order` values to run from 1 to 6.

 

Is there a simple way to do this via a SQL without any PHP loops etc?

 

Cheers,

gevans

[jazzman247]

You code block is being skipped because the table already exist.

Try this...

 

ALTER TABLE `pages` ADD `order` tinyint(1) NOT NULL;

[gevans]

I already have the `order` field in the table. I'm looking to populate the new field

[fenway]

You can use user variables to mimic row numbering.

[gevans]

Sorry, forgot to close this thread. I just wrote a small php script to update the db, cheers