ON DUPLICATE KEY UPDATE - when if statement true - update column, else trigger 1062 error to catch duplication

Question

I'am new to this site, and this is my first post.

I have MySQL PDO prepared query:

try {
      $sql = "INSERT INTO `site_users_wishlist` (`user_id`, `uin`, `active`)
              VALUES (?, ?, 'Y')";
      $stmt = $pdo->prepare($sql);
      $stmt->execute(array($userId,$uin));
}
catch (PDOException $e) {
        if ($e->errorInfo[1] == 1062) {
            echo "__item_exists_in_wishlist__";
            exit;
        }
        else {
            echo "Error adding item to wishlist!<br/>".$e;
            exit;
        }
}
echo "__item_added_to_wishlist__";
exit;

The 'active' column has values of Y / N / B (Yes, No, Bought).

When user adds item to his wishlist, it inserts into the database with 'active'='Y', and when user deletes item from the wishlist it becomes 'active'='N'.

At this point the problem goes on:

• If user tries to add item more than once, i catch 1062 error of duplicate entry and shows message to user.

• If the user previously deleted the item, and want to re-add it to his/her wishlist i want to show message that it successfully added to wishlist instead of it is already exists in database.

I need some clean code, something like

$sql = "INSERT INTO `site_users_wishlist` (`user_id`, `uin`, `active`)
        VALUES (?, ?, 'Y')
        ON DUPLICATE KEY UPDATE IF(`active`<>'Y', `active`='Y', TRIGGER DUPLICATE ERROR 1062";

Thanks in advance

EDIT1:

Table create code:

CREATE TABLE `site_users_wishlist` (
 `user_id` int(11) NOT NULL COMMENT 'user id',
 `uin` int(5) NOT NULL COMMENT 'item id',
 `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'date of addition by user',
 `added_price` int(10) DEFAULT NULL COMMENT 'price when added',
 `updated_price` int(10) DEFAULT NULL COMMENT 'current price',
 `active` tinytext NOT NULL COMMENT 'N - not, Y - yes, B - bought by user',
 PRIMARY KEY (`user_id`,`uin`),
 KEY `user_id` (`user_id`),
 CONSTRAINT `userId` FOREIGN KEY (`user_id`) REFERENCES `site_users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Answer 1

Ok, so, I didn't found elegant single query solution for this issue, but here is the solution with 3 queries, as in my specific needs there is no problem in making connection 3 times.

try {
        $sql = "SELECT * FROM `site_users_wishlist` WHERE `user_id` = ? AND `uin` = ? AND `active` <> 'Y'";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(array($userId, $uin));
        $exists = $stmt->rowCount();

            if ($exists == 1) {
                try {
                    $sql2 = "UPDATE `site_users_wishlist` SET `active` = 'Y' WHERE `user_id` = ? AND `uin` = ?";
                    $stmt2 = $pdo->prepare($sql2);
                    $stmt2->execute(array($userId, $uin));
                }
                catch (PDOException $e) {
                    echo "Error adding item to wishlist!<br/>".$e;
                    exit;
                }
            }
            else {
                try {
                    $sql2 = "INSERT INTO `site_users_wishlist` (`user_id`, `uin`, `uinsql`, `added_price`, `last_updated_price`, `active`) VALUES (?, ?, ?, ?, ?, 'Y')";
                    $stmt2 = $pdo->prepare($sql2);
                    $stmt2->execute(array($userId, $uin, $uinsql, $price, $price));
                }
                catch (PDOException $e) {
                    if ($e->errorInfo[1] == 1062) { // 1062 error code for duplicate entry
                        echo "__item_exists_in_wishlist__";
                        exit;
                    }
                    else {
                        echo "Error adding item to wishlist!<br/>".$e;
                        exit;
                    }
                }
            }
    }
    catch (PDOException $e) {
        echo "Error adding item to wishlist!<br/>".$e;
        exit;
    }

    echo "__item_added_to_wishlist__";
    exit;

Maybe it will be helpful someday for someone else ;)