RE: [PHP] Good SQL builder class

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:antonfh@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 >