51

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')";
JimmyJammed
  • 9,598
  • 19
  • 79
  • 146
  • you've got the answer right there. Your example should work as expected (aasuming ID is an autoincrement). – konsolenfreddy Sep 20 '11 at 21:45
  • 1
    @konsolenfreddy: He's using the insert sytnax that requires every column value to be specified. – webbiedave Sep 20 '11 at 22:15
  • 2
    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 Sep 20 '11 at 22:17

5 Answers5

112

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:

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
regality
  • 6,496
  • 6
  • 29
  • 26
  • 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! – JimmyJammed Sep 20 '11 at 22:39
  • 5
    "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 Jul 09 '14 at 15:03
12

I prefer this syntaxis:

$query = "INSERT INTO myTable SET fname='Fname',lname='Lname',website='Website'";
jenkin90
  • 357
  • 1
  • 5
6
$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.

Korvin Szanto
  • 4,531
  • 4
  • 19
  • 49
  • 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. – scragar Jun 27 '14 at 12:34
  • 2
    This is an old one :P I've updated with the null instead of an empty string. – Korvin Szanto Jul 01 '14 at 17:29
3

This is phpMyAdmin method.

$query = "INSERT INTO myTable
         (mtb_i_idautoinc, mtb_s_string1, mtb_s_string2) 
         VALUES
         (NULL, 'Jagodina', '35000')";
minimal
  • 457
  • 3
  • 15
  • 1
    If you're going to give a query from PHP, you should really make it a parameterized query. – GrandOpener Jul 09 '14 at 15:03
  • 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()`. – mickmackusa Nov 25 '21 at 05:30
0

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')";
Rahul Bharati
  • 952
  • 1
  • 7
  • 16