Building Dynamic SQL String from Associative Array

•  Printer friendly

It would be nice to have functions that convert associative arrays to SQL queries. Such a function will save our time from building queries. The functions take two main parameters, the name of the table and the data to be inserted in associative array like this:

$data = array(
        'name'  => 'dede',
        'email' => 'dede@gmail.com',
        'city'  => 'surabaya',
        'age'   => 21
        );

The array then converted to SQL string:

INSERT INTO members (name, email, city, age)
VALUES ('dede', 'dede@gmail.com', 'surabaya', '21');

The function is simple. First we get the column names using array_keys(), and get the values using array_values(). Both arrays are converted to strings and joined to build the complete SQL string.

<?php
function build_sql_insert($table, $data)
{
    $key = array_keys($data);
    $val = array_values($data);
    $sql = "INSERT INTO $table (" . implode(', ', $key) . ") "
         . "VALUES ('" . implode("', '", $val) . "')";

    return($sql);
}
?>

The function to generate the SQL for update is slightly different. In this function, each pairs of array elements is converted to "key='name'". An additional parameter is added for the WHERE clause.

<?php
function build_sql_update($table, $data, $where)
{
    $cols = array();
    foreach($data as $key=>$val) {
        $cols[] = "$key = '$val'";
    }
    $sql = "UPDATE $table SET " . implode(', ', $cols) . " WHERE $where";

    return($sql);
}
?>

We've got functions to build the SQL for INSERT and UPDATE. Another function for data validation would be perfect.

The validation function takes two arguments. One argument is the data to be inserted, and the second argument is the regular expression for each column. For the example above, the regular expression would be something like this:

$regex = array(
    'name'  => "^[\w.]+$",
    'email' => "^[a-z0-9_\+-]+(\.[a-z0-9_\+-]+)*"
             . "@[a-z0-9-]+(\.[a-z0-9-]+)*\.([a-z]{2,4})$",
    'city'  => "^[\w]+$",
    'age'   => "^[0-9]{1,2}$"
);

And here's the data validation function:

<?php
function is_valid($regex, $data)
{
    $valid = true;

    foreach($regex as $field=>$re) {
        if (isset($data[$field])) {
            $valid &= preg_match("/$re/", $data[$field], $m);
        }
    }

    return($valid);
}
?>

Put all of the functions above in one file, and make sure that the functions work.

<?php
/* function to build SQL INSERT string */
function build_sql_insert($table, $data)
{
    $key = array_keys($data);
    $val = array_values($data);
    $sql = "INSERT INTO $table (" . implode(', ', $key) . ") "
         . "VALUES ('" . implode("', '", $val) . "')";

    return($sql);
}

/* function to build SQL UPDATE string */
function build_sql_update($table, $data, $where)
{
    $cols = array();
    foreach($data as $key=>$val) {
        $cols[] = "$key = '$val'";
    }
    $sql = "UPDATE $table SET " . implode(', ', $cols) . " WHERE $where";

    return($sql);
}

/* perform data validation */
function is_valid($regex, $data)
{
    $valid = true;
    foreach($regex as $field=>$re) {
        if (isset($data[$field])) {
            $valid &= preg_match("/$re/", $data[$field], $m);
        }
    }
    return($valid);
}

/* and make some test */

$data = array(
        'name'  => 'dede',
        'email' => 'dede@gmail.com',
        'city'  => 'surabaya',
        'age'   => '21'
        );

$where = 'id = 14';

$regex = array(
        'name'  => "^[\w.]+$",
        'email' => "^[a-z0-9_\+-]+(\.[a-z0-9_\+-]+)*"
                 . "@[a-z0-9-]+(\.[a-z0-9-]+)*\.([a-z]{2,4})$",
        'city'  => "^[\w]+$",
        'xxxx'    => "^[\w]+$",
        'age'   => "^[0-9]{1,2}$"
        );
        
if (is_valid($regex, $data)) {
    print "Data valid";
} else {
    print "Data invalid";
}

$sql = build_sql_insert('members', $data);
print $sql;

$sql = build_sql_update('members', $data, $where);
print $sql;

/* 
will print:

Data valid

INSERT INTO members (name, email, city, age) 
VALUES ('dede', 'dede@gmail.com', 'surabaya', '21')

UPDATE members SET name='dede', email='dede@gmail.com', city='surabaya',
age='21' WHERE id = 14
*/
?>

Keywords: SQL query, dynamic SQL, array2sql, SQL from associative array

Share:   Save to del.icio.us   Digg this!