String format with variables binding

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

Moderator: General Moderators

Post Reply
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

String format with variables binding

Post 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
Last edited by VladSun on Wed Jul 31, 2013 2:42 am, edited 8 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: String format with variables binding

Post by onion2k »

Very nice.

Isn't your example code going to end up with "(id = 10 or 10 IS NULL)" though? :twisted:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: String format with variables binding

Post 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
Last edited by VladSun on Thu Apr 16, 2020 7:26 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: String format with variables binding

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: String format with variables binding

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: String format with variables binding

Post by VladSun »

I'd prefer throwing an exception :)
Code updated.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply