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
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(); }