Geek asks and answers

How to handle null values โ€‹โ€‹in this php / mysql insert statement

I sometimes have arrays with null or empty values. In MySql, they are set to non-empty values, but have default values โ€‹โ€‹assigned. Why then, MySql gives me an error, for example. Column user_type can not be null. (I do not work in strict mode).

I understand that in preparing the request, I could use the DEFAULT keyword instead of some values, but I really do not want to do this. I would like to be able to write my SQL statements verbatim, rather than combining them 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 remember, this worked fine (i.e., substituting the correct default values) until I switch from using? request markers for the specified parameters ...

Thank...

+5
source share
1 answer

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

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));
+1
source

More articles:


All Articles