Set IF statements to write something or not, to a column in MySQL

Question

Is there a way to set an IF statement in MySQL or PHP to write something to a column or not? What I want to do is completely skip a column so that "NULL" is written to it, in case there is no data to be submitted in the column.

In case nothing is to be submitted to the database in one column, I want it to be NULL for that record obviously. Right now, in each column where there is no data, it's just empty.

I could ofcourse set a completely new variable with a new query and all, and just skip that column in the query, but in my case, it'd be tons of more (probably) unnecessary code.

Answer 1

You can implement conditional logic in SQL using the CASE statement, e.g.

UPDATE table SET column=CASE WHEN @param='' THEN NULL ELSE @param END WHERE ...

Answer 2

Alright then, you wanted information, so here we go...

It looks like this now:

$query = "INSERT INTO requests (Ex1, Ex2, Ex3, Ex4, Ex5, Ex6) VALUES ('".$Ex1."', '".$Ex2."', '".$Ex3."', '".$Ex4."', '".$Ex5."', '".$Ex6."')";

And I want to insert something to "Ex3" in case there is data to insert, otherwise, I basically don't even want to include the column or the data to be submitted to that column in the query.

Answer 3

To insert null don't add quotes around inserted variables.

INSERT INTO table (col1, col2) VALUES ($val1, $val2);

A quick bit of php can add quotes:

foreach($_POST as $key=>$value) {
    if($vaule == '') $_POST[$key] = 'NULL';
    else $_POST[$key] = "'".$value."'";
}

But you should be checking and validating data on it's way anyway, so usually the above php is too simple. But yea, leave the quotes off, only add them to actual values. You could set a function up to validate, clean and add quotes to all your data prior to using it.