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
function.
Parameters:
$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;
break;
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
break;
default:
break;
}
return $query;
}
-TG
----- Original Message -----
From: Ben Stones <b3ns93@xxxxxxxxxxxxxx>
To: php-general@xxxxxxxxxxxxx
Date: Sun, 14 Feb 2010 13:18:06 +0000
Subject: Inserting Associative array values into a MySQL INSERT
statement?
> Hi,
>
> I want to be able to create a function that acts as an insert mysql
function
> 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
associative
> 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
