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...

I don't think this can be done this way: a value is being sent when bound (because it appears at all!), even if NULL. Thus MySQL cannot apply the default value -- because a value was specified. There are triggers, but "ick!". One "solution" would be to have a minimal query-generator based off say, a Map. (Still using placeholders, of course.) — user166390
– user166390, Commented Jun 4, 2012 at 6:32
Are you talking about php? Because you mentioned about "Array", probably worth mentioning Array in which language — Andreas Wong
– Andreas Wong, Commented Jun 4, 2012 at 6:32
(I don't think it was working with just using ? markers with the same insert structure; might want to verify that and, if it does work, see by what magic.) — user166390
– user166390, Commented Jun 4, 2012 at 6:37
possible duplicate of pdo prepared statement insert DEFAULT when the variable in BindParam is null. I tried: IFNULL or COALESCE — lanzz
– lanzz, Commented Jun 4, 2012 at 6:53

hgolov · Accepted Answer · 2012-06-04 07:15:45Z

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));

edited Jun 4, 2012 at 7:15

answered Jun 4, 2012 at 6:50

hgolov

7221 gold badge12 silver badges29 bronze badges

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

3 Comments

user166390 Over a year ago

This has the severe disadvantage of not using the DB default values.

DatsunBing Over a year ago

@hgolov, I was hoping to create the SQL statements (and their PDO counterparts) statically and not dynamically because they are static members of a hierarchical mapper class. I do a check such as "if !isset($this->_PDOInsert) {...} The default() function approach won't work because the query's parameter types vary with each usage, and are only set once (when the static member is set).

hgolov Over a year ago

I see that you marked this as solved. Did it work in your pdo code?

Collectives™ on Stack Overflow

How to handle nulls in this PHP/mysql insert statement

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related