Why when I sumbit a form only two column values are saved to the database?
I have been trying to solve this problem that I am encountering at the moment for a week now. So I hope I will find a solution here. I have an HTML file that containes a register form with 'POST' method. I intend to store user's information into the DB named 'pizza_project' using PHP file. However, everytime I submit the form only two values are stored : password and sort code (which are also hashed before being stored). Moreover, even though in my database it is said that no zero values and no default values are allowed to be stored, everytime I submit a form '0' is stored to the AccountNumber column. Other columns remain empty.
This is my HTML form:
<div class="reg_container">
<form id="form_register" class="register_section" action="http://localhost/pizza_register.php" method="post">
<div class="reg_form_item">
<div class="reg_form_itself">
<p class="h3">PERSONAL INFORMATION</p>
<label for="customer_name"></label>
<input type="text" id="customer_name" name="customer_name" placeholder="Full name" class="bigger_field" required>
<label for="customer_phone"></label>
<input type="tel" id="customer_phone" name="customer_phone" placeholder="Phone number" class="bigger_field" required>
<label for="customer_email"></label>
<input type="email" id="customer_email" name="customer_email" placeholder="Email address" class="bigger_field" required>
<label for="password"></label>
<input type="password" id="password" name="password" placeholder="Password" class="bigger_field" required>
</div>
</div>
<div class="reg_form_item"><div class="line"></div></div>
<div class="reg_form_item">
<div class="reg_form_itself">
<p class="h3">DELIVERY, PAYMENT</p>
<label for="full_address"></label>
<textarea id="full_address" name="full_address" rows="3" placeholder="Full address" class="input-field" required></textarea>
<label for="account_number"></label>
<input type="text" id="account_number" name="account_number" placeholder="Account number" class="bigger_field" required>
<label for="sort_code"></label>
<input type="password" id="sort_code" name="sort_code" placeholder="Sort Code" class="bigger_field" required>
<input class="input-submit" type="submit" name="submit" value="SAVE">
</div>
</div>
</form>
</div>
This is my PHP file:
<?php
$con = mysqli_connect("localhost","root","","pizza_project")
or die("Error " . mysqli_error($con));
$customer_name = mysqli_real_escape_string($con,$_POST["customer_name"]);
$customer_phone = mysqli_real_escape_string($con,$_POST["customer_phone"]);
$customer_email = mysqli_real_escape_string($con,$_POST["customer_email"]);
$password = mysqli_real_escape_string($con,$_POST["password"]);
$full_address = mysqli_real_escape_string($con,$_POST["full_address"]);
$account_number = mysqli_real_escape_string($con,$_POST["account_number"]);
$sort_code = mysqli_real_escape_string($con,$_POST["sort_code"]);
$hashed_password = password_hash($password, PASSWORD_DEFAULT);
$hashed_sort_code = password_hash($sort_code, PASSWORD_DEFAULT);
$sql = $con->prepare("INSERT INTO customers (CustomerName, CustomerPhone, CustomerEmail, hashed_password, FullAddress, AccountNumber, hashed_sort_code) VALUES (?, ?, ?, ?, ?, ?, ?)");
$sql->bind_param("sssssis", $customer_name, $customer_phone, $customer_email, $hashed_password, $full_address, $account_number, $hashed_sort_code);
if ($sql->execute()) {
header("Location: http://localhost/pizza_home_p.html");
exit();
} else {
error_log("Error: " . $sql->error);
echo "New record was not created successfully";
}
$sql->close();
mysqli_close($con);
This is how the data is stored to the DB
Thanks everyone for the help.
Answers
It seems like you're encountering a couple of issues with your form submission and PHP script. Let's address them one by one:
-
Not all form fields are being stored: From your description, it seems that only the
password
andsort_code
fields are being stored in the database. This could be due to either the form fields not being submitted correctly or the PHP script not processing them properly. -
Default value '0' being stored in AccountNumber column: The fact that '0' is being stored in the
AccountNumber
column even though it's not a default value suggests that the field might not be getting the expected input from the form.
Let's check your HTML form first. Everything seems fine there, but I noticed a small mistake in your form inputs:
<input type="password" id="sort_code" name="sort_code" placeholder="Sort Code" class="bigger_field" required>
The input type for the sort_code
field is set to password
, which should be changed to text
because password
inputs hide the characters entered. Since you're not displaying sensitive information like passwords, it's better to change it to text
:
<input type="text" id="sort_code" name="sort_code" placeholder="Sort Code" class="bigger_field" required>
Now, let's examine your PHP script. Everything seems fine in the PHP script, but there's a small optimization we can make in the INSERT
query preparation. Instead of using mysqli_real_escape_string
for each input, you can directly use parameterized queries, which are more secure and convenient. Here's how you can rewrite the script:
<?php
$con = mysqli_connect("localhost", "root", "", "pizza_project") or die("Error " . mysqli_error($con));
$customer_name = $_POST["customer_name"];
$customer_phone = $_POST["customer_phone"];
$customer_email = $_POST["customer_email"];
$password = $_POST["password"];
$full_address = $_POST["full_address"];
$account_number = $_POST["account_number"];
$sort_code = $_POST["sort_code"];
$hashed_password = password_hash($password, PASSWORD_DEFAULT);
$hashed_sort_code = password_hash($sort_code, PASSWORD_DEFAULT);
$sql = $con->prepare("INSERT INTO customers (CustomerName, CustomerPhone, CustomerEmail, hashed_password, FullAddress, AccountNumber, hashed_sort_code) VALUES (?, ?, ?, ?, ?, ?, ?)");
$sql->bind_param("sssssss", $customer_name, $customer_phone, $customer_email, $hashed_password, $full_address, $account_number, $hashed_sort_code);
if ($sql->execute()) {
header("Location: http://localhost/pizza_home_p.html");
exit();
} else {
error_log("Error: " . $sql->error);
echo "New record was not created successfully";
}
$sql->close();
mysqli_close($con);
?>
With these changes, your PHP script should now properly insert all the form data into the database without storing default values or zero values unexpectedly.