Sql conditional insert (insert value if not null or insert null)

Question

I have a table (order). The form that is used to insert data in the table order contains some optionals fields. That's mean some fields can have data or be empty. I already set those fields "nullable" in the database.

I want to write a sql query, something like:

(if :data is not null, insert :data else insert null).

:data here is a value of field from the submitted form.

I already tried IFNULL, ISNULL but seems not working cause i got some errors like "×Cannot execute SQL statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near..."

How can i write such query ?

EDIT:

this a query from Modather Sadik

 insert into [table] (data) Values ((CASE WHEN @d IS NULL OR datalength(@d) = 0 THEN NULL ELSE @d END))

that query corresponds to what i want to achieve. I tried this:

INSERT INTO commande 
        VALUES (
               null,
               :date_ajout,
               CASE WHEN :etat_commande IS NULL OR datalength(:etat_commande) = 0 THEN NULL ELSE :etat_commande END,
               CASE WHEN :lieu_commande IS NULL OR datalength(:lieu_commande) = 0 THEN NULL ELSE :lieu_commande END,
               CASE WHEN :rappel IS NULL OR datalength(:rappel) = 0 THEN NULL ELSE :rappel END,
               CASE WHEN :avec_avance IS NULL OR datalength(:avec_avance) = 0 THEN NULL ELSE :avec_avance END,
               CASE WHEN :article_nom IS NULL OR datalength(:article_nom) = 0 THEN NULL ELSE :article_nom END,
               CASE WHEN :article_reference IS NULL OR datalength(:article_reference) = 0 THEN NULL ELSE :article_reference END,
               CASE WHEN :article_quantite IS NULL OR datalength(:article_quantite) = 0 THEN NULL ELSE :article_quantite END,
               CASE WHEN :article_prix IS NULL OR datalength(:article_prix) = 0 THEN NULL ELSE :article_prix END,
               CASE WHEN :article_description IS NULL OR datalength(:article_description) = 0 THEN NULL ELSE :article_description END,
               CASE WHEN :article_site_web IS NULL OR datalength(:article_site_web) = 0 THEN NULL ELSE :article_site_web END,
               CASE WHEN :article_image1 IS NULL OR datalength(:article_image1) = 0 THEN NULL ELSE :article_image1 END,
               CASE WHEN :article_image2 IS NULL OR datalength(:article_image2) = 0 THEN NULL ELSE :article_image2 END,
               CASE WHEN :id_transporteur IS NULL OR datalength(:id_transporteur) = 0 THEN NULL ELSE :id_transporteur END,
               :id_employe,
               CASE WHEN :id_client IS NULL OR datalength(:id_client) = 0 THEN NULL ELSE :id_client END,
               CASE WHEN :id_fournisseur IS NULL OR datalength(:id_fournisseur) = 0 THEN NULL ELSE :id_fournisseur END,
               CASE WHEN :date_depart_livraison IS NULL OR datalength(:date_depart_livraison) = 0 THEN NULL ELSE :date_depart_livraison END,
               CASE WHEN :date_livraison IS NULL OR datalength(:date_livraison) = 0 THEN NULL ELSE :date_livraison END,
               CASE WHEN :montant_avance IS NULL OR datalength(:montant_avance) = 0 THEN NULL ELSE :montant_avance END,
               CASE WHEN :mode_paiement IS NULL OR datalength(:mode_paiement) = 0 THEN NULL ELSE :mode_paiement END,
               CASE WHEN :etat_paiement IS NULL OR datalength(:etat_paiement) = 0 THEN NULL ELSE :etat_paiement END,
               CASE WHEN :prix_achat IS NULL OR datalength(:prix_achat) = 0 THEN NULL ELSE :prix_achat END,
               CASE WHEN :prix_de_vente IS NULL OR datalength(:prix_de_vente) = 0 THEN NULL ELSE :prix_de_vente END,
               CASE WHEN :montant_a_payer IS NULL OR datalength(:montant_a_payer) = 0 THEN NULL ELSE :montant_a_payer END,
               CASE WHEN :frais_livraison IS NULL OR datalength(:frais_livraison) = 0 THEN NULL ELSE :frais_livraison END,
               CASE WHEN :infos_frais IS NULL OR datalength(:infos_frais) = 0 THEN NULL ELSE :infos_frais END,
               CASE WHEN :benefice IS NULL OR datalength(:benefice) = 0 THEN NULL ELSE :benefice END
               );

but i have this error:

Cannot execute SQL statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':article_image1 IS NULL OR datalength(:article_image1) = 0 THEN NULL ELSE :artic' at line 15

Answer 1

This code:

 insert into [table] (data) 
     Values ((CASE WHEN @d IS NULL OR datalength(@d) = 0 THEN NULL ELSE @d END))

will check if the value is null or empty and if so, it will insert null otherwise it will insert the sent value.

for sure @d will be filled from your form as parameter.

Answer 2

Are you looking for an insert which will insert when data is not null in particular column?

insert into orders
select col1, col2, col3 
  from srctable
 where col3 is not null