Create a PDO/MySQL Query Builder

Created by josh
October 19, 2020 8:10:34 PM PDT (Revised November 29, 2020 8:14:12 AM PST )


Extend PHP's PDO class to create a nice wrapper and query builder for MySQL, MariaDB and Percona Databases

The PDO class in PHP is a useful library for running queries and interacting with transctions between the codebase and the database. For MySQL (and databases alike), you can leverage your own extended library of PDO by building in some useful methods.

 

What queries would you need to build in a typical web app?

  • Select
  • Join (inner, left, right, outer, cross, natural, natural left, natural right... good grief)
  • Update
  • Insert
  • Delete
  • And, of course, the sub-clauses - where (and OR where), group by, order by, limit, for, into, on duplicate key

 

If you want to see an example of the full class, take a look at the Gaseous CMS project's Query class here.

 

Select:

For Select statements, you want to clarify columns, tables and/or partitions (which are optional). Remember, you can SELECT NOW() - so no target requirement. The linked example above to the Gaseous' project version is slightly more advanced, where it determines specific keywords in the statement that need to be quoted.

public function select(array $columns, $from = null, $partition = null)
{
    $i              = (int)0;
    $select_clause  = [];

    foreach ($columns as $alias => $col) {
        if ($i == $alias) {
            $select_clause[] = $col;
        } else {
            $select_clause[] = $col . ' AS ' . $alias;
        }

        if (is_int($alias)) {
            $i = (int)($alias+1);
        }
    }

    $this->select   = 'SELECT ' . implode(",\n", $select_clause);
    $this->from     = !empty($from) ? (' FROM ' . $from) : null;

    if (!empty($this->from) && !empty($partition)) {
        $this->from .= $partition;
    }

    return $this;
}

 

Join:

For various join types, default to inner joining without any condition (or rather - leave the condition blank since there's no standard way to set a default there).

    public function join($reference, $condition = null, $join_type = 'INNER')
    {
        if (
            !in_array(strtoupper($join_type), ['NATURAL', 'NATURAL INNER', 'NATURAL LEFT', 'NATURAL RIGHT', 'NATURAL OUTER'])
            && substr(strtoupper($condition), 0, 3) != 'ON '
        ) {
            $condition = 'ON ' . $condition;
        }

        $this->join .= ' ' . $join_type . ' JOIN ' . $reference . ' ' . $condition;

        return $this;
    }

 

Update:

    public function update($table_reference, array $assignment_list, $update_options = null)
    {
        $i              = (int)0;
        $set_clause     = [];

        foreach ($assignment_list as $key => $val) {
            if (is_int($key)) {
                $set = $val;
            } else {
                $set                = $key;
                $bind_array         = $val;
                $this->bind_array   = array_merge($this->bind_array, $bind_array);
            }

            if ($i > (int)0) {
                $set_clause[] = $set;
            } else {
                $set_clause[] = 'SET ' . $set;
            }

            $i++;
        }

        $this->update .= 'UPDATE ' . $update_options . ' ' . $table_reference . ' ' . implode(', ', $set_clause);

        return $this;
    }

 

Insert:

    public function insert($table_reference, array $assignment_list, $insert_options = null, $partition_reference = null)
    {
        $columns = $values = [];

        foreach ($assignment_list as $column => $value) {
            $columns[]          = $column;
            $values[]           = '?';
            $this->bind_array[] = $value;
        }

        $insert_clause = 'INSERT ' . $insert_options . ' INTO ' . $table_reference;

        if (!empty($partition_reference)) {
            $insert_clause .= ' PARTITION ' . $partition_reference;
        }

        $insert_clause .= ' (' . implode(', ', $columns) . ') VALUES (' . implode(', ', $values) . ')';

        $this->insert = $insert_clause;

        return $this;
    }

 

Delete:

    public function delete($table_reference, $delete_options = null, $partition_reference = null)
    {
        $delete_clause = 'DELETE ' . $delete_options . ' FROM ' . $table_reference;

        if (!empty($partition_reference)) {
            $delete_clause .= ' PARTITION ' . $partition_reference;
        }

        $this->delete = $delete_clause;

        return $this;
    }

 

Where:

    public function where(array $clause)
    {
        $i              = (int)0;
        $where_clause   = [];

        foreach ($clause as $key => $val) {
            if (is_int($key)) {
                $where = $val;
            } else {
                $where              = $key;
                $bind_array         = $val;
                $this->bind_array   = array_merge($this->bind_array, $bind_array);
            }

            if ($i > (int)0) {
                $where_clause[] = 'AND ' . $where;
            } else {
                if (empty($this->where)) {
                    $where_clause[] = ' WHERE (' . $where;
                } else {
                    $where_clause[] = ' AND (' . $where;
                }
            }

            $i++;
        }

        $this->where    .= "\n" . implode("\n", $where_clause) . ')';

        return $this;
    }

 

Group By:

    public function groupBy(array $group_by)
    {
        foreach ($group_by as $key => $g) {
            $group_by[$key] = trim($g);
        }

        $group_by_clause = ' GROUP BY ' .  implode(',', $group_by);

        $this->group_by = $group_by_clause;

        return $this;
    }

 

Order By:

    public function orderBy(array $order_by)
    {
        $order_by_clause = [];

        foreach ($order_by as $key => $val) {
            if (is_int($key)) {
                $order  = $val);
                $sort   = 'ASC';
            } else {
                $order  = $key;
                $sort   = in_array(strtoupper($val), ['ASC', 'DESC']) ? $val : 'ASC';
            }

            $order_by_clause[]  = $order . ' ' . $sort;
        }

        $order_by_clause = ' ORDER BY ' . implode(', ', $order_by_clause);

        $this->order_by = $order_by_clause;

        return $this;
    }

 

