Inserting null values to database using prepared statements PHP MYSQL

Question

Hi I am using prepared statements for the first time. I have a form whose values, i am inserting in Mysql database using Mysqli prepared statements. But the problem is if user leaves an input box empty, Query doesn't insert row to the database.

Form

<form action="test.php" method="post" class="signupform">
    <input type="text" Placeholder="Name" name="name" Required="required"/>
    <br />
    <input type="email" Placeholder="Email-id" name="email" Required="required"/>
    <br />
    <input type="password" Placeholder="Password" name="pass" Required="required"/>
    <br />
    <span>Male<input type="radio" name="sex" value="M" checked="checked"/>&nbsp;&nbsp;&nbsp;Female<input type="radio" name="sex" value="F"/></span>
    <br />
    <input type="text" Placeholder="City" name="city"/>
    <br /><br />
    <input type="submit" value="CREATE MY ACCOUNT" name="submit"/>
</form>

<?php

if(isset($_POST['submit'])){
    include_once('includes/db.php');
    $name=$_POST['name'];
    $pass=$_POST['pass'];
    $email=$_POST['email'];
    $sex=$_POST['sex'];
    $city = $_POST['city'];
    if ($stmt = $mysqli->prepare("INSERT INTO login VALUES('',?,?,?,?,?,'')")) {
        $stmt->bind_param("sssss", $name, $email, $pass, $sex, $city);
        $stmt->execute();
        if($stmt){
            echo "result inserted";
        }
    }
}

?>

On using above form and query when i fill all the boxes of form it insert a new row for me. But if i leave an input box empty, It doesn't insert any row.

I also have seen a lot of questions which says that if i use variables like this

if(empty($_POST['city'])) { $city = null; } else { $city = $_POST['city']; }

then it will work and most of them are accepted answers. I am confused why this solution is not working for me ???

Any help is appreciated...Thanks

Answer 1

Your query is wrong:

if ($stmt = $mysqli->prepare("INSERT INTO login VALUES('',?,?,?,?,?,'')")) {

It should be something like:

if (!empty($name) || !empty($pass) || !empty($email))
    {
        $stmt = $mysqli->prepare("INSERT INTO login(`name`,`password`,`email`,`sex`,`city`) VALUES(?,?,?,?,?)");
        $stmt->execute([$name, $pass, $email, $sex, $city]);
        echo "result inserted";

    } else {
        echo 'You have not entered all of the fields.';
    }

In this instance, if the variables are not empty then perform insert. Else if they are empty fire a echo stating the fields haven't been filled in.

If you are happy for the fields to be null simply change !empty() to empty() but as Fred -ii- stated above, ensure your database allows NULL within them fields.