PDO Prepare two queries together

PHP
1

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?

2

Works you with PDO directly or have you a some class for it? If first, then create the class. That solves your problem with code redundancy.

And probably it would be better to use named placeholders, as :thid or :name.

3

I tried named placeholders, but it made no difference.

The code is already within a class. Note above the query (all three are) stored as a private property.
That way I can prep them in the first loop like:-

$sql = $this->dbedit->prepare($this->$label);	// Prepare the edit query for the data group (once for each group)

Then in the inner loop, I prepare the input data array and execute for each row.

I’m now looking into using a stored procedure, which seems to be working so far.
I can replace the first query with something as simple as:-

private $newmarks = "CALL newmark (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" ;		// 11 params
4

Named placeholders is not for your problem. This is just better to read.

If you have a class for DB-access you should not to take care about prepare at all. You should just write:

$db->execute($sql, $params);

or

$data = $db->fetchAll($sql, $params);

And DB-access class methods should also execute $statement->closeCursor(); command to avoid PDO-error 2014.

5

I used to always use named placeholders. You are right, it can make the code easier to understand.
Though to be honest, I rarely use them now. I find it a bit long-winded making associative arrays, and in some cases inflexible.

I do already have a PDO wrapper with a prepare/execute method, I will use it for one-off statements. But I don’t think it fits in this scenario, where I want to execute within a loop. I prefer to prepare only once, then execute multiple times.
Plus I’m not sure how it would help with PDO’s dislike of chained statements.

I think my complication has arisen because I am preparing three dis-similar statements within the outer loop, so processing them all with the same code.
I suppose I could have created a new method to take care of the messy business of the differences between the statements, having the double ones broken into separate ones.

In the end, I have made two stored procedures for the two double statements. It appears to work well so far, and has allowed me to keep the PHP side of things much cleaner, with the database taking care of what it does best. I think I can consider this one solved.

That one may have fixed the error I was getting, so one to bear in mind. Though I am happy with the procedure method this time.

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.