Need Help In Database Auto_increment

[Eggie 0]

i am creating a game and i set when someone registers than he gets id...in "extra" i have set it to be "auto_increment" but whenever new player signs up he gets number bigger than 210...(my first id was 211,next was 212,third was 213,fourth got 214) how to set it to go from 1 to infinite? thnx

Edited January 20, 2008 by Eggie (see edit history)

[turbopowerdmaxsteel 0]

You must have manually inserted the 211 ID. If you can afford to empty the table and re-enter the values the problem can be solved. In your insert query, leave the value for the AUTO_INCREMENT field to blank. For example, say you have a table with two fields ID (the auto number) and name (a varchar). Use the following query to insert a record with IDs starting from 1.

INSERT INTO MyTable VALUES('', "Partho")

You can set the initial number to a greater number but you can't get it back down (or so I think) unless you empty/re-create the table. Use the following query to start the numbers from 200.

INSERT INTO MyTable VALUES('200', "Partho")

From the next time, you can just use the previous query to continue with numbers 201, 202 and so on.

[faulty.lee 0]

If you had inserted some record before, and deleted them, the auto increment number will still remain from the last one that you inserted, e.g. 210 in this case, so the next data you insert will start from 211. For this you need to reset the auto_increment counter. Assuming you're using MySQL

ALTER TABLE your_table AUTO_INCREMENT = 0

Then your next inserted data will start with 0, then 1, 2, 3 .....

Hope that's what you're looking for

Good Luck

PS: For your information, the auto_increment is made to remain as last even though you've deleted your record, for the purpose of data integrity. It was done so that even you've deleted your old record, anything that refers to that ID won't accidentally belongs to another row of data. For example, you have your first row with ID=1, and another table that refer to this ID. That 2nd table referring to ID 1, and contain a bad comment for it. Later you deleted that row from the first table, and reset the auto_increment to 1, and insert another data. This time the new row, also has an ID=1, but the bad comment is automatically referred to it although it wasn't your intention to do so. You might not see the problem, maybe for now, but when you database grow bigger and bigger, it will get worse, and very hard to debug.

[Eggie 0]

ALTER TABLE table_name AUTO_INCREMENT = 1;

that is what i need but thanks anyway
that sets your auto_increment value to 1!

[Eggie 0]

faulty.lee...i didn't even see your post...after i saw turbopowerdmaxsteel's post i leaved it here on the post and haven't refreshed...and i posted itthanx anyways

[faulty.lee 0]

It's OK. In fact I would be happy if you can figure it out yourself. That shows your enthusiasm on what you wanted to do.

[Eggie 0]

It's OK. In fact I would be happy if you can figure it out yourself. That shows your enthusiasm on what you wanted to do.

well..i didnt figure it out myself...i googled it ..thats how i figured it

[sparkx 0]

The fact that you even Googled it puts you in front of about half the programmer wanabies on this forum as it is. On my website I do the following to increment things:-Run While. If a constant added variable is equal to the ID it repeats the while. If failed found first "skipped" number.-If no skipped number + 1 to the last number.-Double Check if number exists (useless unless two entries and being inserted at the exact same time).-Insert entry.You probably wont understand what I just said until you get better at programming, but that’s how I check. I don't know if the code you found included skipped number or not but that is always nice to have unless you want deleted ID's to stay deleted. I made up this method and am not exactly sure how secure it is but it may be a good idea to do if you really get serious about your game.Good Luck,Sparkx

[Eggie 0]

good code but i dont want that a guy who registers 210th get id 2so that code is better in my case

[TavoxPeru 0]

Another way to reset your auto increment id field is by truncate the table so the ID of your first new inserted record will get as Id the value 1, TRUNCATE TABLE is a data manipulation statement that empties completely a table and also resets any auto_increment counter of the table, sounds like this is similar to perform a DELETE FROM TABLE statement, but it is not absolutely true, because it depends on the version of your database, also, if you perform the DELETE statement this will not reset the auto_increment counter of the table, so, if you insert a record to your table after this the auto_increment counter will be setting up with the next value.

 

But be carefull with this method, if you have data on your table and you want to preserve it, well, what i generally do in this case is to first delete the primary index of the table, then i delete the auto_increment field, after that i generate an sql file of the table by exporting it, then i proceed to delete the table and finally i import the generated sql file.

 

Sounds like there are too much tasks to perform a simple task like this, well, if you use MySql and phpMyAdmin it is very easy and simple, you simply go to the export tab of your table, select the options you need and press a button. There are a lot of options that you can select here, for this case the options i select are:

Export: Sql.

Structure: Add DROP TABLE, Add IF NOT EXISTS, Enclose table and field names with backquotes.

DATA: Complete inserts, Extended inserts.

Export type: INSERT

Save as file: This is optional because it depends on the size of the table, if the table have few records -less than 100- i do not use it.

Then, phpMyAdmin proceeds to generate the sql code, if i dont use the last option, it shows up the code on a textarea field, where i simply select all the generated sql code and copy it to the clipboard, or, in the other case, save the file to disk.

 

After that, if i use the Save as file option i go to the Import tab, open the FILE input control, browse my disk and select the just generated sql file, finally press the GO button. In the other case, i only need to go to the SQL tab of the table, and simply paste the just generated sql code inside the textarea field and finaly press the GO button.

 

Best regards,