Passing NULL from PHP to MySQL for auto increment

Question

So I have a database setup in MySQL with three columns. The first column is 'idnum' that auto increments the users id numbers. The second and third are first and last names respectfully. My problem is when I go to send the the names to the DB via a query string in my PHP file, I get a couple different errors back...

When I send the query:

$sql = "insert into namesdb values('NULL', 'firstname', 'lastname')";
$result = $db->query($sql);

I get back this error: "Incorrect integer value: 'NULL' for column 'idnum' at row 1." Because column 1 is an INT type.

But then when I send this query:

$sql = "insert into namesdb values(".NULL.", 'firstname', 'lastname')";
$result = $db->query($sql);

I get back a syntax error...

Any idea on what the heck I'm doing wrong here??

Thank you for any help!

Answer 1

$sql = "insert into namesdb values('NULL', 'firstname', 'lastname')";

In the above query 'NULL' is a string object and your column is an Integer so the error.

$sql = "insert into namesdb values(".NULL.", 'firstname', 'lastname')";

In this query you are sending php NULL value so the final query looks like the following

"insert into namesdb values(, 'firstname', 'lastname')";

So it is invalid.

The correct way to insert should be like this

$sql = "insert into namesdb values(NULL, 'firstname', 'lastname')";

or like this

$sql = "insert into namesdb values('firstname', 'lastname')";

The reason above query works is because of the auto increment.

Answer 2

It should be:

$sql = "insert into namesdb values(NULL, 'firstname', 'lastname')";
$result = $db->query($sql);

'NULL' is a string of "NULL".

Though another option (the one I would go with) is to list the columns explicitly:

INSERT INTO namesdb (firstname, lastname) VALUES ('firstname', 'lastname')

I prefer listing the columns because it is more future proof, and it's easier to see what's going on. Imagine if columns are rearranged, added, or removed in the future. Suddenly fixing your queries is going to be a massive pain if you have to remove the 6th unnamed parameter everywhere (for example).

Answer 3

Its better specify field names which you want to insert and dont specify id field

like

       insert into namesdb(firstname,lastname) values('firstname', 'lastname')

It will auto increment your id field

Answer 4

You can write query this way to avoid that problem..

 $sql = "INSERT INTO table_name SET column_name_1 = 'value_1', column_name_2 = 'value_2'";