Write tidy Select and Join SQL Queries in PHP

Most of us have to interact with databases now and then in our projects, and SQL is by far the most common language used. However, working with SQL in PHP can be messy. If your queries are complex, you have to code them up as text strings which can be error prone, and suffer from formatting challenges. Also, when you want to build your SQL queries to have variables inside them, then you are forced to do substitution or pasting, which is a little bit tricky.

Today I will walk you through writing long and confusing Select and Join SQL Queries with ease in PHP. All you have to do is copy and paste this PHP file and save it in your project. The file contains a PHP class DbQuery with several methods/functions.

Download the source code of this project on Github.

The DbQuery class is a query builder which helps you create SQL queries. For instance:

$sql = new DbQuery(); 
$sql->select('*'); 
$sql->from('product', 'p'); 
$sql->where('p.product_id > 1'); 
$sql->where('p.category_id > 3'); 
$sql->orderBy('p.product_id'); $sql->limit(5, 10); 
echo $sql;

The above code will output the below query.

SELECT * FROM `product` p WHERE (p.product_id > 1) AND (p.category_id > 3) ORDER BY p.product_id LIMIT 10, 5

Main methods

__toString() — Generate and get the query.

build() — Generate and get the query (return a string).

from(string $table, mixed $alias = null) — Set table for FROM clause.

groupBy(string $fields) — Add a GROUP BY restriction.

having(string $restriction) — Add a restriction in the HAVING clause (each restriction will be separated by an AND statement).

innerJoin(string $table, string $alias = null, string $on = null) — Add a INNER JOIN clause
E.g. $this->innerJoin('product p ON ...').

join(string $join) — Add a JOIN clause
E.g. $this->join('RIGHT JOIN'.DB_PREFIX.'product p ON ...');.

leftJoin(string $table, string $alias = null, string $on = null) — Add a LEFT JOIN clause.