Multi-dimensional arrays generally indicate some kind of glue/hanging table, so
you'll have to special case them for your needs...
But here is a generic insert and update functions that may be a groundwork for
you....
/**
* Insert a single row into a $database.$table from an array hash of column =>
value pairs
*
* Usually these are the form field names corresponding to the equivallent SQL
database column/field name.
* Use the string 'null' to insert a true NULL.
*
* @access public
* @return mixed inserted ID on success or result on failure
* @param string $database the database to connect to (agis_core) is the
default
* @param string $table the name of the table to insert into
* @param hash $rows hash of column => value pairs (optionally columns
validated against $validate_columns)
* @param array $valid_columns array of column/field names. Also useful
to limit SQL to certain forced columns to prevent
unwanted tampering with 'default' columns for example.
* @author Daevid Vincent
* @date 11/23/09
* @see sql_update(), sql_insert_id()
*/
function sql_insert($database, $table, $rows, $valid_columns=null)
{
ksort($rows); //not required, just easier to debug and find appropriate
keys.
$validate_columns = (is_array($valid_columns)) ? true : false;
$temp = array();
$arrays = array();
foreach ($rows as $column => $val)
{
if (is_array($val))
{
$arrays[$column] = $val;
unset($rows[$column]);
continue;
}
if ($validate_columns && !in_array($column, $valid_columns))
{
unset($rows[$column]);
continue;
}
$val = trim($val);
if (!$val)
{
unset($rows[$column]);
continue;
}
if (strtolower($val) == 'null')
$temp[$column] = 'NULL';
else
$temp[$column] = "'".mysql_escape_string($val)."'";
}
$values = implode(', ',$temp);
$columns = "`".implode("`, `", array_keys($rows))."`";
$sql = "INSERT INTO `".$table."` (".$columns.") VALUES (".$values.")";
//echo $sql;
if (count($arrays))
echo "\n<br/>sql_insert() has arrays that need to be handled
still: ".implode(', ', array_keys($arrays));
$result = sql_query($database, $sql, null, false);
if ($result)
{
$iid = sql_insert_id();
if ($iid) return $iid;
}
return $result;
}
/**
* Update rows in $database.$table from an array hash of column => value pairs
*
* Usually these are the form field names corresponding to the equivallent SQL
database column/field name.
* Use the string 'null' to insert a true NULL.
*
* @access public
* @return mixed affected rows on success or result on failure
* @param string $database the database to connect to (agis_core) is the
default
* @param string $table the name of the table to insert into
* @param hash $rows hash of column => value pairs (optionally columns
validated against $validate_columns)
* @param mixed hash of ID column/field name and record ID value [such
as array('id_foo' => 69)] OR string to craft custom
WHERE clause
* @param array $valid_columns array of column/field names. Also useful
to limit SQL to certain forced columns to prevent
unwanted tampering with 'default' columns for example.
* @author Daevid Vincent
* @date 11/23/09
* @see sql_insert()
*/
function sql_update($database, $table, $rows, $where, $single=true,
$valid_columns=null)
{
ksort($rows); //not required, just easier to debug and find appropriate
keys.
$validate_columns = (is_array($valid_columns)) ? true : false;
$temp = array();
$arrays = array();
foreach ($rows as $column => $val)
{
if (is_array($val))
{
$arrays[$column] = $val;
unset($rows[$column]);
continue;
}
if ($validate_columns && !in_array($column, $valid_columns))
{
unset($rows[$column]);
continue;
}
$val = trim($val);
if (!$val)
{
unset($rows[$column]);
continue;
}
if (strtolower($val) == 'null')
$temp[$column] = '`'.$column."` = NULL";
else
$temp[$column] = '`'.$column."` =
'".mysql_escape_string($val)."'";
}
$sql = "UPDATE `".$table."` SET ".implode(', ', $temp);
if (is_array($where))
{
foreach ($where as $c => $v)
$w[] = '`'.$c."` = '".mysql_escape_string($v)."'";
$sql .= " WHERE ".implode(' AND ', $w);
}
else $sql .= ' '.$where;
if ($single) $sql .= ' LIMIT 1';
//echo $sql;
if (count($arrays))
echo "\n<br/>sql_update() has arrays that need to be handled
still: ".implode(', ', array_keys($arrays));
$result = sql_query($database, $sql, null, false);
if ($result)
{
$ar = sql_affected_rows($database);
if ($ar) return $ar;
}
return $result;
}
> -----Original Message-----
> From: Anton Heuschen [mailto:[email protected]]
> Sent: Friday, December 04, 2009 5:10 AM
> To: PHP General List
> Subject: [PHP] Good SQL builder class
>
> Good day.
>
> I'm looking for a good class to handle building dynamically from and
> array (and if it is good it will automatically determine / or even
> have different methods) to handle mutli-dimensional arrays or simple
> associative arrays ... and build the SQL statement :
>
> for example I have an array :
>
> $home[$suburb]["street"] = test1;
> $home[$suburb]["housenr"] =2;
>
>
> Ok to keep it simple to 2, then I want to build the SQL like
>
> insert into homes (STREET, HOUSENR) VALUES ($val1,$val2);
>
>
> something like that, but I could also pass some array like :
>
> $home["street"] = test2;
> $home["housenr"] = 2;
>
>
> but the idea stays the same = the index is the name of the DB fields
> and the assigned value the element
>
>
>
> I have looked on hotscripts and phpclasses but I have no idea how good
> the solutions are that I have found thus far - therefor need some
> recommendation from someone else past experience of this
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
