We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

auto incrementing a table values MySQL

NewWebDesigner
on
Medium Priority
242 Views
Last Modified: 2012-05-11
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?
Comment
Watch Question

Top Expert 2011

Commented:
You could do an sql export without the incremental value and then insert it back again into an emptied table.
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
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.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I agree: ID values should not be "nice", just be "correct".
having "holes" in the ID values should not disturb anyone.
Mohamed AbowardaSenior Software Engineer
CERTIFIED EXPERT

Commented:
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.
Mohamed AbowardaSenior Software Engineer
CERTIFIED EXPERT

Commented:
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

Mohamed AbowardaSenior Software Engineer
CERTIFIED EXPERT

Commented:
Sorry, $i = 1; in the first line.

Mistyped twice.

Author

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.
Most Valuable Expert 2011
Author of the Year 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Vimal DMSenior Software Engineer
CERTIFIED EXPERT

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.