How to insert NULL into integer column in mysql if the POST is empty using php

Question

This is the original coding. I have tried so many method from previous posting in stackoverflow but none of them works. My column for father, mother , husband and wife are in the integer type.

$db = mysqli_connect('x', 'xx', 'xxx', 'xxxx');

if (isset($_POST['add_member'])) {

  // receive all input values from the form
  $username = mysqli_real_escape_string($db, $_POST['username']);
  $sex = mysqli_real_escape_string($db, $_POST['sex']);
  $father = mysqli_real_escape_string($db, $_POST['father']) == "" ? null : (int)mysqli_real_escape_string($db, $_POST['father']);
  $mother = mysqli_real_escape_string($db, $_POST['mother']) == "" ? null : (int)mysqli_real_escape_string($db, $_POST['mother']);
  $husband = mysqli_real_escape_string($db, $_POST['husband']) == "" ? null : (int)mysqli_real_escape_string($db, $_POST['husband']);
  $wife = mysqli_real_escape_string($db, $_POST['wife']) == "" ? NULL : (int)mysqli_real_escape_string($db, $_POST['wife']);
  $family = mysqli_real_escape_string($db, $_POST['family']) == "" ? null : (int)mysqli_real_escape_string($db, $_POST['family']);
    
    $query = "INSERT INTO users (username, gender, father_id, mother_id, husband_id, wife_id, family_id) VALUES ('$username', '$sex', '$father', '$mother', '$husband', '$wife', '$family')";
    mysqli_query($db, $query);
    header('location: index.php');
  }
}

Here are some methods I used but still not work

 $husband =  mysqli_real_escape_string($db, $_POST['husband']);
 $husband = !empty($husband_contact) ? "'$husband'" : NULL;

Another method:

if (empty($_POST['husband'])){ $husband= NULL; 
}else{ $husband =  mysqli_real_escape_string($db, $_POST['husband']);}

Another method (I am not sure about this because it donot have $db, is it even possible?):

 if ($_POST['husband'] == '') {
 $husband = NULL;
 }else {
  $husband = "'" . mysqli_real_escape_string($_POST['husband']) . "'";
 }

and another method (same with this, no $db)

$husband = !empty($_POST['husband']) ? "'".$mysqli->real_escape_string($_POST['husband'])."'" : NULL;

Try `echo $query;` and it should be immediately clear why it is not doing what you want. You are creating an empty string to insert, not null. You should look at using prepared statements instead of building up your query from strings containing variables. https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php — user1191247, Dec 26 '21 at 18:56
If you use prepared statemets, you wouldn´t have this problema and you will avoid sql injections. Right now, you are trying to insert the string `null` in an int column. You are not inserting a null value — nacho, Dec 26 '21 at 18:59
Use prepared statements and bind the values. Don't use `mysqli_real_escape_string` — Dharman, Dec 26 '21 at 19:03

score 0 · Answer 1 · answered Dec 26 '21 at 19:07

Firstly, be sure your table's columns accept null values.

I am not sure this is the correct and secure way to insert data into database. You should try the PDO binding method.

try {

  $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

  // set the PDO error mode to exception

  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  $sql = "INSERT INTO users (username, sex, father, mother, husband, wife, family)

  VALUES (?,?,?,?,?,?,?)";

  $conn->bind_param($username, $sex, $father, $mother, $husband, $wife, $family);

  // use exec() because no results are returned

  $conn->exec($sql);

  echo "New record created successfully";

} catch(PDOException $e) {

  echo $sql . "<br>" . $e->getMessage();

}

$conn = null;

?>

How to insert NULL into integer column in mysql if the POST is empty using php

1 Answers1