CODEWITHSUNDEEP
phpmysqlupdates
jerneva
jerneva

Reputation: 465

MYSQL - Update database creates new record each time

Each time i update the database, it create a new row with the new information i was trying to update and a new customerID each time, is there a way to resolve this.

The update query calls two tables Cus_acct_details and cus_register. The query is meant to change cus_email in both tables, and update all the information in cus_acct_details.

PHP

<?php

//$user = $_SESSION["Cus_Email"];
$Cust_ID = $_SESSION["CustomerID"];
if (isset($_POST['Update'])) {
    $UpdateFname = $_POST['fname'];
    $UpdateLname = $_POST['Lname'];
    $UpdateEmail = $_POST['email'];
    $UpdatePhone = $_POST['phone'];
}
$sql = $dbc->query("UPDATE Cus_Register, Cus_acc_details 
                       SET Cus_acc_details.CUS_Fname = ' $UpdateFname', 
                           Cus_acc_details.CUS_Lname = ' $UpdateLname', 
                           Cus_acc_details.CUS_Email = ' $UpdateEmail', 
                           Cus_acc_details.Cus_Phone = ' $UpdatePhone', 
                           Cus_Register.CUS_Email = ' $UpdateEmail', 
                        ON Cus_Register.Cus_Email = Cus_acc_details.Cus_Email 
                     WHERE Cus_Register.CustomerID = '$Cust_ID' 
                  ");
print_r($_POST);
header('Location: Cus_Account.php');
?>

HTML

<section class="container">
    <form id="myform " class="Form" method="post" action="Cus_Account.php?c_id=<?php echo $c_id ?>" accept-charset="utf-8">

        <!--                    <div id="first">-->
        <input type="text" id="fname" name="fname" value="<?php echo $_SESSION['fname']; ?>" required> 
        <input type="text" id="lname" name="lname" value="<?php echo $_SESSION['lname']; ?>"  required>
        <input type="text" id="email" name="email" value="<?php echo $_SESSION['Cus_Email']; ?>" required>
        <input type="number" id="phone" name="phone"  value="<?php echo $_SESSION['phone']; ?>"  required>
        <input type="submit" name="Update" value="Update">
        <br>
    </form>

The $cust_id variable was defined earlier on.

Where have a gone wrong.

Upvotes: 1

Views: 1657

Answers (2)

spencer7593
spencer7593

Reputation: 108420

An UPDATE statement won't insert a new row. There must be an INSERT statement running. (1)

The syntax of the update statement looks wrong to me, I'd expect that to be throwing an error.

The ON clause is used with the JOIN keyword, but the old-school comma operator is used for the join operation. The SET clause should be the last thing before the WHERE clause.

 UPDATE Cus_Register
   JOIN Cus_acc_details 
     ON Cus_Register.Cus_Email = Cus_acc_details.Cus_Email
    SET Cus_acc_details.CUS_Fname = ?
      , Cus_acc_details.CUS_Lname = ?
      , Cus_acc_details.CUS_Email = ?
      , Cus_acc_details.Cus_Phone = ?
      , Cus_Register.CUS_Email = ?
  WHERE Cus_Register.CustomerID = ?

It seems odd that there's an extra space in the string literals.

Assigning the return from a ->query() to a variable is a common pattern. But naming that variable $sql is very strange.

The normative pattern is to assign the SQL text (a string) to a variable named $sql, and then referencing the variable 

 $sql = 'SELECT foo FROM bar ORDER BY foo LIMIT 1';
 $result = $dbc->query($sql);

Then check the return from query, to see if it was successful, or if an error occurred. If you're using PDO, you can configure the connection to throw an exception, and handle it in a catch block.

If your code doesn't do that, it's putting it's pinky finger to the corner of its mouth Dr. Evil style and saying "I'm just going to assume it all goes to plan. What?"

Also, the code appears to be vulnerable to SQL Injection. If any potentially unsafe values are included in the SQL text, those values must be properly escaped before they are included.

The preferred pattern is not even include the values in the SQL text, but to use prepared statements with bind placeholders, and supply the values through the placeholders.

https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

(1.) Of course it's possible to define a BEFORE UPDATE and/or an AFTER UPDATE trigger that performs an INSERT. But it's the INSERT statement that inserts the row, even if the firing of the trigger is "caused" by running an UPDATE.

Upvotes: 1

James Dewes
James Dewes

Reputation: 387

Set CustomerID to be a key and add an ON DUPLIACTE KEY UPDATE clause

Upvotes: 0

Related Questions