How to check for duplicates before inserting into database?

Question

So I made a function to add photos to favorites, how do I make it so I don't keep adding the same images?

My code:

function addToFavorites($fileName)
{
    global $conn;
    $email = $_SESSION['email'];
    $imageId = $_GET["id"];

    $sql = "insert into favorites set UserEmail='".mysqli_real_escape_string($conn, $email)."', ImageID=".$imageId;
    $result = mysqli_query($conn, $sql);

Any help would be great thanks!

Answer 1

You let the database do the work! Simply define a unique constraint or index on the table:

alter table favorites add constraint unq_favorites_useremail_imageid
    unique (useremail, imageid);

With this constraint in place, the database will return an error if you attempt to insert a duplicate.

If you want to avoid the error, you can use on duplicate key update:

insert into favorites (UserEmail, ImageId)
    values (?, ?)
    on duplicate key update ImageId = values(ImageId);

Some notes about this:

• The ? is a parameter placeholder. Learn to use parameters rather than munging values in query strings.
• This does not use set. That is a MySQL extension. There is no advantage in this case; you might as well use the standard syntax.
• The on duplicate key is a no-operation, but it prevents the code from returning an error when there is a duplicate.