0

I am trying to prevent a duplicate entry into my database. I have two select boxes on a form that are populated from two tables category and publisher. Using the UPDATE method below it duplicates the entry if I send the same value to the database. What changes can I make so entries are not duplicated when updated?

    $sqlQuery = "UPDATE records
                INNER JOIN category ON category.catID = records.catID
                INNER JOIN publisher ON publisher.pubID = records.pubID
                SET title=$title, year=$year, desc=$desc, price=$price, pub=$pub
                WHERE recID=$recID"; 

        $stmt = $dbConn->prepare($sqlQuery);
        $stmt->execute(array($sqlQuery));
DonE
  • 13
  • 1
  • 5
  • 1
    "What changes can I make so entries are not duplicated when updated?" MySQL supports a UNIQUE key which can and will prevent duplication. – Raymond Nijland Dec 05 '18 at 15:01
  • Update is not duplicate anything, since its an update. What do you mean, duplicate? Please clarify your question. – vaso123 Dec 05 '18 at 15:03

1 Answers1

1

You simply need to create a unique key using the columns that make a row unique...

ALTER TABLE records
ADD CONSTRAINT U_records UNIQUE (title,pub);

Here the example to make all records unique using title and pub columns. This means there can be multiple duplicate titles, but they'll need a different pub. Please modify to your use case, you can add as many columns as you want but keep it as low as possible for performance reasons.

Adding this constraint will produce an error from MySQL (a contraint violation if IRC?). You will need to catch it to tell the user.

Salketer
  • 14,263
  • 2
  • 30
  • 58