Database

MySQL and PHP – insert NULL rather than empty string

Normally, you add regular values to mySQL, from PHP like this:

function addValues($val1, $val2) {
    db_open(); // just some code to open the DB 
    $query = "INSERT INTO uradmonitor (db_value1) VALUES ('$val1')";
    $result = mysql_query($query);
    db_close(); // just some code to close the DB
}

When your values are empty/null ($val1==”” or $val1==NULL), and you want NULL to be added to SQL and not 0 or empty string, to the following:

function addValues($val1, $val2) {
    db_open(); // just some code ot open the DB 
    $query = "INSERT INTO uradmonitor (db_value1) 
              VALUES (".(($val1=='')?"NULL":("'".$val1."'")).")";
    $result = mysql_query($query);
    db_close(); // just some code to close the DB
}

Note that null must be added as “NULL” and not as “‘NULL'” . The non-null values must be added as “‘”.$val1.”‘”, etc.

Hope this helps, I just had to use this for some hardware data loggers, some of them collecting temperature and radiation, others only radiation. For those without the temperature sensor I needed NULL and not 0, for obvious reasons ( 0 is an accepted temperature value also).

SOURCE: stackoverflow.com

Leave a Reply