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:anto...@gmail.com] 
> 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

Reply via email to