On duplicate key update always inserting new row

[Adamhumbug]

Hi All,

I have a php function to insert data into my database which works fine but i want to update the record using the same button and have thus used on duplicate key update.

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    if (isset($_POST['createNewQuote'])) {
        
        $sql = 
        "INSERT into quote (name, internal_ref, currency, kit_delivery, kit_return, job_id) 
        values (:name, :internal_ref, :currency, :kit_delivery, :kit_return, :jobId)
        ON DUPLICATE KEY UPDATE
        name=:name
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([
            ':name' => $_POST['quoteName'],
            ':internal_ref' => $_POST['internalReference'],
            ':currency' => $_POST['currency'],
            ':kit_delivery' => $_POST['kitDeliveryDate'],
            ':kit_return' => $_POST['kitCollectionDate'],
            ':jobId' => $_POST['jobId']
        ]);
    }
}

The issue that i have is that everytime i click the save button that triggers this function, i am getting a new row in the database created - is there something obvious i am doing wrong?

I have attached the structure just in case that helps

[Barand]

You should be getting a mysql error - you have 7 placeholders but only passing 6 values in the execute. The way you have defined it you need to pass the $_POST['quotename'] twice.

A better way is

...
ON DUPLICATE KEY UPDATE
        name=VALUES(name)

telling it to use the same value as in the VALUES clause. The you only need provide the name once.

As for your problem, what has to be duplicated to throw a duplicate key error? Your id will be auto_incremented so that won't be duplicated.

[Adamhumbug]

2 minutes ago, Barand said:

You should be getting a mysql error - you have 7 placeholders but only passing 6 values in the execute. The way you have defined it you need to pass the $_POST['quotename'] twice.

A better way is

...
ON DUPLICATE KEY UPDATE
        name=VALUES(name)

telling it to use the same value as in the VALUES clause. The you only need provide the name once.

As for your problem, what has to be duplicated to throw a duplicate key error? Your id will be auto_incremented so that won't be duplicated.

Thats my issue right there - i was relying on the primary key (id) - i didnt realise that it would automatically increment when using this method.

[mac_gyver]

the place-holder mismatch will work using emulated prepared queries (which should be avoided whenever possible), but not for true prepared queries. the emulator is more of an approximator, because there are differences that you can detect between how it works vs a true prepared query.

[Barand]

It will if you try to write the same id twice - include id in the fields to be inserted if it should update the one already there or pass null if you want a new record inserted

[Adamhumbug]

6 minutes ago, Barand said:

It will if you try to write the same id twice - include id in the fields to be inserted if it should update the one already there or pass null if you want a new record inserted

Thank you all - issue resolved.