Mixing
SQL Query not updating the database
SQL Query not updating the database
I was getting an error from my processing form down below. I'm not sure why it isn't updating to the database when I hit submit. It says it has been successful but yet the databse has not been updated?!
I think it maybe because of the "mysqli_stmt_execute" but I'm not sure.
Any thoughts :/
Thanks.
<?php
if(isset($_POST['submit'])){
include_once 'connection.php';
$first = mysqli_real_escape_string($conn, $_POST['first']);
$last = mysqli_real_escape_string($conn, $_POST['last']);
$uid = mysqli_real_escape_string($conn, $_POST['uid']);
$pwd = mysqli_real_escape_string($conn, $_POST['pwd']);
$hashedPwd = md5($pwd);
//Error Handling
//Checking for empty fields
if(empty($first) || empty($last) || empty($uid) || empty($pwd)){
header("Location: ../signup.php?signup=empty");
exit();
}else{
//Checking if the input characters are valid
if(!preg_match("/^[a-zA-Z]*$/", $first) || !preg_match("/^[a-zA-Z]*$/", $last) ){
header("Location: ../signup.php?signup=invalid");
exit();
}else{
$sql = "SELECT * FROM users WHERE Staff_ID='$uid'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0){
header("Location: ../signup.php?signup=usertaken");
exit();
}else{
//Hasing the password surecirty
}
}
}
//Insert user into database
$sql = "INSERT INTO users (Firstname, Lastname, Staff_ID, Password) VALUES (?, ?, ?, ?);";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)){
echo"SQL Error";
} else{
mysqli_stmt_bind_param($stmt, "ssss", $first, $last, $uid, $pwd);
mysqli_stmt_execute($stmt);
}
header("Location: ../signup.php?signup=success");
exit();
}else{
header("Location: ../signup.php");
exit();
}
?>
bind
execute
insert
md5
You shouldn't use
mysqli_real_escape_string() if you're using bind_param. That will cause double escaping of special characters.– Barmar
Jun 30 at 1:19
mysqli_real_escape_string()
bind_param
@user3783243 Sorry for asking, but I am a bit of a novice at php, how would I be able to check the result of bind and execute functions. Would I use echo? Would you be able to show me an example :)
– Bob Singh
Jun 30 at 1:51
I can not see any update queries there :)
– hassan
Jun 30 at 1:52
@hassan How would I be able to add an update query? :)
– Bob Singh
Jun 30 at 1:56
1 Answer
1
The PHP manual says
You should not add a terminating semicolon or g to the statement.
So this line
$sql = "INSERT INTO users (Firstname, Lastname, Staff_ID, Password) VALUES (?, ?, ?, ?);";
should certainly be
$sql = "INSERT INTO users (Firstname, Lastname, Staff_ID, Password) VALUES (?, ?, ?, ?)";
Try this code:
<?php
if(isset($_POST['submit'])){
include_once 'connection.php';
$first = $_POST['first'];
$last = $_POST['last'];
$uid = $_POST['uid'];
$pwd = $_POST['pwd'];
$hashedPwd = md5($pwd);
//Error Handling
//Checking for empty fields
if(empty($first) || empty($last) || empty($uid) || empty($pwd)){
header("Location: ../signup.php?signup=empty");
exit();
}else{
//Checking if the input characters are valid
if(!preg_match("/^[a-zA-Z]*$/", $first) || !preg_match("/^[a-zA-Z]*$/", $last) ){
header("Location: ../signup.php?signup=invalid");
exit();
}else{
$sql = "SELECT * FROM users WHERE Staff_ID='$uid'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);
if($resultCheck > 0){
header("Location: ../signup.php?signup=usertaken");
exit();
}else{
//Hasing the password surecirty
}
}
}
//Insert user into database
$sql = "INSERT INTO users (Firstname, Lastname, Staff_ID, Password) VALUES (?, ?, ?, ?)";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)){
echo"SQL Error";
} else{
mysqli_stmt_bind_param($stmt, "ssis", $first, $last, $uid, $pwd);
mysqli_stmt_execute($stmt);
}
header("Location: ../signup.php?signup=success");
exit();
}else{
header("Location: ../signup.php");
exit();
}
?>
That didn't seem to work :/. But thank you. :)
– Bob Singh
Jun 30 at 4:00
@BobSingh Try the code.
– Saral
Jun 30 at 4:08
Unfortunately the code you had given me didn't work. Thank you very much for helping me though. :) Have a great day! Its wierd how the code is running and it goes to the success page, but nothing is being added to the database.
– Bob Singh
Jun 30 at 5:01
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Check the result of the
bindandexecuteas well. Use the error reporting function. You should parameterize every query, not just theinsert. Dont escape when parameterizing. Additionally don't usemd5for hashing.– user3783243
Jun 30 at 1:16