PHP SQL query Builder without DB connection

Question

I've got an old project that fetch data this stupid way:

$db = new Database($server, $name, $pass);
$where = 'country = "UK"';
if ($_GET['gender']) { $where .= ' AND gender = "' . $_GET['gender'] . '"'; }
$sql = "SELECT * FROM users WHERE $where ORDER BY name";
$users = $db->fetchAll($sql);

I would like to improve it with an SQL builder (on other projects I use Zend's DB Tables), but problem is, I need to keep the Database class because it's fetch*() methods does more than just loading data (e.g. debugging, performance, statistics, etc.).

I have looked at Zend_Db_Table and this post but all these query builders are attached to a DB connector with own fetch() or execute() methods. But I would need a builder that just creates SQL query that can be used in existing method:

$query = new MySqlQuery();
$query
    ->select('*')
    ->from('users')
    ->orderBy('name')
    ->where('country', 'UK');
if ($_GET['gender']) { 
    $query->where('gender', $_GET['gender']); 
}
$sql = $query->toString();

$db = new Database($server, $name, $pass);
$users = $db->fetchAll($sql);

I was thinking about simply stealing the Zend classes and rewriting them so they don't need the DB connection, but I would like to see if these is something already done.

Couldn't your `Database` class inherit the Zend_Db_Table class, overwrite the method for `fetchAll` but still have it call the parent `fetch` (in this case, `Zend_Db_Table::fetch`) to retrieve the data? __NB__ - I don't use ZendFrameworks so this is pure speculation. guess work, and "finger in the air" luck — DaveyBoy, Sep 08 '16 at 14:56
@DaveyBoy Unfortunately parts of the project are non-OOP (the SQL query) while others are overly OOP so the inheritance tree of the `Database` is `Database` <- `BaseDatabase` <- `CustomDb` <- `BaseDb` <- `AbstractDb` <- `BaseObject` <- `AbstractObject` and it would be very hard to inherit from something else. So the `Database` has to stay _as is_ because I don't have time to completely rewrite it. — Radek Pech, Sep 08 '16 at 16:17

score 2 · Accepted Answer · answered Sep 09 '16 at 11:47

I've created modified Zend_Db_Select that does not require active connection to the database.

require_once 'Zend/Db/Query/Mysql.php';

$query = new Zend_Db_Query_Mysql();

$query
    ->from(array('a' => 'articles'))
    ->columns(array('id', 'text' => 'content_text'))
    ->joinLeft('authors',
        $query->column('author', 'articles', new Zend_Db_Expr('authors.id'))
    )
    ->where($query->column('archived', 'articles', 0))
    ->order(new Zend_Db_Expr($query->column('release_time', 'articles') . ' DESC'))
;

$sql = $query->assemble();

As a bonus, I've added the column() method that translates column and table names to their aliases.

Available as a Zend extension or a standalone library.

score 0 · Answer 2 · answered Jun 06 '18 at 15:14

This project seems to be what you want:

https://github.com/nilportugues/php-sql-query-builder

The only bummer is that it requires PHP 5.4 or higher. I'm facing the same problem, and on top of that my PHP needs to be 5.3, which is the sole reason why I'm not using it.

PHP SQL query Builder without DB connection

2 Answers2