Fresh Blurbs

Elegant Way To Format SQL's Where IN For PHP 5.3

It's a pretty common need to format an array as a proper comma-delimited string for use in SQL's "WHERE ... IN" clause. Following is a simple solution that demonstrates how closures in PHP 5.3+ make writing such code pretty elegant. A unit-test is included to demonstrate several edge cases:

function sql_instring($arr) {
    if (empty($arr)) {
        return "IS NULL";
    }

    return "IN (" .
    implode(',',
            array_map(function($arg) {
                if (is_numeric($arg)) {
                    return $arg;
                }
                elseif (is_string($arg)) {
                    return "'$arg'";
                }
                else {
                    throw new Exception('Invalid Argument: must be string or a number.');
                }
            },
            $arr)
    ) .
    ")";
}

class SQLInTest extends PHPUnit_Framework_TestCase {

    function test_instring() {
        $arr = array (1,2,3,4,5);
        $expected = "IN (1,2,3,4,5)";
        $this->assertEquals($expected,sql_instring($arr));        

        $arr = array (1);
        $expected = "IN (1)";
        $this->assertEquals($expected,sql_instring($arr));        

        $arr = array ();
        $expected = "IS NULL";
        $this->assertEquals($expected,sql_instring($arr));        

        $arr = array ('a', 'b', 'c');
        $expected = "IN ('a','b','c')";
        $this->assertEquals($expected,sql_instring($arr));        

    }

}
comments powered by Disqus