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
– P.Salmon, Commented Apr 3, 2020 at 14:41
@P.Salmon imageID is a primary key, so is unique yes with every row in the table. — xo.
– xo., Commented Apr 3, 2020 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
– splash58, Commented Apr 3, 2020 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.
– xo., Commented Apr 3, 2020 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.
– xo., Commented Apr 3, 2020 at 14:45

Mm-Art-In · Accepted Answer · 2020-04-03 15:03:45Z

2

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

edited Apr 3, 2020 at 15:03

answered Apr 3, 2020 at 14:57

Mm-Art-In

23k13 gold badges81 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xo. Over a year ago

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! :)

Collectives™ on Stack Overflow

Run an INSERT ON DUPLICATE KEY UPDATE using php and mysql

1 Answer 1

1)

2a)

2b)

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1)

2a)

2b)

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related