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

make sure the db can have NULL values. Use a ternary operator with a default value; too many ways to do this — Funk Forty Niner, Feb 23 '17 at 14:55
The POST value arn't send to the server if they are null or empty, that's why you need to check for them to be empty or null and set a default value. — Nicolas, Feb 23 '17 at 15:00
**Never store plain text passwords!** Please use PHP's [built-in functions](http://jayblanchard.net/proper_password_hashing_with_PHP.html) to handle password security. If you're using a PHP version less than 5.5 you can use the `password_hash()` [compatibility pack](https://github.com/ircmaxell/password_compat). Make sure you ***[don't escape passwords](http://stackoverflow.com/q/36628418/1011527)*** or use any other cleansing mechanism on them before hashing. Doing so *changes* the password and causes unnecessary additional coding. — Jay Blanchard, Feb 23 '17 at 15:01
`('',?,?,?,?,?,'')` those 2 `''` may be failing here, so it's unsure which columns those are being indexed as. Check for errors on the query `mysqli_error($mysqli)` in the `else`. — Funk Forty Niner, Feb 23 '17 at 15:07
Sidenote: you're forcing people to ask for their gender having one radio button "checked" and would be forced to choose the other if they're female. Some may not want to divulge that information, *just saying.* — Funk Forty Niner, Feb 23 '17 at 15:11
You have a few answers below, see those. Your silence isn't helping, unless you left the question only to return at a later date. — Funk Forty Niner, Feb 23 '17 at 15:11
@Fred-ii- looking at the OP's profile you can tell that he have a tendency of leaving the questions he asks like that, as you can see he's been quite while we busy commenting tryingto help — Masivuye Cokile, Feb 23 '17 at 15:19
@MasivuyeCokile I kind of had that feeling earlier. They probably think that that's what the "cool kids" do; well they're wrong *lol* — Funk Forty Niner, Feb 23 '17 at 15:20
@MasivuyeCokile actually, they seem to only respond to YCS http://stackoverflow.com/q/42221826/ maybe they think he's God or something and we are mere peasants. — Funk Forty Niner, Feb 23 '17 at 15:24
lol... everyone replies to him @Fred-ii- The guy is good though — Masivuye Cokile, Feb 23 '17 at 15:26
@fred well YCS is the only one to answer over 1000 PDO questions etc and seems to link his blog in every single answer ^_^ (note the sarcasm here) — Option, Feb 23 '17 at 15:31
@Option *hehe*, noted ;-) Maybe he gets a penny for every visit. — Funk Forty Niner, Feb 23 '17 at 15:33
@Fred, I am suprised he hasn't diverted it through one of those links that pay per visit haha! — Option, Feb 23 '17 at 15:41
@Fred-ii- I am very sorry that i couldn't reply yesterday as one of my best friend met with an accident...so... — Ashish, Feb 24 '17 at 06:09
@JayBlanchard I don't store plain passwords to the database...It was just for the question that i have written .plain.... — Ashish, Feb 24 '17 at 06:11
@Fred-ii- As you are suggesting i will remove required property from radio buttons...And DB table can have null values in that field... — Ashish, Feb 24 '17 at 06:17

Option · Accepted Answer · 2017-02-23T15:06:54.610

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.

edited Feb 23 '17 at 15:06

answered Feb 23 '17 at 14:59

Option

2,605
2
19
29

Sure, but this field `` seems to not be "required", so `!empty($city)` may not be needed. I suggested a ternary operator. I guess they'll have to choose how they want to handle the form/inputs. Plus, the radio buttons also don't seem to be "required". The OP will need to decide what they want to do. – Funk Forty Niner Feb 23 '17 at 15:05
Very true there thanks for pointing it out. I'll adjust accordingly so it's more accurate. – Option Feb 23 '17 at 15:06

Masivuye Cokile · Answer 2 · 2017-02-23T15:08:11.787

Probably this is not one of the smartest way to do it, but hey, it will get the job done.

One of the things that you need to do before assigning a variable to an $_POST field, you need to check if that $_POST field isset and its not empty, then assign the value if not empty, Currently if someone leaves out a field in your form when you run the query you will probably get a notice of undefined.

This is what you can do.

<?php

if (isset($_POST['submit'])) {
    include_once('includes/db.php');


    if (!empty($_POST['name'])) {

        $name = $_POST['name'];
    } else {

        $name = " ";
    }

    if (!empty($_POST['pass'])) {

        $pass = $_POST['pass'];
    } else {

        $pass = " ";
    }

    if (!empty($_POST['email'])) {

        $email = $_POST['email'];
    } else {

        $email = " ";
    }

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

        $sex = $_POST['sex'];
    } else {

        $sex = " ";
    }

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

        $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";
        } else {

            echo "could not insert";
        }
    }
}

?>

There are other better ways to do this.

Radio buttons don't use `empty()`, they use `isset()`, bit of a sidenote ;-) — Funk Forty Niner, Feb 23 '17 at 15:06

Inserting null values to database using prepared statements PHP MYSQL

2 Answers2