hi, Can we reset the id auto increment in the mysql database using by phpMyadmin? let's say the shoutbox. the id #number table.. somehow i deleted some rows.. and i wanted to re-order/reset the id number acendingly in order rather than skip out.. thks

Reply

I don't think you can do that using phpMyAdmin, or even SQL for that matter, but you could write a PHP script to do that for ya...
Make a query like:

Select ID from <table> order by ID

then use a while/for loop to iterate through the results, and update each row with a new number for the ID.. you could use a PHP variable to track the current ID number, and increment it with each loop...

Update <table> set ID=???

Should work.. Although I have never tried manually updating an auto_increment field.. come to think of it, it seems pointless to adjust it, since it is dealt with internally anyway, and the actual value should never affect your script...

If that doesn't work, and you still need to sort them, try adding another field to the DB table, like a sort-order field..

Reply

Please be warn that this is the manual-intensive-labor way, but no thinking require.

If you want to change any field in the table and do not mind changing only 1 field at a time, you can always go to php admin.

Then, select your database.

Next, click on the "table" you want to modify.

Last find the field and click edit; so basicially, click on field of a time and edit.

Hopefully, you don't have much entries in that table; otherwise, I highly unrecommend this route.

Well, hope it helps.

Reply

yes, System that's perfect and exactly true. Thanks for that trick. Indeed a littol risky humm.. phpMyAdmin should do that function by default. See whether i can request the feature about that on their sourceforge site.

this is the old and null way backup and re-construct

Thanks System and tigen for discussing this,

 

 

 

Reply

I asked about this a while ago. A quick and easy way to set the next ID is this:

where [tablename] is the name of the table and [number] is the next ID. I'm not sure of a quick way to change other, existing IDs, though.

Reply

It would be better not to do reorder of ID fields anyway. I do not want even to show them in my forms, grids. Maybe the new numbered field would be the best way to solve it.

Why shoud everybody avoid changing ID fields? Simply because of possibility of having relations in database between tables. ID fields sometimes have their main role in relations and playing games with them will make a mess. I would never do it.

Reply

This is a pretty primitive solution, but after you delete the rows which you wish to remove from the table, you could export the remaining rows into either a .csv file. In this subsequent excel file you could remove the first column which contains the id, save this file, and then re-import this back into your database table (you should click on the option of over-writing rows in this table which have the same id/primary key value as a row in the excel file), or re-import into a new table. The ids will be automatically re-set without any numbers skipped - it should not take too long, plus you should not lose any data in the process

Reply

I don't think you can reset, you can only change value in phpMyAdmin.

Reply

I know it's a zombie post, but I thought I'd give my part nonetheless.



The above will reset the auto_increment to the lowest value. So everytime an entry is removed, drop any remaing ID's that are larger than the current entry by one, then query the database with the exact line above and you wont have any problems.

Sweet!

Reply

ALTER TABLE `tablenamegoeshere` AUTO_INCREMENT =0
Can Reset The Id Auto Increment?

Replying to najib
It's not a zombie post! Thanks! Saved a lot of time! :)

Reply