CODEWITHSUNDEEP
phpmysql
Radek Pech
Radek Pech

Reputation: 3098

PHP SQL query Builder without DB connection

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.

Upvotes: 1

Views: 1432

Answers (2)

Lucas Madureira
Lucas Madureira

Reputation: 21

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.

Upvotes: 0

Radek Pech
Radek Pech

Reputation: 3098

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.

Upvotes: 2

Related Questions