I built a multi-purpose query builder for INSERT and UPDATE statements.  
You send it certain and it sends back the SQL, minus the WHERE clause for 
the UPDATE SQL.  I thought it would be dangerous to leave it without any 
WHERE clause because if you forgot to add one, you'd end up doing that 
UPDATE on every row in the table which is generally not good.  I still 
haven't decided how I want to handle the WHERE clause thing, but for now 
there's a placeholder that I do a str_replace() on after I call the 


$table - name of table that's being inserted into or updated
$arr - associative array of values.  key is the column/field name and value 
is the value it'll be set to.
$dateupdates - an array with a list of values corresponding to the date 
fields I want updated to NOW()
$type - whether it's an insert or an update

The dbclean() function is one I wrote so I don't have to go through all my 
code changing mysql_real_escape_string() to something else or more 
specific if I need to updated how I clean the data going into the 
database and/or used in queries.  In the past, I've also passed a "type" 
variable to this as well, indicating the type of data and then doing 
specific things depending on the type, but I'm rebuilding my common 
functions from scratch and haven't gotten to that part yet.

Here ya go, in case it helps:

function dbBuildIUQuery($table = '', $arr = array(), $dateupdates = 
array(), $type = '') {

    $query = '';

    switch ($type) {
        case 'insert':
            $query = "INSERT INTO " . dbclean($table) . " (";
            $queryvalues = ") VALUES (";

            $arrkeys = array_keys($arr);
            $arrvals = array_values($arr);

            foreach($arrkeys as $key => $val) {
                $arrkeys[$key] = "`" . dbclean($val) . "`";
            foreach($arrvals as $key => $val) {
                $arrvals[$key] = "'" . dbclean($val) . "'";

            foreach ($dateupdates as $key) {
                $arrkeys[] = '`' . dbclean($key) . '`';
                $arrvals[] = 'NOW()';

            $query       .= implode(',', $arrkeys);
            $queryvalues .= implode(',', $arrvals) . ");";

            $query .= $queryvalues;

        case 'update':

            $query = "UPDATE " . dbclean($table) . " SET ";
            $queryvalues = array();
            foreach ($arr as $key => $val) {
                $queryvalues[] = " `" . dbclean($key) . "` = '" . 
dbclean($val) . "'";
            $query .= implode(',', $queryvalues) . ' WHERE {whereclause};'; 
 // Added {whereclause} so if we forget to add one, the query fails 
without setting all rows to these values


    return $query;


----- Original Message -----
From: Ben Stones <b3n...@googlemail.com>
To: php-general@lists.php.net
Date: Sun, 14 Feb 2010 13:18:06 +0000
Subject: [PHP] Inserting Associative array values into a MySQL INSERT 

> Hi,
> I want to be able to create a function that acts as an insert mysql 
> that accepts specific parameters for the fields and the values I want to
> insert into those respective fields and I know I'll need to use 
> arrays to complete this task when passing values to the function, but I'm
> not sure how to pass multiple values in an array through an insert
> statement? Any help greatly appreciated!
> Thanks.

PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to