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"; ?><?phpif(!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
Advertisement
Answer
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();}