I can’t believe I have not come across this before, but here it is. I assumed I would be able to chain two queries together in a prepared statement.
When I first tried, it gave me a syntax error. After doing a bit of searching and reading, it seems I must enable emulated prepares for this to work. Which now I think about it, that makes sense.
When I try again in emulate mode, the syntax error is gone, but I now get a buffering error, like:-
General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.
What I read tells me the fix for that is setting use buffered query, but I get the same error regardless of the setting on that option.
The main reason for chaining the queries is this.
private $newmarks = "INSERT INTO seamarks (thid, name, type, light, sound, racon, colour, shape, lat, lon, abp, active) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, 1) ; INSERT INTO changes (mid, lat, lon, date) VALUES (LAST_INSERT_ID(), ?, ?, ?)" ; // 13 params
The second query uses the ID made by the first one. I could prepare and execute them separately, getting the insert ID from the first one between executes, but that would complicate the code.
Also worth noting, as I think it is probably relevant, the queries are executed in a loop, multiple times.
Actually a loop within a loop, as there are 3 different prepares. Only the first 2 are double queries, only the first one (shown) needs the last ID.
Should this be possible at all?
Or should I be looking at setting up a stored procedure instead?