Building Dynamic SQL String from Associative Array

Nov 7, 2008 | Tags: PHP | del.icio.us del.icio.us | digg Digg

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.

Listing 1: Build SQL INSERT

  1. <?php
  2. function build_sql_insert($table, $data)
  3. {
  4.     $key = array_keys($data);
  5.     $val = array_values($data);
  6.     $sql = "INSERT INTO $table (" . implode(', ', $key) . ") "
  7.          . "VALUES ('" . implode("', '", $val) . "')";
  8.  
  9.     return($sql);
  10. }
  11. ?>

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.

Listing 2: Build SQL UPDATE

  1. <?php
  2. function build_sql_update($table, $data, $where)
  3. {
  4.     $cols = array();
  5.     foreach($data as $key=>$val) {
  6.         $cols[] = "$key = '$val'";
  7.     }
  8.     $sql = "UPDATE $table SET " . implode(', ', $cols) . " WHERE $where";
  9.  
  10.     return($sql);
  11. }
  12. ?>

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:

Listing 3: Data validation

  1. <?php
  2. function is_valid($regex, $data)
  3. {
  4.     $valid = true;
  5.  
  6.     foreach($regex as $field=>$re) {
  7.         if (isset($data[$field])) {
  8.             $valid &= preg_match("/$re/", $data[$field], $m);
  9.         }
  10.     }
  11.  
  12.     return($valid);
  13. }
  14. ?>

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

Listing 4: All in one

  1. <?php
  2. /* function to build SQL INSERT string */
  3. function build_sql_insert($table, $data)
  4. {
  5.     $key = array_keys($data);
  6.     $val = array_values($data);
  7.     $sql = "INSERT INTO $table (" . implode(', ', $key) . ") "
  8.          . "VALUES ('" . implode("', '", $val) . "')";
  9.  
  10.     return($sql);
  11. }
  12.  
  13. /* function to build SQL UPDATE string */
  14. function build_sql_update($table, $data, $where)
  15. {
  16.     $cols = array();
  17.     foreach($data as $key=>$val) {
  18.         $cols[] = "$key = '$val'";
  19.     }
  20.     $sql = "UPDATE $table SET " . implode(', ', $cols) . " WHERE $where";
  21.  
  22.     return($sql);
  23. }
  24.  
  25. /* perform data validation */
  26. function is_valid($regex, $data)
  27. {
  28.     $valid = true;
  29.     foreach($regex as $field=>$re) {
  30.         if (isset($data[$field])) {
  31.             $valid &= preg_match("/$re/", $data[$field], $m);
  32.         }
  33.     }
  34.     return($valid);
  35. }
  36.  
  37. /* and make some test */
  38.  
  39. $data = array(
  40.         'name'  => 'dede',
  41.         'email' => 'dede@gmail.com',
  42.         'city'  => 'surabaya',
  43.         'age'   => '21'
  44.         );
  45.  
  46. $where = 'id = 14';
  47.  
  48. $regex = array(
  49.         'name'  => "^[\w.]+$",
  50.         'email' => "^[a-z0-9_\+-]+(\.[a-z0-9_\+-]+)*"
  51.                  . "@[a-z0-9-]+(\.[a-z0-9-]+)*\.([a-z]{2,4})$",
  52.         'city'  => "^[\w]+$",
  53.         'age'   => "^[0-9]{1,2}$"
  54.         );
  55.        
  56. if (is_valid($regex, $data)) {
  57.     print "Data valid";
  58. } else {
  59.     print "Data invalid";
  60. }
  61.  
  62. $sql = build_sql_insert('members', $data);
  63. print $sql;
  64.  
  65. $sql = build_sql_update('members', $data, $where);
  66. print $sql;
  67.  
  68. /*
  69. will print:
  70.  
  71. Data valid
  72.  
  73. INSERT INTO members (name, email, city, age)
  74. VALUES ('dede', 'dede@gmail.com', 'surabaya', '21')
  75.  
  76. UPDATE members SET name='dede', email='dede@gmail.com', city='surabaya',
  77. age='21' WHERE id = 14
  78. */
  79. ?>

Related Articles

5 Comments

Alex on Aug 17, 2008:

Your blog is interesting!

Keep up the good work!

Giuseppe Vigo on Nov 7, 2008:

Thanks for build_sql_insert, very very cool!

ferdy on Feb 1, 2009:

Can something similar be done with any assoc multidimensional array ?

Julian Davchev on Feb 15, 2009:

Examples given are quite wrong and promote sql injection. There should be clear destinction what validation and escaping is. Escaping should always be done and regular expressions is not the right approach when considering db context. Assuming db is mysql the use of mysql_real_escape_string function is the very least that should be used. For more on sql injection read http://www.webappsec.org/projects/articles/091007.shtml

rex on Jun 28, 2010:

thnx... good work... really really good... thnx

Leave a comment

Name (required)
Email (will not be published) (required)
Website

Characters left = 1000

Tags

Correlation  HTTP  Image  Networking  OpenCV  PHP  Projects  Regex  SEO  Security  WebService 

Recent Posts

  1. OpenCV Utility: Reading Image Pixels Value
  2. OpenCV Circular ROI
  3. OpenCV 2.0 Installation on Windows XP and Visual Studio 2008
  4. Runtime ROI Selection using Mouse
  5. Real Time Eye Tracking and Blink Detection
View Archives

About the Author

avatar Cool PHP programmer writing cool PHP scripts. Feel free to contact
Tel. +62 31 8662872
+62 856 338 6017
ICQ 489571630
Skype dede_bl4ckheart
Yahoo dede_bl4ckheart
Google nashruddin.amin

Recommended Sites:

Hacker's HTTP Client
HTML and CSS Tutorials
Stop Dreaming Start Action
Online Quran and Translation