INSERT or UPDATE, but not sure I can have duplicate key update

Creating a bit of a bookmark system. Users can bookmark or un-bookmark players to follow. On the surface, I thought a duplicate key update would work with the playerID. However, a player might have several Users bookmark him.

So it would have to pair up playerID to userID. If that pair doesn't exist, INSERT. If that pair does exist, UPDATE.

Below is what I had for the duplicate key update. How would I go about checking a pairing of two columns? Concat?

$query = "INSERT INTO a_player_bookmark (playerID,bookmark,userID,username)
                        VALUES ('". $pid ."','". $bookmark ."','". $userID ."','". $user ."')
                        ON DUPLICATE KEY
                        UPDATE bookmark = '". $bookmark ."'

                        ";

Solution

Right, so relate each column via a foreign key (for completeness' sake), and then make a unique key over 2 columns, both the playerID (follower) and userID (followed). Then you can have a -> b, and b -> a, but adding it again will cause a duplicate key error. This allows you to use ON DUPLICATE KEY UPDATE follower=follower to skip inserting, or bookmark = VALUES(bookmark) to update the bookmark value.

I've also renamed your table bookmarks for brevity.

-- the sql query (plain text / string)
INSERT INTO bookmarks (playerID, bookmark, userID, username)
    VALUES (:playerID, :bookmark, :userID, :username)
ON DUPLICATE KEY UPDATE bookmark = VALUES(bookmark)

Now within PHP, I've demonstrated the basic concepts of running a prepared statement with PDO. If you're not willing to do PDO then you can just concatenate into the query accordingly (replacing the placeholders yourself), but I strongly discourage it.

//within php
$con = /* a PDO connection reference, see PDO docs for creating one */;
$query = /* our earlier statement, in string form */;
$stmt = con->prepare($query); //creates a PreparedStatement
//bind our values to the placeholders in the sql statement
$stmt->bindValue(':playerID', $pid, PDO::PARAM_INT);
$stmt->bindValue(':bookmark', $bookmark, PDO::PARAM_STR);
$stmt->bindValue(':userID', $userID, PDO::PARAM_STR);
$stmt->bindValue(':username', $user, PDO::PARAM_STR);
$stmt->execute(); //run the query
$updated = $stmt->rowCount(); //number of updated rows

Moving forward, I would drop username as a column (seems redundant, why not just reference the users table to get the name?), and then depending on the purpose of bookmark I would potentially even forsake that.

But before any of this, you need to ensure the proper keys are on your table.

-- I suggest removing any existing constraints beforehand
-- Relate playerID to the users table
ALTER TABLE bookmarks ADD CONSTRAINT `FK_follower` FOREIGN KEY (`playerID`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
-- Relate userID to the users table
ALTER TABLE bookmarks ADD CONSTRAINT `FK_followed` FOREIGN KEY (`userID`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
-- Relate username to the users table, if you decide to keep it
ALTER TABLE bookmarks ADD CONSTRAINT `FK_followed_name` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON UPDATE CASCADE ON DELETE CASCADE
-- Add your UNIQUE KEY for the two columns defining the relationship, aka a composite key
ALTER TABLE bookmarks ADD UNIQUE INDEX `following` (`playerID`, `userID`)