inserting a NULL to MySql from SQL if statement

Question

I am assigning a variable a numeric value or writing a NULL to it with an if statement, if it's 0.00.

When writing to MySQL from my PHP page, I'm trying to pass this value and it always writes 0.00 for NULL. I'm guessing that's because my insert statement is sending .$price. and it sees it as being in quotes?

If I echo $price before the insert, it shows me that it's NULL but after the insert statement, it never remains.

How can I insert a NULL.. since I have to use a variable and send a value if there is one?

Michael Berkowski · Accepted Answer · 2011-08-22 20:26:20Z

2

To insert NULL into your table, you acutally need to pass the string "NULL" into your query, rather than the PHP NULL value. A PHP NULL, if enclosed in single-quotes and passed to the query will be interpreted as a 0.

// If $price is NULL in PHP, set it to the string "NULL"
// Otherwise, set it to the current value of $price, but enclosed in single quotes as '$price'
$price = $price === NULL ? "NULL" : "'$price'";

// Then $price gets passed to your query either as NULL or as the single-quoted $price
mysql_query("INSERT INTO tbl (price) VALUES ($price);");

Of course, you have already called $price = mysql_real_escape_string($price); before this...

answered Aug 22, 2011 at 20:26

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nanne · Accepted Answer · 2011-08-22 20:26:46Z

0

Hard to say as you don't post any definitions or code, but probably your table doesn't allow NULL, and so it defaults to the type's "zero" value.

answered Aug 22, 2011 at 20:26

Nanne

64.4k16 gold badges122 silver badges166 bronze badges

Collectives™ on Stack Overflow

inserting a NULL to MySql from SQL if statement

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related