INSERT NULL Values not working instead i get 0.000

Question

I'm trying to input a form into a database that uses numerical values (mainly decimals)

This form updates and inserts fine, but i want NULL fields to insert NULL into the database.

If I leave a field blank it still puts 0.000 into the database. I have the database set to NULL = YES

Is there a short piece of code i can use to do this for all 14 text boxes?

This is my code:

<?php
if (isset($_POST['submit'])) {

$date2 = date("y-m-d"); // DATE OF TANK ADDITION
$time2 = date("H:i:s", time() - 3600);   // TIME OF TANK ADDITION 

$test1 = $_POST['test1']; 
$test2 = $_POST['test2']; 
$test3 = $_POST['test3']; 
$test4 = $_POST['test4']; 
$test5 = $_POST['test5']; 
$test6 = $_POST['test6']; 
$test7 = $_POST['test7']; 
$test8 = $_POST['test8']; 
$test9 = $_POST['test9']; 
$test10 = $_POST['test10']; 
$test11 = $_POST['test11']; 
$test12 = $_POST['test12']; 
$test13 = $_POST['test13'];
$test14 = $_POST['test14'];
$time = $_POST['time'];
$date = $_POST['date'];
$month = $_POST['month'];
$day = $_POST['day'];
$active1 = $_POST['active1'];
$active2 = $_POST['active2'];
$active3 = $_POST['active3'];
$active4 = $_POST['active4'];
$active5 = $_POST['active5'];
$active6 = $_POST['active6'];
$active7 = $_POST['active7'];
$active8 = $_POST['active8'];
$active9 = $_POST['active9'];
$active10 = $_POST['active10'];
$active11 = $_POST['active11'];
$active12 = $_POST['active12'];
$active13 = $_POST['active13'];
$active14 = $_POST['active14'];
$tank = $_POST['tank'];


$insert = "INSERT INTO tests SET 
member_id='$_SESSION[SESS_MEMBER_ID]', 
test1='$test1', 
test2='$test2',
test3='$test3',
test4='$test4',
test5='$test5',
test6='$test6',
test7='$test7',
test8='$test8',
test9='$test9',
test10='$test10',
test11='$test11',
test12='$test12',
test13='$test13',
test14='$test14',
date='$date', 
month='$month', 
day='$day', 
time='$time',
active1='$active1',
active2='$active2',
active3='$active3',
active4='$active4',
active5='$active5',
active6='$active6',
active7='$active7',
active8='$active8',
active9='$active9',
active10='$active10',
active11='$active11',
active12='$active12',
active13='$active13',
active14='$active14',
tank_id='$tank'";

Blank is not null. `NULL` is null, and having the field completely absent from the `INSERT` statement is what triggers the default value. MySQL will "do its best" and try to convert your blank fields. — Sammitch, Feb 18 '14 at 01:23
You should seriously contemplate normalizing your database tables. — Ja͢ck, Feb 18 '14 at 01:29
Then you should try setting your column to `NOT NULL` @StephenJackson — Funk Forty Niner, Feb 18 '14 at 01:53

jeroen · Accepted Answer · 2014-02-18T02:28:24.707

5

When a field is empty or someone clears it on edit (I assume that is possible too), the value is an empty string.

So to make sure you insert NULL where there are empty strings, you can do (for all variables that can be NULL):

$test1 = ($_POST['test1'] === '') ? NULL : $_POST['test1'];

That will really set the value to NULL instead of an empty string that will be cast to 0 when you insert it.

And you should use prepared statements for your sql inserts.

Edit: Also note that when you try to insert your NULL value, you should be inserting just NULL and not 'NULL'. That problem would also be solved by switching to a prepared statement with bound variables.

Edit 2: A temporary solution that would work here, is to remove the quotes from the sql statement and add them in the assignment:

$test1 = ($_POST['test1'] === '') ? NULL : ("'" . (float) $_POST['test1'] . "'");
...

and:

$insert = "INSERT INTO tests SET 
member_id='$_SESSION[SESS_MEMBER_ID]', 
test1=$test, 
test2=$test2,
test3=$test3,
...

I have used (float) to at least get rid of the sql injection problem here (mysql_real_escape_string() might be better, don't know, don't use that any more...), but this is more of a hack than a real solution. Please switch to prepared statements with bound variables.

edited Feb 18 '14 at 02:28

answered Feb 18 '14 at 01:27

jeroen

91,079
21
114
132

1

@StephenJackson Everywhere where you initialize your variables, this `$test1 = ...` line replaces yours (line 7) and you should do that with all variables that can be `NULL`. – jeroen Feb 18 '14 at 01:42
i changed line 7 to your above code and i still get 0.000 in my table, default is set to NULL and null is set to YES in phpMyadmin – Stephen Jackson Feb 18 '14 at 01:48
@StephenJackson The default value does not matter when you set a value manually. For what column do you get 0.000, the `test1` column? You should really post your database structure. – jeroen Feb 18 '14 at 01:50
for now i need test1, 2 and 3 to be NULL if text field is left blank upon submit – Stephen Jackson Feb 18 '14 at 01:56
@StephenJackson Then you should use the example from my anwer to set these 3 variables. – jeroen Feb 18 '14 at 01:57
i did, but somehow still get the default 0.000 after the insert query? – Stephen Jackson Feb 18 '14 at 02:01
1

@StephenJackson You should really switch to prepared statements. The problem you have now, is that you are trying to insert the string `'NULL'` to the database while you should be adding `NULL` (without the single quotes). Just do a `var_dump($insert);` and you'll see what I mean. – jeroen Feb 18 '14 at 02:03
no idea how to set prepared statements... guess its time to learn thanks for the help though, appreciate it – Stephen Jackson Feb 18 '14 at 02:13
1

@StephenJackson You could fix / hack it by adding the quotes in the assignment if the value is not an empty string and removing them from the sql string but prepared statements would definitely be easier and get rid of the nasty sql injection problem you have now. – jeroen Feb 18 '14 at 02:18
how could i do that for now? then i need to revisit the prepared statements for the whole site and rewrite it more securely. – Stephen Jackson Feb 18 '14 at 02:22

score 2 · Answer 2 · edited May 23 '17 at 11:56

2

You have to leave the items you want NULL out of the query entirely.

Try something like:

foreach ($_POST as $k => $v)
{
    $query .= "{$k}='{$v}',";
}

You will have to make your commas work, etc.

WARNING:

Don't use this code in production, please. You should always sanitize user inputs, use parameterized SQL, and don't accept POST keys you are not expecting.

edited May 23 '17 at 11:56

Community

1
1

answered Feb 18 '14 at 01:19

willoller

7,106
1
35
63

score 1 · Answer 3 · answered Feb 18 '14 at 01:30

1

Do you have a DEFAULT NULL in your table's structure for the specific field? The table needs to know what to do with default values

CREATE TABLE IF NOT EXISTS MyTable (
  MyField datatype DEFAULT NULL,
);

If you want to update a table rather that re-create it, do this:

ALTER TABLE MyTable
ALTER COLUMN MyField datatype DEFAULT NULL

answered Feb 18 '14 at 01:30

OP didn't mention default values, merely the fact that the fields are nullable. – Ja͢ck Feb 18 '14 at 01:32

INSERT NULL Values not working instead i get 0.000

3 Answers3