• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

auto incrementing a table values MySQL

I have a database named universities that looks like this:

SchoolID       SchoolName
2                          A
3                           B
5                           C

I removed schools I didn't need and now I want to redue the schooID so that the database looks like this

SchoolID     SchoolName
1                        A
2                         B
3                          C

In my real database there are 1000 values so I need a way to do this programmatically. How do I do this with PHP and a MySQL database?
0
NewWebDesigner
Asked:
NewWebDesigner
1 Solution
 
Rik-LeggerCommented:
You could do an sql export without the incremental value and then insert it back again into an emptied table.
0
 
Dave BaldwinFixer of ProblemsCommented:
Other than looking nice, what are you wanting to accomplish?  MySQL and other SQL databases don't necessarily keep rows in the order they are entered.  They just find the next empty space large enough in the table file and put the INSERTed row there.  The "ORDER BY" clause is the only thing that is guaranteed to return rows in a certain order.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree: ID values should not be "nice", just be "correct".
having "holes" in the ID values should not disturb anyone.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Mohamed AbowardaSoftware EngineerCommented:
Remove auto increment from ID table field, create PHP script to execute SQL queries like the following (Change TableName to your table name):
 
$i = 0;
$sql = mysql_query("SELECT * FROM TableName ORDER BY id");
while($rows = mysql_fetch_array($sql))
{
	$id = $rows['id'];
	mysql_query("UPDATE TableName SET id = $i WHERE id = $id");
	$i++;
}

Open in new window


Add auto increment back to the ID table field.
0
 
Mohamed AbowardaSoftware EngineerCommented:
Change $i to 1 instead of 0, the code will be as the following:

$i = i;
$sql = mysql_query("SELECT * FROM TableName ORDER BY id");
while($rows = mysql_fetch_array($sql))
{
	$id = $rows['id'];
	mysql_query("UPDATE TableName SET id = $i WHERE id = $id");
	$i++;
}

Open in new window

0
 
Mohamed AbowardaSoftware EngineerCommented:
Sorry, $i = 1; in the first line.

Mistyped twice.
0
 
NewWebDesignerAuthor Commented:
alright guys, thanks for the responses. I will try Medo3337 and see if that works.  As to whether I should clean u pthe data or not, i have not decided.
0
 
Ray PaseurCommented:
If you can change the auto_increment column, then you can dispose of the auto_increment column since it does not need to be intact.  There is no extra credit for carrying data fields that have no meaning.  So maybe the best thing is to use ALTER TABLE and remove the column once and for all.

But I would probably not do that, if it were my app.  Instead I would get this book and learn about how PHP and MySQL work together.
http://www.sitepoint.com/books/phpmysql4/

Then I would read the pages linked here:
http://lmgtfy.com?q=Should+I+normalize+my+database
0
 
Vimal DMCommented:
hai,

There are three ways of doing this

1) Can fetch the records from the particular table and insert into the new table and make the process

2) Can export the table without the auto_increment id ,empty the table and import  again

3) Or write an update query where the id can be reset as "Medo3337" mentioned
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now