Page 1 of 1

String format with variables binding

Posted: Fri Nov 13, 2009 3:14 am
by VladSun

Code: Select all

function compile_binds($string, $binds)
{
    if (!preg_match_all('#{(\d+)}#', $string, $map))
        return $string;
 
    if ( ! is_array($binds))
        $binds = array($binds);
 
    $map = $map[1];
    $segments = preg_split('#{(\d+)}#', $string);
 
    $result = '';
    for($i=0, $len = count($segments); $i<$len; $i++)
    {
        $result .= $segments[$i];
 
        if (array_key_exists($i, $map))
        {
            if (!array_key_exists($map[$i]-1, $binds))
                throw new Exception('Bind index not found for {'.$map[$i].'}', 1025);
                    
            $result .= $binds[$map[$i]-1] ;
        }
    }
 
    return $result;
}
Usage:

Code: Select all

$query = "
    select
        *
    from
        person
    where
        (id = {1} or {1} IS NULL)
        or
        (surname={3} and firstname = {2})
";
echo compile_binds($query, array(
    10,
    'John',
    'Trotzki'
));
For CI users:
- go to "system/database" directory;
- open DB_driver.php;
- rename compile_binds method to compile_binds_ci;
- insert this code:

Code: Select all

    function compile_binds($sql, $binds)
    {
        if (!preg_match_all('#{(\d+)}#', $sql, $map))
            return $this->compile_binds_ci($sql, $binds);
 
        if ( ! is_array($binds))
            $binds = array($binds);
 
        $map = $map[1];
        $segments = preg_split('#{(\d+)}#', $sql);
 
        // Construct the binded query
        $result = '';
        for($i=0, $len = count($segments); $i<$len; $i++)
        {
            $result .= $segments[$i];
 
            if (array_key_exists($i, $map))
            {
                if (!array_key_exists($map[$i]-1, $binds))
                    throw new Exception('Bind index not found for {'.$map[$i].'}', 1025);
                    
                $result .= $this->escape($binds[$map[$i]-1]) ;
            }
        }
 
        return $result;
    }
This way you can use both the "?" placeholder style or the {N} one.

EDIT: Exception is thrown if {N} is not found in the bind list.
EDIT2: NULL values bug fixed

Re: String format with variables binding

Posted: Fri Nov 13, 2009 3:58 am
by onion2k
Very nice.

Isn't your example code going to end up with "(id = 10 or 10 IS NULL)" though? :twisted:

Re: String format with variables binding

Posted: Fri Nov 13, 2009 4:22 am
by VladSun
onion2k wrote:Very nice.
Thanks :)
onion2k wrote:Isn't your example code going to end up with "(id = 10 or 10 IS NULL)" though? :twisted:
:P yep

Re: String format with variables binding

Posted: Fri Nov 13, 2009 4:29 am
by VladSun
I am wondering, whether it is a good idea to throw an exception if {N} doesn't exist in the $binds index range. Otherwise, it's silent handling (i.e. putting an empty string) will lead to hard to find bugs.
I'm almost sure it's a good idea - what do you think?

Re: String format with variables binding

Posted: Fri Nov 13, 2009 6:04 am
by onion2k
I'd either throw an exception or use a placeholder value like "NOTFOUND" for bindings that are missing. That'd make it easy to debug.

Re: String format with variables binding

Posted: Fri Nov 13, 2009 6:51 am
by VladSun
I'd prefer throwing an exception :)
Code updated.