WackoWiki

View source for SQL Query Builder

-> R7.1
-> plus Prepared Statements support


%%(hl php)
<?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);
}
%%