CODEWITHSUNDEEP
phpmysql
Reham Fahmy
Reham Fahmy

Reputation: 5063

Sort rows in database Mysql

I've database with a lot of entries and with help of some friends here i manage how to remove the duplicates lines deleting-duplicate-rows-in-a-mysql-database but now i've to face new problem with is unsorted rows.

here is example of my database now

CREATE TABLE `my_table` (
  `id` int(10) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `address` varchar(255) NOT NULL default '',
  `phone` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `my_table` VALUES (784, 'Albert', 'EGYPT', '202020');
INSERT INTO `my_table` VALUES (21, 'John', 'USA', '984731');
INSERT INTO `my_table` VALUES (56, 'Albert', 'EGYPT', '343354');

as you can see at id [784,21,56] so is there any how i can resort it to be

INSERT INTO `my_table` VALUES (1, 'Albert', 'EGYPT', '202020');
INSERT INTO `my_table` VALUES (2, 'John', 'USA', '984731');
INSERT INTO `my_table` VALUES (3, 'Albert', 'EGYPT', '343354');

id [1,2,3,4,....etc] resort it , I'm aware that change in ID may cause problems in script itself since it depends on id to garb the entry informations but since it is new then i need to sort it 1,2,3,4,.....etc.

Upvotes: 1

Views: 481

Answers (5)

Mikey.
Mikey.

Reputation: 37

this question ( Reorder the database record rows through front-end using php ) does exactly what you want, although it uses php to accomplish the task.

grts,

M.

Upvotes: 1

Fred
Fred

Reputation: 5006

What about puting an auto increment on your table?

CREATE TABLE `my_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL default '',
  `address` varchar(255) NOT NULL default '',
  `phone` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Then you just need to insert values without specifying ids.

[EDIT]

You could also order your rows like this without changing anything in your table structure

select @rownum:=@rownum+1 as rank, t.*
from my_table t, (SELECT @rownum:=0) r
order by rank;

Upvotes: 1

Michał Powaga
Michał Powaga

Reputation: 23173

Use AUTO_INCREMENT column:

CREATE TABLE `my_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL default '',
  `address` varchar(255) NOT NULL default '',
  `phone` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

and then insert:

INSERT INTO `my_table` VALUES ('Albert', 'EGYPT', '202020');
INSERT INTO `my_table` VALUES ('John', 'USA', '984731');
INSERT INTO `my_table` VALUES ('Albert', 'EGYPT', '343354');

Upvotes: 1

Grim...
Grim...

Reputation: 16953

If you're sure you want to do it, I'm pretty sure removing the id field and adding it again (indexed and with autoincrement) will renumber the rows.

I'm not sure, though, because it's not the kind of thing I've ever done. Are you sure you want to do this? What does it matter if they're numbered strangely?

Also, there's no guarantee that MySQL will renumber the rows the way you want - it might well jumble them up a bit.

If you want them ordered by country, or something, you'd have to use some PHP and update the rows one by one, which isn't as hard as it might sound.

Upvotes: 1

Florin Frătică
Florin Frătică

Reputation: 587

I think if you delete the ID field and create it again (with autoincrement) this will fix your problem. But I never checked this so first make a backup of your database.

Upvotes: 1

Related Questions