Generating SQL Insert Queries

22 February 2007

Compared to SELECT, UPDATE or DELETE, the SQL Insert query is arguably the hardest to code. Admittedly, most apps are going to have an established ORM or other DB access layer, but one goal of this site is to show features for which PHP is not particularly well known.

Given an associative array in the form $row[$field] = $value;, how can you write an insert query?


function getInsertSql ($table, $row) {
   return "INSERT INTO `$table` (" .
      implode(",",
         array_map (
            create_function ('$a', 'return "`$a`";'),
            array_keys ($row))) .
      ") VALUES (" .
         implode(",",
            array_map (
               create_function ('$a', 'return "\"" . mysql_real_escape_string ($a) . "\"";'),
               $row)) .
      ")";
}

An insert query generated in a single line of code. This is obviously more proof of concept than anything. Single each create_function persists through the life of the application, it will consume much more memory than a more conventional INSERT generator would, especially if this is called in a loop.

The point, though, is to demonstrate a case of PHP being used closer to LISP (right down to the indentation!)

2 Comments currently posted.

Knut Vidar Siem says:

With MySQL, I find that using the “INSERT … SET” syntax (supported from version 3.22.10) allows for both easier statement generation and debugging: http://dev.mysql.com/doc/refman/5.1/en/insert.html. The code for this can be reused for UPDATE statements.

This was more of a side note.

Alex says:

Thank You

Implicit Evaluation with PHP

22 February 2007