MySQL NULL or usser input

Question

This code is for inserting data in to my tables.

     $sku = $conn->real_escape_string($_REQUEST['sku']);
    $name = $conn->real_escape_string($_REQUEST['name']);
    $price =  $conn->real_escape_string($_REQUEST['price']);
    $type =  $conn->real_escape_string($_REQUEST['type']);
    $size =  $conn->real_escape_string($_REQUEST['size']);
    $bWeight =  $conn->real_escape_string($_REQUEST['bWeight']);
    $fHeight =  $conn->real_escape_string($_REQUEST['fHeight']);
    $fWeight =  $conn->real_escape_string($_REQUEST['fWeight']);
    $fLenght =  $conn->real_escape_string($_REQUEST['fLenght']);


    //Insert Into mainInfo
    $sql = "INSERT INTO mainInfo (sku, name, price, type) VALUES ('$sku', 
    '$name', '$price', '$type')";
    if($conn->query($sql) === TRUE){
      echo "Records inserted Successfully <br> ";
    } else {
      echo "Error couldnt insert records: " .$conn->error;
    }


    //Insert Into properties
    $sql = "INSERT INTO properties (size, bWeight, fHeight, fWeight, fLenght, 
    sku) VALUES ('$size', '$bWeight', '$fHeight', '$fWeight', '$fLenght', 
    '$sku')";
    if($conn->query($sql) === TRUE){
      echo "Records inserted Successfully <br> ";
    } else {
      echo "Error couldnt insert records: " .$conn->error;
    }

Here is the HTML code, if that is neccesary.

     <form class="prodInf" method="post" action="pehape/apstrade.php" >

<div class="container-fluid">
  <div class="row">
    <div class="col-md-4">
        <label class="labelName" for="prodSKU"><b>SKU</b> </label>
        <input type="text" name="sku">
          <br>
        <label class ="labelName"for="prodName"><b>Name</b> </label>
        <input type="text" name="name">
          <br>
        <label class ="labelName"for="prodPrice"><b>Price</b> </label>
        <input type="text" name="price">
          <br>
        <label id="lastProdInf" class ="labelName"for="prodType">Type 
      Switcher</label>
        <select class="prodctType" name="type">
          <option value="1" selected="selected">DVD-Disc</option>
          <option value="2">Book</option>
          <option value="3">Furniture</option>
        </select>

     </div>
   </div>
 </div>

  <br>

  <!--type apraksts un ievade -->
 <div class="container-fluid">
  <div class="row">
    <div class="col-md-4">
      <div class="inputDisplay">

        <!--DvD logs -->
        <div class="inputDvd">

            <label class="typePar" for="size"><b>Size</b> </label>
            <input type="text" name="size">

          <p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed 
     gravida fringilla sem, at lobortis lorem pulvinar et.</p>
        </div>

        <!--book logs -->
        <div class="inputBook">

            <label class="typePar" for="bWeight"><b>Weight</b></label>
            <input type="text" name="bWeight">

          <p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed 
     gravida fringilla sem, at lobortis lorem pulvinar et.</p>
        </div>

        <!--furniture logs -->
        <div class="inputFurniture">

            <label class="typePar" for="fHeight"><b>Height</b> </label>
            <input type="text" name="fHeight">
              <br>
            <label class="typePar" for="fWeight"><b>Weight</b> </label>
            <input type="text" name="fWeight">
              <br>
            <label class="typePar" for="fLenght"><b>Lenght</b></label>
             <input type="text" name="fLenght">

          <p>Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed 
    gravida fringilla sem, at lobortis lorem pulvinar et.</p>
        </div>

        </div>
      </div>
    </div>
  </div>

  <input type="submit" name="save" value="submit">
</form>

If user doesn't input data in some of the fields, how can i leave them empty, right now this code gives me. Error couldnt insert records: Incorrect integer value: '' for column 'bWeight' at row 1 after i input data only in $size

Answer 1

first, ensure that null values are allowed in your database schema. Then, you should not use real_escape_string() for integers because this function always returns a string (see the doc).

You can use filter_var() to ensure the provided value is an integer or force a cast to int with $value = (int) $userInput.

Also note that it is better to use prepared statements everywhere instead of real_escape_string