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

Question

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')";

Solution 1

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:

• http://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html

Solution 2

I prefer this syntaxis:

$query = "INSERT INTO myTable SET fname='Fname',lname='Lname',website='Website'";

Solution 3

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

Solution 4

This is phpMyAdmin method.

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

View more solutions

Comments

[JimmyJammed]

Yes I know it is better to set each column. But the table this is for will not be altered so I was just curious how to use the least amount of code to do the trick. Thanks!

[Alix Axel]

+1, I like it too, only works in MySQL / doesn't work in SQLite tho.

[scragar]

This is really hacky behaviour, and is relying on MySQL converting the empty string to an integer(which isn't strict behaviour), failing without an error(which is configurable) and then using null as it normally would(where in null becomes the equiv for DEFAULT). I really suggest not using this ever.

[Korvin Szanto]

This is an old one :P I've updated with the null instead of an empty string.

[GrandOpener]

"Will not be altered" is a pretty huge assumption. Maybe it's a one-off table and you're dropping it when you're done with this current task, but otherwise who knows when business requirements will change in unexpected ways. In the vast majority of cases, the person who has to maintain the system next will greatly appreciate it if you use the second form with the column names.

[GrandOpener]

If you're going to give a query from PHP, you should really make it a parameterized query.

[mickmackusa]

If you have dynamic fed values that need to be used in your query, yes, use a prepared statement. If all values being inserted are static (and don't contain any sql-breaking characters), then you can just use a basic query().