4

The first column in a MySQL table of mine is the primary key with auto increment. I am trying to add a new row with PHP like this.

mysql_query("INSERT INTO users VALUES ('', '$username', '$hashedPassword')");

What should I put in '' or the first field? It is auto generated, but I don't want the field to be empty.

Interestingly, making the field empty works, but I am afraid if this breaks in certain circumstances I have not encountered. What is the standard entity to put there?

Also, does your solution also apply to timestamp fields that default to the current time?

I am new to PHP and MySQL. Thanks.

John Hoffman
  • 17,857
  • 20
  • 58
  • 81
  • empty\nada\blank is the way to go. –  Mar 22 '12 at 20:26
  • possible duplicate of [PHP MYSQL - Insert into without using column names but with autoincrement field](http://stackoverflow.com/questions/1871331/php-mysql-insert-into-without-using-column-names-but-with-autoincrement-field) – j08691 Mar 22 '12 at 20:28

6 Answers6

2

INSERT INTO users ('username', 'hashedpassword') VALUES ('$username', '$hashedPassword')

You don't need to reference the auto generated id field as it will be handled by MySQL.

Alex Lockwood
  • 83,063
  • 39
  • 206
  • 250
Sam French
  • 705
  • 1
  • 5
  • 10
1

You can use NULL.

But better is to name the columns explicitly and just omit that column.

INSERT INTO users (username, hashedPassword)
VALUES ('$username', '$hashedPassword')

From the MySQL manual:

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign NULL or 0 to the column to generate sequence numbers.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
1

If it's auto-increment column, then don't specify anything. You should also explicitly list all columns that you are going to insert. Try this:

INSERT INTO users (username, password) 
VALUES ('$username', '$hashedPassword');
Sergio Tulentsev
  • 226,338
  • 43
  • 373
  • 367
1

The purpose of auto_increment is not to have to put anything there. You MUST keep it NULL or DEFAULT, or do not included it as a vlue you are inserting/updating. It's handeled internally by MySQL

Ben Ashton
  • 1,385
  • 10
  • 15
1

It's good to get into the habit of specifying your fields like this

INSERT INTO users (username,password) VALUES ( '$username', '$hashedPassword')

Or if you want to specify the id field too use NULL

INSERT INTO users (id,username,password) VALUES (NULL, '$username', '$hashedPassword')

By specifying the fields, your code is less likely to break if, for example, you add an extra field to the table.

liquorvicar
  • 6,081
  • 1
  • 16
  • 21
1

As others have suggested, specify which columns you're going to add data to. Once you do that, you may omit the primary key column, and the DBMS will take care of it for you.

Dot NET
  • 4,891
  • 13
  • 55
  • 98