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

Re: Good SQL builder class

Take a look at http://www.tonymarston.net/php-mysql/databaseobjects.html

You can also download a working example of this code from
http://www.tonymarston.net/php-mysql/sample-application.html

--
Tony Marston
http://www.tonymarston.net
http://www.radicore.org

"Anton Heuschen" wrote in message
news:e6d501390912040509k1b27f082mc22ff95d460048bd@mail.gmail .com...
> 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

RE: 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
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
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
>


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

Re: Good SQL builder class

I actually spent a great deal of time trying to make purely array based query
builders, and came to the conclusion that it's not possible to make a really
robust one. However, you can do it with OO and arrays.

Have a look at Drupal 7 (current development version of the Drupal CMS). It
includes a new DB layer totally rewritten to use PDO and includes a series of
robust SQL builders. It's also really easy to separate from Drupal; you need
to comment out 2, perhaps 3 lines of code total. At some point I want to spin
it off as a stand-alone project, but for now it's easy to rip out. In fact
it's already been ripped out and backported to Drupal 6, so you can grab the
code from there (GPL) and tweak as needed.

http://drupal.org/project/dbtng

On Friday 04 December 2009 7:09:55 am Anton Heuschen wrote:
> 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

--
Larry Garfield
larry@garfieldtech.com

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