Why Inserting a value in a nullable field from a HTML FORM gives me a NULL even if it is not?

Question

I created a database with a table that contains both mandatory and nullable fields. When I insert mandatory fields everyhing works fine, but when I try to insert a value in a field that is optional and can be NULL i always get a NULL result when checking the database. Here's the code: (user_id is mandatory and username is optional. Whether I type something in username or not I always get a NULL in the corresponding db field.

<html>
<head>
<title>Users</title>
</head>
<body>
<h1>Users input:</h1> <br/>
<FORM ID="users_input" NAME="users_input" METHOD="POST"
ACTION="get_users.php">
<LABEL>User_ID:
<INPUT TYPE="INT" NAME="user_id" ID="user_id"/>
</LABEL>
<LABEL>User name:
<INPUT TYPE="INT" NAME="username" ID="username"/>
</LABEL>
<INPUT TYPE="SUBMIT" NAME="Submit" ID="Submit" Value="Submit" />
</FORM>
</body>
</html>

**get_users.php**

  <?php include "conn.php"; ?>
<?php
if(!empty($_POST['username']))
{
$username=$_POST['username'];
$query="INSERT INTO users (username)
VALUES('".$_POST["username"]."')";
mysql_query($query,$conn);}
else
{
$username=NULL;
}

if (!empty($_POST['user_id']))
{
$user_id=$_POST['user_id'];
$query="INSERT INTO users(user_id) VALUES('".$_POST["user_id"]."')";
mysql_query($query,$conn);

}
else
{ echo "error";

}

?>

Thank you for your help

Can you provide some example cases of `username` being filled and not being filled and the result you're getting? — Vasilis G., Jun 05 '21 at 19:31
Welcome to stack exchange. A couple things. Input fields do not have a `type="int"` - if you want to enforce a number, you can `type="number"` - but you might not want numeric usernames. Also, no need to capitalize your HTML tags — Kinglish, Jun 05 '21 at 19:34
2 more things. Consider using `mysqli` rather than `mysql`. There are many reasons to do this, one of which is `mysql` is no longer supported and may go away in a future php release. AND, it's important to protect your database from attacks, so you really should start using prepared statements. It's just good (and necessary) practice: https://www.w3schools.com/php/php_mysql_prepared_statements.asp — Kinglish, Jun 05 '21 at 19:36
The (original) `mysql` extension is already not supported since PHP 7.0. Basically there is no officially supported PHP version that would still have it. https://www.php.net/manual/en/function.mysql-connect.php — Zoli Szabó, Jun 05 '21 at 19:41
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Jun 05 '21 at 19:51
**Warning:** `mysql_*` extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the [mysqli](https://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](https://www.php.net/manual/en/book.pdo.php) extension should be used. See also the [MySQL API Overview](https://www.php.net/manual/en/mysqlinfo.api.choosing.php) for further help while choosing a MySQL API. — Dharman, Jun 05 '21 at 19:51

score 0 · Accepted Answer · answered Jun 05 '21 at 22:09

Your code is performing two INSERT queries. eEach INSERT will create a new row, but the query that inserts only the username will fail because the user_id is set NOT NULL. Hence. you see one rwo with just the user ID and no user name.

You need to insert both fields in one INSERT, so, in the context you ask the question do this (but see below):

if (!empty($_POST['user_id']))
{
  $username = empty($_POST['username'])?null:$_POST['username'];
  $user_id=$_POST['user_id'];
  $query="INSERT INTO users(user_id,username) VALUES('$user_id','$username')";
  mysql_query($query,$conn) or die(mysql_error($conn));
}
else
{
 echo "error";
}

Important: The solution above is vulnerable to SQL injection. mysql_*() is also an archaic interface to MySQL - it was removed from PHP7 some years ago. You'd do better using PDO which also allows you to use prepared statements. The PDO version of this is:

<?php
/**
 * get_users.php
 *
 */

$host = 'localhost';
$dbname = 'dbname';
$dbuser = 'dbuser';
$dbpass = 'dbpass';

$conn = new PDO("mysql:dbname=$dbname;host=$dbhost", $dbuser, $dbpass);
// Set PDO to throw an exception on error
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try {
    if (!empty($_POST['user_id'])) {
        $username = empty($_POST['username']) ? null : $_POST['username'];
        $user_id = $_POST['user_id'];

        // Query with placeholders instead of values
        $query = "INSERT INTO users(user_id,username) VALUES(:userid,:username)";

        // Prepare query
        $stmt = $conn->prepare($query);

        // Execute query with array of values to use in place of placeholders
        $stmt->execute([':userid' => $user_id, ':username' => $username]);

    } else {
        echo "error: no user ID";
    }
    echo 'User added successfully';
} catch (Exception $e) {
    echo $e->getMessage();
}

Why Inserting a value in a nullable field from a HTML FORM gives me a NULL even if it is not?

1 Answers1