Mysql Help Regarding Renumbering 'id'

mushy

I have mysql table with > 6000 entries. Entries are in the order but their ids are not in order. I want to rename the id number so that id becomes in order as well...

Here is an small example what I want...

Currently Table look like somewhat as follows:

CREATE TABLE `search` (
  `id` int(11) NOT NULL auto_increment,
  `test` varchar(12) default NULL,
  `test2` mediumtext,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

INSERT INTO `search` VALUES (1, 'Entry 1', 'abcd');
INSERT INTO `search` VALUES (2, 'Entry 2', 'efgh');
INSERT INTO `search` VALUES (5, 'Entry 3', 'ijkl');
INSERT INTO `search` VALUES (8, 'Entry 4', 'mnop');
INSERT INTO `search` VALUES (7, 'Entry 5', 'qrst');
INSERT INTO `search` VALUES (3, 'Entry 6', 'uvwx');
INSERT INTO `search` VALUES (4, 'Entry 7', 'yz');
INSERT INTO `search` VALUES (6, 'Entry 8', '1234');
INSERT INTO `search` VALUES (9, 'Entry 9', '5678');
INSERT INTO `search` VALUES (10, 'Entry 10', '9211');

In above example what I want to do is table remain in same order as as it is but id values should be changed... So that table looks like this now

CREATE TABLE `search` (
  `id` int(11) NOT NULL auto_increment,
  `test` varchar(12) default NULL,
  `test2` mediumtext,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

INSERT INTO `search` VALUES (1, 'Entry 1', 'abcd');
INSERT INTO `search` VALUES (2, 'Entry 2', 'efgh');
INSERT INTO `search` VALUES (3, 'Entry 3', 'ijkl');
INSERT INTO `search` VALUES (4, 'Entry 4', 'mnop');
INSERT INTO `search` VALUES (5, 'Entry 5', 'qrst');
INSERT INTO `search` VALUES (6, 'Entry 6', 'uvwx');
INSERT INTO `search` VALUES (7, 'Entry 7', 'yz');
INSERT INTO `search` VALUES (8, 'Entry 8', '1234');
INSERT INTO `search` VALUES (9, 'Entry 9', '5678');
INSERT INTO `search` VALUES (10, 'Entry 10', '9211');

I use phpmyadmin to manage my databases. Is there way to do it by running some mysql quiree...? If we can do it then what is the mysql command for that?

MarkR

You have failed to understand how database tables work. They aren't ordered in any way. If you don't put in ORDER BY, the results will be in an arbitrary order.

There is no concept of storage order in MySQL or most other relational databases.

Moreover you should not care what the IDs are- if your application relies on the values of ID columns, it has a design fault.

Mark

Plasma

In addition to the above post, you really need to rethink what you are trying to achieve - you are going about it the wrong way.

The 'id' field in that column is a unique, auto incrementing field - its just used to assign a unique ID to that row.

If you want to number your results, do it programatically in your PHP code.