MYSQL - ON DUPLICATE KEY does not update

Question

I having been having the worst luck with updating and inserting into a database. My insert into a database works perfectly, i just cannot update.

I have tired two seperate queries one for update the other insert, but updating never works. I am now using the ON DUPLICATE KEY trigger and once again the information just adds a record each time instead of updating. Where am i going wrong? I have been working on this for a day and half now and can't seem to get anywhere. newbee problems!

  <?php
   $Cust_ID = $_SESSION["CustomerID"];
   if (isset($_POST['Update'])) {
      $c_fname = $_POST['fname'];
      $c_lname = $_POST['lname'];
      $c_email = $_POST['email'];
      $c_phone = $_POST['phone'];

  // Save $_POST to $_SESSION
  //query

   $insert_det = "INSERT INTO  Cus_acc_details(CUS_Fname,CUS_Lname,Cus_Email,CUS_Phone) 
    VALUES (?,?,?,?)
    ON DUPLICATE KEY 
    UPDATE
    CUS_Fname = '$c_fname',
    Cus_Lname = '$c_lname'";

$stmt = mysqli_prepare($dbc, $insert_det);
//new
// $stmt = mysqli_prepare($dbc, $insert_c);
//debugging
//$stmt = mysqli_prepare($dbc, $insert_c)  or die(mysqli_error($dbc));

mysqli_stmt_bind_param($stmt, 'sssi', $c_fname, $c_lname, $c_email, $c_phone);

/* execute query */
$r = mysqli_stmt_execute($stmt);

// if inserted echo the following messges
if ($r) {
    echo "<script> alert('registration sucessful')</script>";
}
} else {
echo "<b>Oops! Your passwords do not </b>";
}
?>

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>

both the Customer_id and Cus-_email field's are set as unique on the bd, however, records keep inserting into the db. any help would be appreciated.

SHOW CREATE

CREATE TABLE `Cus_acc_details` (
`CustomerID` tinyint(11) NOT NULL AUTO_INCREMENT,
`AcctId` varchar(100) NOT NULL,
`CUS_Fname` varchar(45) DEFAULT NULL,
`Cus_Lname` varchar(45) DEFAULT NULL,
`CUS_Phone` varchar(45) NOT NULL,
`Cus_Email` varchar(200) NOT NULL,
PRIMARY KEY (`CustomerID`),
UNIQUE KEY `CustomerID` (`CustomerID`),
UNIQUE KEY `CustomerID_2` (`CustomerID`,`Cus_Email`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=outfit

Answer 1

You need to change

UNIQUE KEY CustomerID_2 (CustomerID, Cus_Email)

to:

UNIQUE KEY Email (Cus_Email)

Otherwise, it only makes the combination of customer ID and email unique, not the email by itself, so you can have duplicate emails with different IDs. Since your INSERT doesn't specify the ID (because it's added automatically with AUTO_INCREMENT), the inserted data doesn't have a duplicate key.

ALso, you don't need

UNIQUE KEY CustomerID (CustomerID)

because the primary key is automatically a unique key.

So do the following:

ALTER TABLE Cus_acc_details 
    DROP KEY CustomerID, 
    DROP KEY CustomerID_2, 
    ADD UNIQUE KEY Email (Cus_Email);