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.

leftOuterJoin(string $table, string $alias = null, string $on = null) – Add a LEFT OUTER JOIN clause.

limit(string $limit, mixed $offset = 0) – Limit results in query.

naturalJoin(string $table, string $alias = null) – Add a NATURAL JOIN clause.

orderBy(string $fields) – Add an ORDER BY restriction.

select(string $fields) – Add fields in query selection.

where(string $restriction) – Add a restriction in WHERE clause (each restriction will be separated by an AND statement).

Below are some samples on how to use the SQL Query builder. First we will start by connecting to our database. Please note that the DB_PREFIX constant must be included in your since it’s required in the DbQuery class.

require_once dirname(__FILE__) . '/db-query.php';

define( 'DB_HOST', 'localhost' );
define( 'DB_USER', 'root' );
define( 'DB_PASSWORD', 'password' );
define( 'DB_NAME', 'sql_builder' );

/** Must include this constant in your script since it's used by the DbQuery class */
define( 'DB_PREFIX', '' );

error_reporting(E_ALL); 
ini_set('display_errors', 1);

// Create connection
$conn = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

All the code below will be pasted after the above section. I will write sample codes and echo the SQL query below.

SELECT & ORDER BY

$sql = new DbQuery();
$sql->select('*');
$sql->from('product', 'p');
$sql->orderBy('p.product_id');

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        print_r($row);
    }
}

SELECT * FROM `product` p ORDER BY p.product_id

WHERE, OR & LIMIT

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

SELECT * FROM `product` p WHERE (p.category_id = 3 OR p.category_id = 5) ORDER BY p.product_id LIMIT 10, 5

COUNT, GROUP BY

$sql = new DbQuery();
$sql->select('COUNT(category_id) AS cat_sum, category_id');
$sql->from('product');
$sql->groupBy('category_id');

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        print_r($row);
    }
}

SELECT COUNT(category_id) AS cat_sum, category_id FROM `product` GROUP BY category_id

INNER JOIN

/** 
 * Sql Joins
 * INNER JOIN: returns rows when there is a match in both tables.
 * LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
 * RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.
 **/
$sql = new DbQuery();
$sql->select('p.product_id, p.name AS product_name, c.name');
$sql->from('product', 'p');
$sql->innerJoin('category', 'c', 'p.category_id = c.category_id');

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        print_r($row);
    }
}

SELECT p.product_id, p.name AS product_name, c.name FROM `product` p INNER JOIN `category` `c` ON p.category_id = c.category_id

GROUP BY

$sql = new DbQuery();
$sql->select('COUNT(p.product_id) AS prod_sum, p.category_id');
$sql->from('product', 'p');
$sql->having('COUNT(p.category_id) > 5');
$sql->groupBy('category_id');

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        print_r($row);
    }
}

SELECT COUNT(p.product_id) AS prod_sum, p.category_id FROM `product` p GROUP BY category_id HAVING (COUNT(p.category_id) > 5)

And that’s it guys for today. Thanks for reading! If you love articles like this, be sure to leave a comment and share it on social media. Happy Coding!

Download the source code of this project on Github.