MySQL and PHP - insert NULL rather than empty string

Question

I have a MySQL statement that inserts some variables into the database  I recently added 2 fields which are optional   intLat   intLng    Right now  if these values are not entered I pass along an empty string as a value   How do I pass an explicit NULL value to MySQL  if empty     query    INSERT INTO data  notes  id  filesUploaded  lat  lng  intLat  intLng            VALUES    notes     id   TRIM   imageUploaded      lat     long                        intLat     intLng     mysql query  query

User · Answer

All you have to do is   variable  NULL     and pass it in the insert query  This will store the value as NULL in mysql db

User · Answer

you can do it for example with  UPDATE  table  SET  date       newdate  NULL WHERE id   id

User · Answer

If you don t pass values  you ll get nulls for defaults   But you can just pass the word NULL without quotes

User · Answer

To pass a NULL to MySQL  you do just that   INSERT INTO table  field field2  VALUES  NULL 3    So  in your code  check if  intLat   intLng are empty  if they are  use NULL instead of   intLat  or   intLng     intLat    empty  intLat       intLat      NULL    intLng    empty  intLng       intLng      NULL     query    INSERT INTO data  notes  id  filesUploaded  lat  lng  intLat  intLng            VALUES    notes     id   TRIM   imageUploaded      lat     long                       intLat   intLng

User · Answer

For some reason  radhoo s solution wouldn t work for me  When I used the following expression     query    INSERT INTO uradmonitor  db value1  db value2  VALUES            val1       NULL        val1                      val2       NULL        val2                         null   with quotes  was inserted instead of null without quotes  making it a string instead of an integer  So I finally tried     query    INSERT INTO uradmonitor  db value1  db value2  VALUES            val1              val1                      val2              val2                        The blank resulted in the correct null  unquoted  being inserted into the query

User · Answer

This works just fine for me   INSERT INTO table VALUES      NULLIF   date          first    increments id field

User · Answer

Normally  you add regular values to mySQL  from PHP  like this   function addValues  val1   val2        db open       just some code ot open the DB       query    INSERT INTO uradmonitor  db value1  db value2  VALUES    val1     val2          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  db value2  VALUES                val1       NULL        val1                          val2       NULL        val2                            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

User · Answer

Check the variables before building the query  if they are empty  change them to the string NULL

User · Answer

your query can go as follows    query    INSERT INTO data  notes  id  filesUploaded  lat  lng  intLat  intLng        VALUES    notes     id   TRIM   imageUploaded      lat     lng          lat        NULL  lat              long        NULL  long         mysql query  query

[php] MySQL and PHP - insert NULL rather than empty string

Examples related to php

Examples related to mysql

Examples related to null

Examples related to sql-insert