Inserting NULL if fields are blank

[SalientAnimal]

Hi All,

 

I have a bit of a problem inserting a NULL value into my table.

 

I have three field that I join and insert into one column. However, if all three fields are empty I need it to insert a null value and not 0.

 

Please could someone help, here is what I have so far:

 

First doing some sanitisation :

    // SANITIZE AND VALIDATE THE DATA BEING PROCESSED BY THE FORM
$user_id = filter_input(INPUT_POST, 'user_id', FILTER_SANITIZE_STRING); 
$username = filter_input(INPUT_POST, 'username', FILTER_SANITIZE_STRING); 
$sales_reference = filter_input(INPUT_POST, 'sales_reference', FILTER_SANITIZE_STRING);
$msisdn1 = !empty($msisdn1) ? "'$msisdn1'" : NULL; 
$msisdn2 = !empty($msisdn2) ? "'$msisdn2'" : NULL; 
$msisdn3 = !empty($msisdn3) ? "'$msisdn3'" : NULL; 
$msisdn = !empty($msisdn1 . " " .$msisdn2 . " " . $msisdn3) ? "'$msisdn'" : NULL; 
$identity = filter_input(INPUT_POST, 'identity', FILTER_SANITIZE_STRING); 
$sale_type = filter_input(INPUT_POST, 'sale_type', FILTER_SANITIZE_STRING);

Then the INSERT Statements:

 if ($insert_stmt = $mysqli->prepare("
INSERT INTO 
usr_retentions_sales
(
user_id
, username
,   sales_reference
,   msisdn
, identity
,   sale_type
) 
VALUES (?, ?, ?, ?, ?, ?)"))
{
$insert_stmt->bind_param(
'issiii'
,  $user_id
, $username
,  $sales_reference
,  $msisdn
, $identity
,  $sale_type
);

 

[NotionCommotion]

The second $msisdn isn't defined.  Even if it were, this looks a bit odd.

$msisdn = !empty($msisdn1 . " " .$msisdn2 . " " . $msisdn3) ? "'$msisdn'" : NULL; 

The number of values you are binding doesn't match the number of placeholders.

[Barand]

Why the double sets of quotes?

 

"'$msisdn1'"