Validate, if already exists does not INSERT

Asked

Viewed 1,630 times

2

I have the following code that records in the database the id of an article that the user "liked".

$sql = "INSERT INTO favoritos (id_user, id_oferta)
VALUES ('$login_session', '$id')";

if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}

The way I have the code the user can like the same article as many times as he wants.

How can I resolve this situation?

php

  • If your table is Mysql, create a unique index with the id_user and id_offer fields or before doing the Insert, select and check if you returned any results, if yes, do not update.

4 answers

4


does the search for that favorite and if it exists does not do the.

$sql = "select * from favoritos where id_user=? and id_oferta=? limit 1"
$stmt=$conn->prepare($sql);
$stmt->excute(array($login_session,$id));
$result= $stmt->fetch(PDO::FETCH_ASSOC)

 if($result != false){ //ja existe
//   ..insere no banco
 }

  • I think my brain was being a little lazy.. Thanks John thanks for the help. A Hug!

  • 2

    The right way to handle this case is with a Constraint. What if two threads, for example, two browsers (two browsers will create different sessions, possibly different threads will access the same code) at the same time trying to add as a favorite? with this code, could happen a race condition in the IF and duplicate the favorite.

  • so that in Nxn tables, the two keys are left as primary, thus preventing any duplicate.

2

I would use ON DUPLICATE KEY. You can read more on DOC. The advantage is to have a single query for Insert and update.

INSERT INTO favoritos( id_user, id_oferta )
VALUES( $login_session, $id )
ON DUPLICATE KEY UPDATE id_user = $login_session, id_oferta = $id

2

Another solution for not having to make two requests in the bank is to make one Insert with select:

INSERT INTO 
  favoritos (id_user, id_oferta)
SELECT
  ('$login_session', '$id')
WHERE 
  NOT EXISTS( SELECT 1 FROM favoritos WHERE id_user = '$login_session' AND id_oferta = '$id')

1

In case someone has a similar problem, this is my code: 

//verifica se já existe
$query = mysql_query("select * from favoritos where id_user='$login_session' AND id_oferta='$id'", $connection);
$rows = mysql_num_rows($query);
if ($rows != false) {
echo "ja existe";
} else {

 //Faz o que eu quero :p
}

Browser other questions tagged php

You are not signed in. Login or sign up in order to post.