CODEWITHSUNDEEP
phpmysql
user1551120
user1551120

Reputation: 627

MYSQL and PHP INSERT NULL values

lets say my table only has these members - id (key) and date (which defaults to NULL) . Now when I want to insert a row with my php , do I need to check before my query whether date has a value or not or can I just insert like so - 

 $query = "INSERT INTO mytable VALUES(3,{$_GET['date]})"

And mysql would assign a NULL value to date ?

And does this hold true to a table no matter how large ?

e.g : can I insert many values that come from php and may be empty(or null) to a table , and mysql would automatically assign NULL to them (if I defined them as NULL by default of course) or do I need to do all kinds of checks before my inserts?

Upvotes: 1

Views: 2262

Answers (5)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

No, it will assign the value passed with the parameter $_GET['date']. If the value is empty '' and the date was of data type varchar it will insert a value of '' which is different than NULL then it will insert an empty. Thats because NULL and empty strings are not equal. They are two different things in SQL.

If you want to insert NULL values, either ignore this column in the insert columns list, then it will assigned with the default value which is NULL. Or write it explicitly in the values of the INSERT statement.

Note that: Your code this way is vulnerable to SQL injection. You should use prepared statements or PDO instead. See this for more details:

Upvotes: 1

Akhilraj N S
Akhilraj N S

Reputation: 9497

$date = date("d-m-Y",strtotime($_GET['date']));
if(isset($date))
    $query = "INSERT INTO mytable VALUES(3,$date)";

Upvotes: 0

deceze
deceze

Reputation: 522597

  1. That snippet is wide open to SQL injection. Escape your values or use prepared statements!
  2. If the variable contains nothing, the query will look like ... VALUES (3,). That's a syntax error, so it doesn't work.
  3. If you'd change this to VALUES(3, '{$_GET['date']}')", so in the case of an empty variable the query would be ... VALUES(3, ''), an empty string is the value. That will be cast to some sane value for the column in question, in case of a date column, to an unrecognized date. That's not NULL.

NULL only applies if you omit the column entirely from a query or explicitly set it to NULL. In any other case, the value will be something other than NULL.

Upvotes: 0

sephoy08
sephoy08

Reputation: 1114

Try this

 $query = "INSERT INTO mytable VALUES(3,'".$_GET['date']."')";

But also consider the datatype of your table field if its set to date/datetime then, check the return value of $_GET['date'] it must also in form of date.

Upvotes: 0

Martin
Martin

Reputation: 1508

This might be relevant and this would also make sure you are not vulnerable to sql injection attacks.

I'd say to just check each variable personally, then you have way more control over your variables before they are getting put in your database.

Upvotes: 1

Related Questions