Trying to update an existing record - getting an error

Question

I'm trying to insert records into my DB, if a duplicate is found it should only update one field.

This is my code so far

$strSQL = "INSERT INTO ppage_image_uploads(Cons_ID, Img_SRC, Program, TeamRaiser_ID) 
    VALUES
    ('" . $_POST["cons_id"] . "',
    '" . $_POST["img_src"] . "',
    '" . $_POST["program"] . "',
    '" . $_POST["tr_id"] . "')
    ON DUPLICATE KEY UPDATE
        Img_SRC = VALUES '" . $_POST["img_src"] . "'
    )";

Took this loosely from another question: MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query

I'm ending up with an error message from my page that handles this script saying

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''theimageurl.jpg' )' at line 8

Does anything look wrong in that statement that I'm missing?

You might want to research SQL injection and what you can do about it ... i suggest you to read [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Raymond Nijland, Sep 11 '19 at 13:28
@JimmyB thank you very much! That did the trick, initial record commits and then a second attempt only updates the image. Exactly how I wanted it to. Thanks a million! — HisPowerLevelIsOver9000, Sep 11 '19 at 14:23

GMB · Answer 1 · 2019-09-11T14:35:56.747

0

There is an unmatched closing parenthesis at the end of your sql query.

The VALUES in the ON DUPLICATE KEY clause can be used to refer to the value that would have been inserted if no duplicate key conflict had happened, as explained in the documentation.

$strSQL = "INSERT INTO ppage_image_uploads(Cons_ID, Img_SRC, Program, TeamRaiser_ID) 
    VALUES
    ('" . $_POST["cons_id"] . "',
    '" . $_POST["img_src"] . "',
    '" . $_POST["program"] . "',
    '" . $_POST["tr_id"] . "')
ON DUPLICATE KEY UPDATE
    Img_SRC = VALUES(Img_SRC)";

edited Sep 11 '19 at 14:35

answered Sep 11 '19 at 13:31

GMB

216,147
25
84
135

Thanks @GMB for the response. Still getting the same error though unfortunately – HisPowerLevelIsOver9000 Sep 11 '19 at 14:21
@HisPowerLevelIsOver9000: I updated the query, would you please check again? – GMB Sep 11 '19 at 14:36

Trying to update an existing record - getting an error

1 Answers1