How can I use MySQL NOT NULL with PHP?

Question

I've form to insert values in MySQL database with PHP (Example code is below):

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
<style>
label{text-align:left;
       width:150px;
       display:block;
           float:left;
           clear:right;
       font-size:18;}

form {
    width: 30%;
    margin: 0 auto;
}
</style>
<label>Name:</label><input name=name autocomplete=off><br>
<label>Date of Birth:</label><input size=2 name=dd placeholder=dd> / <input size=2 name=mm placeholder=mm> / <input size=6 name=yyyy placeholder=yyyy><br>
<label>Time of Birth:</label><input name=tob placeholder="hh : mm : ss"><br>
<label>City:</label><input name=city><br><br>
<center><input type=submit value=Save></center>
</form>

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST"){
$servername = "localhost";
$username = "userame";
$password = "password";
$dbname = "myDB2";
if(empty($_POST["name"])){ echo "Name is Required<br>";}
else {
$name = $_POST["name"];
}
var_dump($name);
$tob = $_POST["tob"];
$city = $_POST["city"];
$dd = $_POST["dd"];
$mm = $_POST["mm"];
$yyyy = $_POST["yyyy"];

$dob = "$yyyy-$mm-$dd";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

//Inserting Data
$sql = "INSERT INTO Person
VALUES('$name','$dob','$tob','$city')";
if ($conn->query($sql) === TRUE) {
    echo "<br>New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
}
?>

AND The COLUMN Name is set to varchar(60) NOT NULL UNIQUE data-type & constraint.

---

In-spite of

• NOT NULL constraint for Name Column, and

• if(empty($_POST["name"])){ echo "Name is Required";} else { $name = $_POST["name"]; }

When I inserts submit form without entering any value in Name Input in the form, I get following message (according to code)

Name is Required
NULL
New record created successfully

And My Database output like:

Here you can see that var_dump($name) says $name is NULL BUT Still MySQL Database accepting this value for Name Column!

---

So, My question is What is the use of NOT NULL or How can I use MysQL NOT NULL with PHP? for inserting data with the help for form?

Answer 1

You need to translate PHP's null into SQL's NULL type appropriately. When you do simple string interpolation in PHP:

"INSERT INTO foo VALUES ('$bar')"

where $bar is null, the result ends up as:

INSERT INTO foo VALUES ('')

MySQL sees that as an empty string, which is not NULL, so it's fine.

You have to do something like this:

$value = null;
if ($value === null) {
    $bar = 'NULL';
} else {
    $bar = "'$value'";
}
$query = "INSERT INTO foo VALUES ($bar)";

Here the result is one of these two:

INSERT INTO foo VALUES (NULL)
INSERT INTO foo VALUES ('baz')

Then MySQL will see the null as NULL.

However, if you're going to such lengths in PHP, you can obviously simply not fire the query if your values are empty, instead of firing the query and letting MySQL fail due to constraints.

You further need to learn about SQL injection.

Answer 2

An empty string is not NULL. What you're inserting in your table is an empty string:

$sql = "INSERT INTO Person VALUES('$name','$dob','$tob','$city')";

if $name is NULL in PHP, this would just result in a string like: INSERT INTO Person VALUES('','...','...','...')

If you want to pass NULL to MySQL, you have to send NULL, not "NULL" and not "".

$nameForMysql = is_null($name) ? "NULL" : "'$name'";
$sql = "INSERT INTO Person VALUES($nameForMysql,'$dob','$tob','$city')";

Answer 3

So just make your if...else more wide:

if(!isset($_POST["name"]) || empty($_POST["name"])){ echo "Name is Required<br>";}
else {
$name = $_POST["name"];
$tob = $_POST["tob"];
$city = $_POST["city"];
$dd = $_POST["dd"];
$mm = $_POST["mm"];
$yyyy = $_POST["yyyy"];

$dob = "$yyyy-$mm-$dd";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

//Inserting Data
$sql = "INSERT INTO Person
VALUES('$name','$dob','$tob','$city')";
if ($conn->query($sql) === TRUE) {
    echo "<br>New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
}

Answer 4

Specify NULL where there is no value, sql is treating your data as empty string not null so Use the code below

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
<style>
label{text-align:left;
       width:150px;
       display:block;
           float:left;
           clear:right;
       font-size:18;}

form {
    width: 30%;
    margin: 0 auto;
}
</style>
<label>Name:</label><input name=name autocomplete=off><br>
<label>Date of Birth:</label><input size=2 name=dd placeholder=dd> / <input size=2 name=mm placeholder=mm> / <input size=6 name=yyyy placeholder=yyyy><br>
<label>Time of Birth:</label><input name=tob placeholder="hh : mm : ss"><br>
<label>City:</label><input name=city><br><br>
<center><input type=submit value=Save></center>
</form>

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST"){
$servername = "localhost";
$username = "userame";
$password = "password";
$dbname = "myDB2";
if(empty($_POST["name"])){ echo "Name is Required<br>";
$name = "NULL";
}
else {
$name = $_POST["name"];
}
var_dump($name);
$tob = $_POST["tob"];
$city = $_POST["city"];
$dd = $_POST["dd"];
$mm = $_POST["mm"];
$yyyy = $_POST["yyyy"];

$dob = "$yyyy-$mm-$dd";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

//Inserting Data
$sql = "INSERT INTO Person
VALUES('$name','$dob','$tob','$city')";
if ($conn->query($sql) === TRUE) {
    echo "<br>New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
}
?>

Hope this helps you