How to handle nulls in this PHP/mysql insert statement

Question

I sometimes have arrays with null or empty values. In MySql they are set up to be non-nullable, but they have default values assigned. Why then, does MySql give me an error, e.g. Column user_type can not be null. (I'm not running in strict mode).

I realise that I could use the keyword DEFAULT in place of some values when preparing the query, but I don't really want to do that. I would like to be able to write my SQL statements verbatim, rather than put them together with foreach loops, etc. For example, I would like to use "INSERT INTO users (user_type, first_name, last_name, password) VALUES (:user_type, :first_name, :last_name, :password)";

As far as I recollect, this was working fine (i.e. substituting in the correct defaults) until I moved from using ? query markers to named parameters...

Thanks...

Answer 1

I would create a function that accepts the values as parameters. It would have the default values in an associative array. If the value for any of the parameters is null, it would replace it with the default.

eg

function setUpQuery($user_type_in, $first_name_in, $last_name_in, $password_in){
       $default_values('user_type' => 'Admin', 'first_name' => 'John', 'last_name' => 'Doe', 'password' => 'XXX');
       $user_type = ($user_type_in == NULL)? $default_values['user_type']:$user_type_in;
       .....
      return "INSERT INTO users (user_type, first_name, last_name, password) VALUES ('$user_type', '$first_name', '$last_name', '$password');"
 }

Good Point. How about the following:

INSERT INTO users(user_type, first_name, last_name,password) values 
(ifnull('$user_type',default(user_type)), ifnull('$first_name', default(first_name)),
 ifnull('$last_name',default(last_name)), ifnull('$password', default(password));