Set initial auto increment value for mysql table

Question

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

This is what I still have:

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 value of Auto Increment in this table when creating?

thanks

Answer 1

If you do not already have an auto-increment column in the table:

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

Then, to set the initial auto-increment value:

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

---

Potential duplicate of this post .

Answer 2

Assuming your auto-increment column is the first:

  $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 231 here.

I changed the column type to INT because you cannot use VARCHAR for auto-increment.

(and delete or die(mysql_error()) in this btw line, this is pointless because it is just creating a variable and not an SQL query being executed)