set auto increment initial value for mysql table

I’m trying to create a table in my sql using PHP but I’m not sure how to set an initial value for the auto increment field.

This is what i have so far:

function create_table($db_host,$db_user,$db_pswrd,$db_name){

$connect = mysql_connect($db_host,$db_user,$db_pswrd) or die(mysql_error());

mysql_select_db($db_name, $connect);
 $sql = "CREATE TABLE MY_TABLE
 (
table_id int NOT NULL AUTO_INCREMENT, 
 PRIMARY KEY(table_id),
 table_1 varchar(45),
 table_2 varchar(45),
 table_3 varchar(999),
 table_4 varchar(45)
 )"or die(mysql_error());

 mysql_query($sql,$connect)or die(mysql_error());

mysql_close($connect);

}

So i need to know how to set the initial Auto Increment value on this table, upon creation?

Thanks

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

If you don’t have the auto-increment column in the table yet:

$sql = "ALTER TABLE MY_TABLE ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD INDEX (id);";

Then to set the auto-increment starting value:

$sql = "ALTER TABLE MY_TABLE AUTO_INCREMENT = 111111;";

Potential duplicate of this post.

Method 2

Assuming your auto increment column is the first one:

 $sql = "CREATE TABLE MY_TABLE
 (
 table_1 INT AUTO_INCREMENT,
 table_2 varchar(45),
 table_3 varchar(999),
 table_4 varchar(45)
 ) AUTO_INCREMENT = 231";

The starting value will be, here, 231.

I changed the column type to INT, because you can’t use a VARCHAR for auto-increment.

(and remove the or die(mysql_error()) on this line btw, its pointless because it’s just a variable creation, not a SQL query being executed)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0