PHP set a default value for database insert

Question

I have a number box <input type='number' step='any' name='number' id='number'>

and I take that value and call it in a function like so:

insertIntoDB($number)

function insertIntoDB($number = null){

  //insert into db
  "INSERT INTO 'tablename' (number) VALUES " . $number;

}

and I get this error:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '

Is there away so if the user leaves the field blank it will enter the value as null?

That isn't even close to valid PHP code. Plus you have unnecessary quotes around your table name. — John Conde
– John Conde, Commented May 22, 2014 at 13:52
Wh you will enter a record without informations? or has the table more informations? If ou want to insert null just write it into $number — Jens
– Jens, Commented May 22, 2014 at 13:54

Fluffeh · Accepted Answer · 2014-05-22 13:59:32Z

You can set default values when creating the table itself, but apart from that, the code you pasted is missing some brackets:

INSERT INTO `tablename` (number) VALUES (" . $number.")";

If you want to use a NULL when inserting and get a default value in the table, you can do it when creating the table like this:

CREATE TABLE test 
    (
    id INT NOT NULL AUTO_INCREMENT, 
    someField VARCHAR(15) NOT NULL DEFAULT 'on'
    )

Now when you try to insert data into it, you can do the following:

insert into `tablename` (id, someField) values (null, null)

and get a row with an incremented ID and the string 'on' in the column someField.

If you want to check for nulls when inserting data into a table, you can then use something like the following to ensure you have the value:

$someField=(isset($number))?$number:'null';
INSERT INTO `tablename` (number) VALUES (" . $someField.")";

This is assuming you have already verified that $number is either numeric or isn't submitted when sending the form.

1 Answer 1