PHP为数据库插入设置了默认值

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?

Answer 1

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.