Insert into SQL if record does not exist

Question

I have the following PHP function that adds a row to the table:

$idmusica = $_GET['idmusica'];
$queryvota=("
    INSERT INTO euk_sugestoes_votos (idusuario, idmusica)
    VALUES ($userid, $idmusica)
    ");

The table has this structure:

id | idusuario | idmusica

But I wanted that before inserting, he checked if this line with idusuario and idmusica already exists. If it already exists, should ignore, otherwise it inserts.

How can I make this check?

Thank you!

Answer 1

There is, create a index unique for the 2 columns id_usuario and idmusica and instead of using INSERT INTO use the REPLACE INTO, basically it does this automatic check.

The index unique would already solve the problem, but would return an error.

And switching to REPLACE as it will not update any field, it will just ignore the record.

But the REPLACE will change your primary key if it is AUTO_INCREMENT.

Another way, also keeping one index unique is to use the INSERR IGNORE, then it would be like this, for example:

INSERT IGNORE INTO euk_sugestoes_votos(idusuario,idmusica)VALUES(1,2);

To create the index, follow the command:

CREATE UNIQUE INDEX idx_unique_idusuario_idmusica
    ON euk_sugestoes_votos (idusuario,idmusica);