PHP MySQL Insert null values

Question

I m struggling with some PHP MySQL code   I am reading from 1 table  changing some fields then writing to another table  nothing happens if inserting and one of the array values is null when I would like it to insert null in the database  null values are allowed for the field    It looks a bit like this    results   mysql query  select   from mytable    while   row   mysql fetch assoc  results        mysql query  insert into table2  f1  f2  values     row  string field        row  null field          Not every row has a null value and in my query there are more fields and 2 columns which may or may not be null

User · Answer

I think you need quotes around your   row  null field     so    row  null field      If you don t have the quotes  you ll occasionally end up with an insert statement that looks like this  insert into table2  f1  f2  values   val1    which is a syntax error   If that is a numeric field  you will have to do some testing above it  and if there is no value in null field  explicitly set it to null

User · Answer

This is one example where using prepared statements really saves you some trouble   In MySQL  in order to insert a null value  you must specify it at INSERT time or leave the field out which requires additional branching   INSERT INTO table2  f1  f2    VALUES   String Value   NULL     However  if you want to insert a value in that field  you must now branch your code to add the single quotes   INSERT INTO table2  f1  f2    VALUES   String Value    String Value      Prepared statements automatically do that for you  They know the difference between string 0     and null and write your query appropriately    stmt    mysqli- gt prepare  INSERT INTO table2  f1  f2  VALUES            stmt- gt bind param  ss    field1   field2     field1    String Value    field2   null    stmt- gt execute      It escapes your fields for you  makes sure that you don t forget  to bind a parameter  There is no reason to stay with the mysql extension  Use mysqli and it s prepared statements instead  You ll save yourself a world of pain

User · Answer

For fields where NULL is acceptable  you could use var export  var  true  to output the string  integer  or NULL literal   Note that you would not surround the output with quotes because they will be automatically added or omitted    For example   mysql query  insert into table2  f1  f2  values     row  string field        var export  row  null field    true

[php] PHP/MySQL Insert null values

Examples related to php

Examples related to mysql

Examples related to insert

Examples related to null