PHP mySQL - Insert new record into table with auto-increment on primary key

50
votes

Wondering if there is a shorthand version to insert a new record into a table that has the primary key enabled? (i.e. not having to include the key column in the query) Lets say the key column is called ID, and the other columns are Fname, Lname, and Website

$query = "INSERT INTO myTable VALUES ('Fname', 'Lname', 'Website')";
5
phpmysqlinsertauto-increment
you've got the answer right there. Your example should work as expected (aasuming ID is an autoincrement). – konsolenfreddy
@konsolenfreddy: He's using the insert sytnax that requires every column value to be specified. – webbiedave
I recommend against this type of insert sytnax in applications because if you add columns in the future (or change column order), you must come back and change your SQL statements, even if those columns have default values. If it's a one time DB update, then it's no big deal. – webbiedave

5 Answers

108
votes

Use the DEFAULT keyword:

$query = "INSERT INTO myTable VALUES (DEFAULT,'Fname', 'Lname', 'Website')";

Also, you can specify the columns, (which is better practice):

$query = "INSERT INTO myTable
          (fname, lname, website)
          VALUES
          ('fname', 'lname', 'website')";

Reference:

12
votes

I prefer this syntaxis:

$query = "INSERT INTO myTable SET fname='Fname',lname='Lname',website='Website'";
5
votes
$query = "INSERT INTO myTable VALUES (NULL,'Fname', 'Lname', 'Website')";

Just leaving the value of the AI primary key NULL will assign an auto incremented value.

3
votes

This is phpMyAdmin method.

$query = "INSERT INTO myTable
         (mtb_i_idautoinc, mtb_s_string1, mtb_s_string2) 
         VALUES
         (NULL, 'Jagodina', '35000')";
0
votes

You can also use blank single quotes for the auto_increment column. something like this. It worked for me.

$query = "INSERT INTO myTable VALUES ('','Fname', 'Lname', 'Website')";