I agree, I’ve never been a fan of query building methods, they just take longer to write, longer to debug, longer to run and more difficult to optimize. I agree that if someone has the intention of supporting several different databases then some query building solution makes sense, but most of the time when I make a project for a client - why would I need to support more than one database or switch to xml or plain file data storage or whatever? The server has to support php and mysql, period, no problem with that at all.
- Are there any downsides to using : placeholders for string replacement? Any potential side effects I’m not aware of?
None that I can think of but if you are using preg_replace for substitution be aware of properly escaping the replacement string otherwise some strings (especially binary ones) will become corrupted. Initially I ran across this problem - I wrote about it here.
$sql = $this->sql('SELECT id, title FROM pages');
$sql->select('body, sort_id');
// SELECT id, title, body, sort_id FROM pages
Something like that seems to require knowledge of the string’s contents, whereas the current class doesn’t care what the string contains (other than placeholders).
To me that is overkill and I don’t see much benefit in it. I think parsing placeholders is enough for the sql class to do.
Some time ago I also came to the conclusion that I like plain sql better than building criteria in some convoluted ways. But I have gone a slightly different route because I also became a big fan of having table rows as objects like in Active Record pattern. That is what is used in Propel and the great thing is it allows you to extend each table class with your own methods which can parse and manipulate database results to your hearts content. Wanting to combine these 2 approaches I finally made a rather simple implementation of a Propel-like model that extends a simple database access class, which I can use as well. And for certain db manipulations I do things like this:
// simple row retrieval
$page = PagePeer::getByPK(10);
// simple data update
$page->title = 'Observer';
$page->save();
// delete
$page->delete();
// creating new row
$page = new Page;
$page->title = 'Observer';
$page->save();
$page_id = $page->page_id;
// getting a virtual table field
$title_length = $page->getTitleLength();
// using sql to get data as objects
$page = PagePeer::doSelectOne("SELECT *
FROM page WHERE cat_id=5 ORDER BY date LIMIT 1");
// shorthand for the above and using placeholders
$db->prepare_value(":cat_id", 5);
$page = PagePeer::doSelectOne("WHERE cat_id=:cat_id
ORDER BY date LIMIT 1");
// selecting many rows as objects
// $pages becomes an iterator object that I can traverse with foreach
$pages = PagePeer::doSelect("WHERE cat_id=:cat_id
ORDER BY date");
// if I want just plain data from db I use my db object directly
// - this is a wrapper function with some convenience methods
$page = $db->fetch_one_row("SELECT title, date
FROM page WHERE page_id=5"); // returns associative array
// fetch many rows in an array
$pages = $db->fetch_all_rows("SELECT title, date
FROM page WHERE cat_id=1");
// or when I need only 1 value
$title = $db->fetch_one_value("SELECT title
FROM page WHERE page_id=5");
// plain sql
$db->query("UPDATE page SET date=NOW()
WHERE cat_id=5");
This is slightly different from your approach but has a similar objective of being able to use plain sql easily. I also enjoy the simplicity of working with objects for simple queries with no sql while also being able to use sql whenever convenient. I have used this approach in a few projects and I like it a lot. I ditched the whole idea of building sql code by criteria objects, etc. in favour of plain sql. I think accessing database should be as simple as possible for the developer and I want to stay away from too much complexity or abstraction if possible. It is enough for me to learn sql and I don’t want to have to learn and remember all the query building methods in a complex db pattern.
BTW, my db class uses and extends mysqli. I found mysqli a bit more convenient to use and simpler than PDO. At the end of the day I don’t think it makes a lot of difference.