Run an INSERT ON DUPLICATE KEY UPDATE using php and mysql

Question

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?

Is imageID not unique then? If not and you only want 1 image per student then you need a unique key on studentid. — P.Salmon, Apr 03 '20 at 14:41
@P.Salmon imageID is a primary key, so is unique yes with every row in the table. — xo., Apr 03 '20 at 14:42
U really have 3 params (id have to be set), and in update clause you shouldn't set id once more — splash58, Apr 03 '20 at 14:42
@splash58 I see, so should that just be `NULL` in the INSERT query? As the primary key imageID is set to auto incrament. — xo., Apr 03 '20 at 14:43
@P.Salmon With the insert query, I would have to set to NULL no? As I wouldn't know what id would be allocated as that column is just set to auto increment. Should I be setting that to something else? — xo., Apr 03 '20 at 14:45
You should not be inserting to imageid (ever) but you should have a unique key on studentid. BTW you don't have to insert to every column in the table unless not null is set. — P.Salmon, Apr 03 '20 at 14:45
You want `...ON DUPLICATE KEY UPDATE imagePath = VALUES....` — Martin, Apr 03 '20 at 14:48
As well as this, you want `ALTER TABLE profileImage ADD UNIQUE (studentID)` [[ref](https://stackoverflow.com/questions/5038040/mysql-make-an-existing-field-unique)] to set the `studentID` column to being a UNIQUE column, this will then trigger the ON DUPE.... — Martin, Apr 03 '20 at 14:50

Martin · Accepted Answer · 2020-04-03T15:03:45.800

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`)

Fantastic, thank you!! I hadn't set the studentID beforehand as the unique identifier - This is great thank you so much for pointing these items above out. Great explanation! :) — xo., Apr 03 '20 at 17:12

Run an INSERT ON DUPLICATE KEY UPDATE using php and mysql

1 Answers1

1)

2a)

2b)