Prepared statement syntax for INSERT ... ON DUPLICATE KEY UPDATE?
David
Hi,
Is the following the correct syntax for a prepared statement to carry
out an INSERT ... ON DUPLICATE KEY UPDATE statement?
INSERT INTO publication_source ps (
person_id
, publications_visible
, use_repository
, use_webpage
, use_localdata
, publications_webpage
)
VALUES (
SELECT p.id
FROM person p
WHERE p.username = ?
, ?
, ?
, ?
, ?
, ?
)
ON DUPLICATE KEY UPDATE
publications_visible = ?
, use_repository = ?
, use_webpage = ?
, use_localdata = ?
, publications_webpage = ?
In the above, person_id is the primary key in the publication_source
table, and what I want to do is insert a new record if there is not
already a record for that person_id, and update the existing record if
there is such a record. My back-end script (PHP) doesn't know the
person_id but can obtain it via the known username, hence the select
statement.
Do I have it right that I need to put binding placeholders (ie, the
question marks) for the parameters in both the VALUES clause and the
"ODKU" clause, and to explicitly bind the parameters twice (where it
is necessary for both clauses) in my binding instruction in my script
(PHP $stmt->bind_param)?
ie:
/* This is PHP */
$stmt->bind_param('siiiisiiiis',
$username
, $publns_vis_clean
, $publns_source_repo
, $publns_source_web
, $publns_source_local
, $publns_uri_clean
, $publns_vis_clean
, $publns_source_repo
, $publns_source_web
, $publns_source_local
, $publns_uri_clean
);
Thanks for any advice,
David.
Lennart Jonsson
[...]
> Is the following the correct syntax for a prepared statement to carry
> out an INSERT
>
> person_id
>
> , publications_visible
> , use_repository
> , use_webpage
> , use_localdata
> , publications_webpage
> )
>
> VALUES (
> SELECT p.id
> FROM person p
> WHERE p.username = ?
>
> , ?
> , ?
> , ?
> , ?
> , ?
> )
>
My guess is no. I would suggest:
INSERT INTO publication_source ps ( ... )
SELECT p.id, ?, ?, ...
FROM person p
WHERE p.username = ?
You will off course have to change the order of the parameters in your
php code.
/Lennart
David
Thanks for your advice, Lennart. I tried that, but unfortunately that
didn't resolve the problem (although with judicious use of PHP's
'var_dump($stmt)' I managed to establish that it was the 'prepare' that
definitely was failing so at least I knew that the error was in that
part of my code).
I was suffering from ERROR_COFFEE_LOW beforehand, but having now
rectified that, I had another thought (and also did what I should have
done in the first place (but the above-mentioned error prevented me from
doing so ¦-) ), which was to try to run the query (with appropriate real
values slotted in) directly on my DB (Note to self: keep repeating,
"It's just test data, it's just test data, you can just DROP it and
re-import it if (when) you break it.")).
It looks as though the following should Do The Right Thing,
specifically:
delete the mistaken 'ps' alias for the 'publications_source' table in
the INSERT clause that my fingers had somehow snuck in there (oops);
wrap the whole of the 'SELECT'..WHERE, which is inside the 'VALUES',
in brackets.
I haven't tried to run the prepared statement 'loaded up' in PHP yet,
but it worked at the mysql command line (with suitable parameter
values inserted).
I have to say, I find working out how to combine various
MySQL statements into a single more complicated statement quite
confusing sometimes!
> INSERT INTO publication_source (
person_id
, publications_visible
, use_repository
, use_webpage
, use_localdata
, publications_webpage
)
VALUES (
> (SELECT p.id
> FROM person p
> WHERE p.username = ?)
, ?
, ?
, ?
, ?
, ?
)
ON DUPLICATE KEY UPDATE
publications_visible = ?
, use_repository = ?
, use_webpage = ?
, use_localdata = ?
, publications_webpage = ?
Hopefully this might be useful to somebody else..
David.