Why is MySQL query not updating the data?-CodePudding

The data in the form is not updating when I try to execute the button. There is no response when I try to submit the data.

Here is the html code.

<?php

             $sql = "SELECT * FROM users";
           $query = $conn->query($sql);
           $row = $query->fetch_array();
    
            ?>
<form action="account_edit.php" method="POST" enctype="multipart/form-data">
<div class="form-group">
<label for="exampleInputEmail1">Fullname</label>
<input type="text" name="user_fullname"  value="<?php echo $row['user_fullname']; ?>" class="form-control" id="exampleInputEmail1" placeholder="" required>
</div>
<div class="form-group">
<label for="exampleInputEmail1">Email</label>
<input type="email" name="user_email"  value="<?php echo $row['user_email']; ?>" class="form-control" id="exampleInputEmail1" placeholder="" required>
</div>
<div class="form-group">
<label for="exampleInputPassword1">Password</label>
                                        
<input type="password" name="password"  value="" class="form-control" id="exampleInputEmail1" placeholder="" required>
</div>

<input type="submit" name="submit" value="Update" class="btn btn-success">
</form>
<button data-dismiss="modal" class="btn btn-primary" type="button">Close</button>

Here is the php code for updating the data.

if(isset($_POST['submit']))
{
$user_fullname = $_POST['user_fullname'];
$user_email = $_POST['user_email'];
$password = md5($_POST['password']);


$sql="UPDATE users SET user_fullname=?, user_email=?, password=? WHERE user_fullname=?, user_email=?, password=?";
$stmt=$conn->prepare($sql);
$stmt->bind_param("sss", $user_fullname, $user_email, $password);
if($stmt->execute()){
  echo '<script>alert("Information has been updated")</script>';
  header('location:index.php');
}


}

Honestly I don't know where it go wrong, can you guys help me? When I click the update button it does not do anything like the echo that the data has been updated or update the data in the database.

CodePudding user response：

You should know the unique indentifier of the user you are updating first, but now let's figure why that isn't updating.

We suppose you have in your database

user_fullname: John Smith
user_email: [email protected]
user_password: testingtestin123

When you are updating the name, you send in POST the new value.

$user_fullname = $_POST['user_fullname'];

Now the variable $user_fullname has the value "James Smith", because that's what we sent on the form.

So, when you are preparing the query, you prompt to the server to update a record which has "James Smith" as full name, because in the WHERE statement you are matching the new name. So obviously no query is updated.

That's why you should have the unique identifier. Or at least the old email! If you choose this option, you can send the old email in the form with a hidden input, for example:

<input type="hidden" name="old_email" value="<?php echo $oldMail; ?>" />

So that in the query you can update the account linked to the old email with the new information.

$sql="UPDATE users SET user_fullname=?, user_email=?, password=? WHERE 
user_email=?";
$stmt=$conn->prepare($sql);
$stmt->bind_param("ssss", $user_fullname, $user_email, $password, $old_email);
$stmt->execute();

Remember that the user_email must be linked to ONLY one user, otherwise all the accounts with that email will be updated.

An advice: do not use md5 for password cryptography, it is not very safe actually. Use at least sha256, or better you can read the documentation related to the password_hash() function.

Andrea

CodePudding user response：

In your first code sample you do SELECT * FROM users then fetch_array(). That gets you all the users in the table, in some unpredictable order. Ensure your page gets rendered correctly.

In your second code sample you do

UPDATE users SET user_fullname=?, user_email=?, password=?
 WHERE user_fullname=?, user_email=?, password=?;

This does not adhere to SQL syntax. To make it adhere, you want

UPDATE users SET user_fullname=?, user_email=?, password=?
 WHERE user_fullname=? AND user_email=? AND password=?;

Once you make it syntactically correct, you'll see that it serves very little purpose. It updates the columns to the same values they already have. If no row matches your WHERE condition this updates no rows. You may want INSERT for handling new rows in the table.

Follow @ADyson's suggestion about setting up php for debugging.

Use code like this to debug your SQL.

$result = $stmt->execute();
if (!$result) {   /* TODO improve this error handling */
     printf("%s: error message: %s\n", $sql, $conn->error)
}
if ($result) {
   /* handle your successful run */
}

This is cheezy oversimplified error handling good for getting your code working. As @ADyson pointed out in a comment, this kind of error handling can deliver ugly, and possibly insecure, messages to end users if it fires in production. For production it's smarter to tell the user "ooops, something went wrong" and log the error to the server's error log file. Like this maybe.

$result = $stmt->execute();
if (!$result) { 
     $message = sprintf("%s: error message: %s\n", $sql, $conn->error);
     error_log ($message, 0);
     die ("Sorry, something went wrong.");
}
if ($result) {
   /* handle your successful run */
}

Do not, repeat not, use a simple hash function like MD5 to store users' passwords. Because cybercreeeps. php has really good password-hashing capabilities.