Difference between exec and execute in php

Question

I'm learning php but in the tutorial I use, I can see something like these lines :

$DatabaseAdd->exec('INSERT INTO db_name......

and this one :

$request->execute(array(.....

Is there any difference between exec and execute? Can we use one to replace the other?

Answer 1

tl;dr

• Use PDO::exec to issue one-off non-prepared statements that don't return result sets.
• Use PDO::query to issue one-off non-prepared statements that return result sets.

Both of these are useful if the statements are only executed one time and/or if they are constructed dynamically in a way that is not supported by prepared statements. Usually requires additional tooling to properly construct statements (and avoid things like SQL injection vulnerabilities). That coupled to the fact their flexibility is seldom needed means that it's often preferred to:

• Use PDOStatement::prepare and PDOStatement::execute to prepare statements and execute them, regardless of whether they return results or not. Useful if executed multiple times and/or in a hot path. Also doesn't require additional tooling to handle statement construction. Almost always recommended whenever possible.

---

exec and query act on PDO objects and thus only within the context of a connection. exec is used for statements that don't return a result set (e.g. INSERT, UPDATE, DELETE) while query will return result sets (e.g. from a SELECT statement). They are simply two similar interfaces to do essentially the same thing (issue a statement). The SQL statements are passed as-is to the server and thus may be considered dynamic from the perspective of the client.

What this means is that in theory they might always (i.e. on every call) be parsed, interpreted/compiled and optimized to a query plan by the DBMS before being executed. This is costly in terms of performance if they're executed multiple times.

In practice, they're often cached and reused if executed multiple times, but a DBMS can only do this opportunistically and without any guarantees. Depending on how they are matched, changing the query slightly might require that the DBMS recompiles it entirely. Sometimes the client will construct the query dynamically (too often with primitive string concatenations, sometimes with proper language-based or library-based tooling support) such that it's simply impossible for the DBMS to cache the query plan.

Update: For the curious, Pinq is an example of language-based query builder for PHP and Doctrine LDBAL is library-based example. Note that Pinq only parses PHP expressions for predicates (it seems) and still uses a fluent API for the rest (though some consider that fluent interfaces can form types of DSLs).

With proper tooling and/or when the statement is only executed one time (or a very small number of times), this is fine and sometimes necessary.

For cases where you know you will be issuing the same statement multiple times, only perhaps with different parameters (e.g. a different value in a predicate/WHERE clause), wouldn't it be great if there was a way to communicate it to the DBMS so that it doesn't throw away the whole query plan for sure? It might also allow it to do more heavyweight optimization that it might not otherwise do since it has more time to prepare the statement (slow initialization phase) before it's executed (perhaps in a hot path).

Most database systems offer this capability in the form of prepared statements (using various mechanisms, non-standard AFAIK). PDO exposes it in a unified way through the prepare method, which returns another object which represents the prepared statement.

You can then reuse that object and particularly its execute method (which issues a statement to the DBMS to execute the previously prepared statement). If the statement is parameterized, you can even pass new parameters for each execute call.

This also forces you to use sufficiently appropriate tooling to construct your statements and issue them. As I alluded to earlier, basic string concatenation and other ad-hoc techniques will only get you so far before you shoot yourself in the foot, most likely by failing to escape dynamic parts/parameters properly. This is the #1 source of SQL injection vulnerabilities.

Note that if the statement returns a result set (e.g. SELECT), you will need to use the various fetch variants to retrieve the results.

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
$result = $sth->fetchAll();
print_r($result);

(Source)

Also note that very confusingly, query itself returns its results in the form of a PDOStatement object (just like those returned by prepare). Although one might understand why this interface is reused here, it's probably not the best design (to say the least). For example, and although (a) it doesn't appear to be documented explicitly and (b) I haven't tested it, I would assume calling execute on a PDOStatement returned by a query is illegal (produces an error).

Disclaimer: Only interpreting the docs, not a frequent user anymore.

Similar questions:

• PDO's query vs execute
• PDO::query vs. PDOStatement::execute (PHP and MySQL)
• PHP PDO Prepare queries