Search code examples
phpmysqlprepared-statementon-duplicate-key

Run an INSERT ON DUPLICATE KEY UPDATE using php and mysql

I am trying to use an INSERT ON DUPLICATE KEY UPDATE query in PHP and MySQL.

I have a user profile where a user can update an image to their profile (where it then gets saved into the DB). My issue is, if inside of the profileImage table they already have an entry in there (and the user can be identified with studentID, then only run an UPDATE on the image with just changing the filePath (which holds the name of the image) and do not insert another row into the db table.

However I'm having issues with the query still allowing users to upload more than 1 image.

php script:

$stmt = $conn->prepare ("INSERT INTO `profileImage` (`imageID`, `imagePath`, `studentID`) VALUES (NULL, ?, ?) ON DUPLICATE KEY UPDATE `imageID` = VALUES (`imageID`), `imagePath` = VALUES (`imagePath`), `studentID` = VALUES (`studentID`) ");
$stmt->bind_param("si", $fileName, $studentID); 
$stmt->execute() or die("Failed to insert image into the database");

profileImage table in the db:

imageID is primary key and studentID is a foreign key, is there anyway I can set the studentID in my statement as the trigger, so if the studentID already exists in this table, THEN run the update rather than the insert? Table Screenshot

Solution

  • 1)

    It looks like you need to ensure that the studentID column value is UNIQUE. For this two things are best; to set it as unique and to allow NULL values (this may not be required but it depends on how you're using your data).

    So, from this SO Question you can do:

    ALTER TABLE `profileImage` ADD UNIQUE (`studentID`)

    in your SQL.

    2a)

    Once this is done, then you need to tweak your original SQL Dupe check, as started in comments by P.Salmon, you should never need to be setting or updating the Primary Key column.

     INSERT INTO `profileImage` (`imagePath`, `studentID`) VALUES ( ?, ?) 
 ON DUPLICATE KEY UPDATE `imagePath` = VALUES (`imagePath`), `studentID` = VALUES (`studentID`)

    2b)

    As well as this, you also don't need to set the studentID column if it's already there, and (assumed to be) the cause of the duplication; so:

     INSERT INTO `profileImage` ( `imagePath`, `studentID`) 
 VALUES ( ?, ?) ON DUPLICATE KEY UPDATE `imagePath` = VALUES (`imagePath`)