I. Introduction
The PHP PDO bothers me. Oh, it’s great, as are prepared statements. But it’s so… messy. And not as object-oriented as I would like.
Plus, some SQL syntax is just plain dumb. (OK, it’s almost as old as FORTRAN, when we wrote our code on the cave walls in deer blood. The dead hand of history often forces us down strange paths. But, still.)
Consider this. Assume a table ‘person’, with columns name (string), age (int), rating (float), and id (auto-increment int PK). We might see queries like:
INSERT INTO person (name, age, rating) VALUES ('Charles', 68, 9.5)
UPDATE person SET name='Charles', age=68, rating=9.5 WHERE id=17
SELECT * FROM person WHERE name='Charles' AND age=68 AND rating=90.5
We have three different syntaxes (syntacies?) for the same thing: associating a key with a value. That’s just cuckoo!
Now, there are a variety of PHP frameworks, platforms, and libraries, and some address parts of my complaint. But I found it useful (and educational!) to “roll my own”. The rest of this article describes my approach. Note that it is intended for (relatively!) simple queries, and not-too-huge databases. But it’s been working very well for the (progressive political campaign) platforms I’ve been building. (More about that, at charlesroth.net.)
II. A Modest (Object-oriented) Proposal: SQL fields
First off, let’s abstract the SQL field silliness, with a new class SqlFields (and a hat-tip to Jonathan Swift):
$sqlFields = new SqlFields(['name' => 'Charles', 'age' => 68, 'rating' => 9.5);
$insertSql = $sqlFields->makePreparedStatement("INSERT INTO person");
$updateSql = $sqlFields->makePreparedStatement("UPDATE person SET", "WHERE id=17");
$selectSql = $sqlFields->makePreparedStatement("SELECT * FROM person WHERE");
The $insertSql, etc. are just the fully-fleshed-out SQL prepared statements. The makePreparedStatement() calls take a “prefix” and a “suffix”, assembles all the “gooey stuff” in the middle, slams them all together, and returns the complete statement.
III. An Object-oriented RESULT
At the other end, dealing with the results of a PDO SQL query is also kinda cuckoo. We’ve got the PDO, the actual statement object, the resulting rows, exceptions that can happen in all of those places, insert ids, and the proverbial partridge in the pear tree. (Say that three times fast!)
I want a simple execution, and a simple result. So I’ve defined a PdoRunResult that contains… everything. Success, failure, error info, the rows, the count, the last insert id, and (even) an easy way to get a single value back if that’s all I wanted.
So I can write code like this, where $result is a PdoRunResult object:
$result = $pdo->runSF("SELECT * FROM person", "", $sqlFields); // explained in next section
foreach ($result->getRows() as $row) { ... }
That’s it. Note that no exceptions are thrown! If there are exceptions inside runSF(), you get zero rows, and you can interrogate the error status via $result->getError(). Failing SQL queries are not exceptional! They happen all the time. Deal with it, rather than throwing an exception and hoping somebody else will handle it.
(Side digression: the whole question of exceptions vs error returns is a big debate. But the best advice I’ve seen is that “exceptions must be exceptional”. There’s always going to be a trade-off as to which is optimal.)
Note that there are no statement objects. We don’t need them. (Yes, a more complicated query, that ended up reusing statements for some tricky optimization, might. In my context, I don’t care.)
IV. AlfredPDO — extending PDO.
Finally, the “good stuff in the middle” (old joke). I have an open-source utility library “Alfred” (think Batman’s butler), at CharlesRothDotNet\Alfred. It contains the class AlfredPDO, which extends the normal PDO. (Along with SqlFields, etc.)
The most important method is runSF(). As shown above, it’s called with an SQL “prefix” and “suffix”, just like in the SqlFields example in section II, plus the actual SqlFields object. It does all of the work of inspecting the key/values in the SqlFields object to the prepared statement, binding them with the proper type, and then running the query. (And then closes the cursor.)
The resulting rows, error info, exception info, last insert id (if any), and the full “raw” (or “generated”) SQL (if available from your DB server) are all stuffed into the PdoRunResult object.
V. Complete Examples
These are, of course, trivial examples. But this little set of objects has greatly reduced the size and complexity of all of my queries in all of my (middling-sized) projects.
$pdo = new AlfredPDO($dbname, $dbuser, $dbpw, $dbhost);
if ($pdo->failed()) { // do error handling stuff }
$sqlFields = new SqlFields(['name' => 'Charles', 'age' => 68, 'rating' => 9.5);
$insertResult = $pdo->runSF("INSERT INTO person", "", $sqlFields);
if ($insertResult->failed()) { // do error handling stuff }
echo $insertResult->getInsertId();
$updateResult = $pdo->runSF("UPDATE person SET", "WHERE id=17", $sqlFields);
if ($updateResult->failed()) { // do error handling stuff }
$selectResult = $pdo->runSF("SELECT * FROM person WHERE", "", $sqlFields);
if ($selectResult->failed()) { // do error handling stuff }
foreach ($selectResult->getRows() as $row) {
...
}
VI. Conclusion
That’s it, really. The full code is in the packgist repository CharlesRothDotNet\Alfred, along with a bunch of other utility stuff (like the “Str” package that acts a lot like the Java Apache StringUtils). It’s still quasi-experimental (May 2025), but I’m using inside sites like the Michigan voter guide mivoter.org.
Yes, it’s simple. Yes, it won’t play well with really complicated queries. (And yes, it could be expanded.) But it does so much of the messy stuff that we too-often do “by hand”, and it treats everything as first-class objects. That’s “good enough”.
I’d love to know of other approachs/platforms/libraries that have taken on this problem — particularly the “SQL syntax is dumb” complaint. 🙂