WackoWiki: SQL Query Builder

https://wackowiki.org/doc     Version: 7 (01.01.2020 09:28)

SQL Query Builder

-> R7.1
-> plus Prepared Statements support


<?php

// STS: experimental sql query builder
function sql_format1($string, $vals)
{
    $idx = 0;
    // $[*@#?$!]
    if (!is_array($vals))
    {
            $vals = [$vals];
    }

    return preg_replace_callback('/\s*\n\s*|;;$|\$[$*?]|[$:]([\w]*)|{(([^{}]*|(?R))*)}/',
        function ($x) use ($vals, &$idx)
        {
            if ($x[0][0] == '{')
            {
                $res = '';

                if (is_array($val = $vals[$idx++]))
                {
                    foreach ($val as $sub)
                    {
                        $res .= $this->sql_format1($x[2], $sub);
                    }

                    if (substr($res, -1) == ',')
                    {
                        $res = substr($res, 0, -1);
                    }
                }
                else if ($val)
                {
                    $res = $this->sql_format1($x[2], $val);
                }

                return $res;
            }

            if ($x[0] == ';;')
            {
                $this->cache_sql = 1;
                return '';
            }

            if ($x[0] == '$?')
            {
                $this->print_sql = 1;
                return '';
            }

            if ($x[0] == '$$')
            {
                return $this->config['table_prefix'];
            }

            if ($x[0] == '$*')
            {
                return $vals[$idx++];
            }

            if (ctype_space($x[0]))
            {
                return ' ';
            }

            if ($x[1] == '')
            {
                $val = $vals[$idx++];
            }
            else if (is_numeric($x[1]) && isset($vals[(int)$x[1]]))
            {
                $val = $vals[(int)$x[1]];
            }
            else if (isset($vals[$x[1]]))
            {
                $val = $vals[$x[1]];
            }
            else
            {
                return $x[0];
            }

            if ($x[0][0] == '$')
            {
                $val = quote($this->dblink, $val);
            }

            return "'" . $val . "'";
        }, $string);
}

function sql_format()
{
    $args = func_get_args();
    $fmt = array_shift($args);

    return $this->sql_format1($fmt, $args);
}

function sql_query2()
{
    $args = func_get_args();
    $fmt = array_shift($args);

    return $this->sql_query($this->sql_format1($fmt, $args));
}

function load_all2()
{
    $args = func_get_args();
    $fmt = array_shift($args);
    $this->cache_sql = 0;
    $this->print_sql = 0;
    $query = $this->sql_format1($fmt, $args);

    if ($this->print_sql) echo $query;

    return $this->load_all($query, $this->cache_sql);
}

function load_single2()
{
    $args = func_get_args();
    $fmt = array_shift($args);
    $this->cache_sql = 0;
    $this->print_sql = 0;
    $query = $this->sql_format1($fmt, $args);

    if (substr_compare($query, "LIMIT 1", -7, 7, true))
    {
        $query .= " LIMIT 1";
    }

    if ($this->print_sql) echo $query;

    return $this->load_single($query, $this->cache_sql);
}