Limit:

    public function limit($row_count = 10, $offset = 0)
    {
        $row_count      = number_format($row_count, 0, '', '');
        $this->limit    = ' LIMIT ' . $offset . ', ' . $row_count;

        return $this;
    }

 

For:

    public function for($for = 'UPDATE', $options = null)
    {
        $for        = in_array(strtoupper($for), ['UPDATE', 'SHARE']) ? $for : 'UPDATE';
        $this->for  = ' FOR ' . $for . ' ' . $options;

        return $this;
    }

 

Into:

    public function into($var_name, $into_option = null, $charset_options = null)
    {
        $this->into     = ' INTO ' . $into_option . ' ' . $charset_options . ' ' . $var_name;

        return $this;
    }

 

On Duplicate Key (update):

    public function onDuplicateKeyUpdate(array $assignment_list)
    {
        if (!empty($this->insert)) {
            $set_clause     = [];

            foreach ($assignment_list as $column => $value) {
                $set_clause[]       = $column . ' = ?';
                $this->bind_array[] = $value;
            }

            $this->insert .= ' ON DUPLICATE KEY UPDATE ' . implode(', ', $set_clause);
        }

        return $this;
    }

 

Putting it all together:

public function buildQuery($debug = false)
    {
        if (!empty($this->select)) {
            $this->sql = $this->select
                . $this->from
                . $this->join
                . $this->where
                . $this->group_by
                . $this->order_by
                . $this->limit
                . $this->for
                . $this->into
            ;
        } elseif (!empty($this->update)) {
            $this->sql = $this->update
                . $this->where
                . $this->order_by
                . $this->limit
            ;
        } elseif (!empty($this->insert)) {
            $this->sql = $this->insert;
        } elseif (!empty($this->delete)) {
            $this->sql = $this->delete
                . $this->where
                . $this->order_by
                . $this->limit
            ;
        }

        if ($debug === true) {
            $debug_info = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1);

            var_dump($this->sql, $this->bind_array, $debug_info);
        }

        return $this->sql;
    }

 

See it in action!

Taking an example from Gaseous, this is a real-life application of the query builder.

$db = new Query();

$db->select(
    [
        'uri.uri',
        'ci.page_title_seo',
        'ci.page_title_h1',
        'c.uri_uid',
        'content_uid'               => 'c.uid',
        'parent_content_uid'        => 'c.parent_uid',
        'c.content_body_type_id',
        'content_body_type_label'   => 'cbt.label',
        'ci.uid',
        'ci.meta_desc',
        'ci.meta_robots',
        'ci.generated_page_uri',
        'ci.status',
        'ci.include_in_sitemap',
        'ci.minify_html_output',
        'pr.role_name',
        'content_created'           => 'c.created_datetime',
        'content_modified'          => 'ci.created_datetime',
        'c.created_datetime',
        'modified_datetime'         => 'ci.created_datetime',
        'page_identifier_label'     => 'COALESCE(ci.page_title_h1, ci.page_title_seo, uri.uri)'
    ], 'content AS c'
)->innerJoin(
    'content_body_types AS cbt',
    'cbt.type_id = c.content_body_type_id'
)->innerJoin(
    'uri',
    'uri.uid = c.uri_uid'
)->innerJoin(
    'current_content_iteration AS cci',
    'cci.content_uid = c.uid'
)->innerJoin(
    'content_iteration AS ci',
    'ci.uid = cci.content_iteration_uid'
)->leftJoin(
    'content_roles AS pr', 'pr.content_iteration_uid = ci.uid'
)->leftJoin(
    'account_roles AS ar', 'pr.role_name = ar.role_name'
)->leftJoin(
    'account AS a', 'ar.account_username = a.username'
);

if (is_array($content_uid)) {
    $db->where(
        [
            "c.uid IN ('" . implode("', '", $content_uid) . "')"
        ]
    );
} else {
    $db->where(
        [
            "c.uid = ?" => [$content_uid]
        ]
    );
}

$db->where(
    [
        "uri.archived = '0'",
        "ci.status = ?"         => [$status],
        "c.archived = '0'",
        "ci.archived = '0'",
        "cci.archived = '0'",
        "cbt.archived = '0'",
    ]
);

if ($username) {
    $db->where(
        [
            "(pr.role_name IN (SELECT role_name FROM account_roles WHERE account_username = ? AND archived = '0') OR pr.role_name IS NULL)" => [$username]
        ]
    );
} else {
    $db->where(
        [
            "(pr.role_name IS NULL OR pr.archived = '1')"
        ]
    );
}

$results = $db->fetchAllAssoc();