7

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

rlemon
  • 17,518
  • 14
  • 92
  • 123
  • This is a SQL related question, not PHP. – Matthieu Napoli Sep 26 '11 at 16:02
  • Which column is auto increment? – Matthieu Napoli Sep 26 '11 at 16:03
  • I need to know how to do it, in PHP....... –  Sep 26 '11 at 16:03
  • None, on this table. But that, i already know how to do. I just need to know how to set the initial value. –  Sep 26 '11 at 16:04
  • 2
    @Nav - It's still an SQL question, even if the SQL query is executed in a PHP script. – James Allardice Sep 26 '11 at 16:05
  • @Nav Well why do you want to set the auto increment value for this table then (if it has no auto-increment column)? Give us the SQL schema of your other table. – Matthieu Napoli Sep 26 '11 at 16:09
  • @Matthieu I have several functions (similar to the one above). The function I displayed in the question will not contain an AUTO INCREMENT column but the others will... The reason for why I did not post the function that does have the Auto_increment is because it is far too long (creating loads of tables) so it would take too long to edit (change table names and stuff). Why is it such a big deal anyway.... –  Sep 26 '11 at 16:16
  • @JamesAllardice I know its still an SQL question, thats why mysql is used in the tags.. But its also PHP related, so therefore I've also used the PHP tag. end of the day, it will just make it easier for others to find the question and get the answer... –  Sep 26 '11 at 16:19
  • @Nav it's not really a big deal, but your question ask for something that makes no sense (set auto increment value on a table that has no primary key/integer column and no auto increment, yes that *is* what you are asking), and that is out of context (you ask for a SQL query by tagging it/making it a PHP problem : title, tags, description...). – Matthieu Napoli Sep 26 '11 at 16:22
  • Ok, well if it gives you the excitement, I've done edits for you...xD –  Sep 26 '11 at 16:29
  • why do you have `)"or die(mysql_error());` at the end of your query string? you are just building a straight string there. – rlemon Sep 26 '11 at 16:30
  • To be honest, No idea. I got the code from the web a while ago, and just edited it. Didn't know that should be there. –  Sep 26 '11 at 16:33
  • Alter table can be slow. See here for a better answer: http://stackoverflow.com/questions/4124838/slow-auto-increment-reset – B T Aug 03 '12 at 21:15

2 Answers2

20

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.

Community
  • 1
  • 1
Lucas
  • 875
  • 7
  • 15
  • 1
    I read here (http://tycoontalk.freelancer.com/php-forum/32507-reset-auto-increment-in-mysql.html) that the value of the auto-incriment can be as low as you want and mysql will find the next available id (ie it won't fail if you set auto-incriment lower than the higest id) – B T Aug 03 '12 at 21:00
  • Setting the auto_increment value fails if any `id` value greater than the requested value exists. – Sebastian Jul 11 '16 at 13:37
4

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)

Matthieu Napoli
  • 48,448
  • 45
  • 173
  • 261
  • 1
    The code you suggested returns an error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 231 )' at line 10 –  Sep 26 '11 at 16:57
  • Your corrected code still produces an error, you can't auto increment on a non key. – Eric Leschinski Aug 18 '14 at 14:45