| PHP MySQL facilitator
|
kajje
msg:4106832 | 7:41 am on Mar 30, 2010 (gmt 0) |
Because I'm working with a database with a lot of fields, and my database structure is not 100% finished, I was looking for a way to design my queries a little bit more visual.
I wanted to share this with the community.
Is this approach useful for you, or is it pure cr@p, is this extremely dangerous or can it be enhanced?
All thoughts are more than welcome!
<?
$fillarray = array
(
"INSERT INTO", $db_name,$db_table,
"id" ,"NULL",
"somefield",$somefield,
"otherfield",$_POST['otherfield'],
"specialfield",$_POST['specialfield'],
"date","NOW",
"comments","This is a test",
);
$db_query= lib_mysqltools_fillarray2query($fillarray,1);
$db_result= mysql_query($db_query) or die(mysql_error());
function lib_mysqltools_fillarray2query($fillarray,$security)
{
// STRUCTURE
$return = $fillarray[0]." `".$fillarray[1]."`.`".$fillarray[2]."`\n(\n";
$count_total = count($fillarray);
$counter = 3;
while ($counter < $count_total)
{
$return .= "`".$fillarray[$counter]."`";
if ($counter < $count_total -2)
{
$return .= ",\n";
}
else
{
$return .= "\n";
}
$counter = $counter +2;
}
$return .= ")";
//VALUES
$return .= "VALUES\n(\n";
$count_total = count($fillarray);
$counter = 4;
while ($counter <= $count_total)
{
if ($fillarray[$counter] == "NULL")
{
$return .= "NULL";
}
elseif ($fillarray[$counter] == "NOW")
{
date_default_timezone_set('Asia/Makassar');
$return .= "\"".date("Y-m-d H:i:s")."\"";
}
else
{
if ($security == 1)
{
$protected = sprintf ("%s",mysql_real_escape_string($fillarray[$counter]));
}
else
{
$protected = $fillarray[$counter];
}
$return .= "'$protected'";
}
if ($counter < $count_total -2)
{
$return .= ",\n";
}
else
{
$return .= "\n";
}
$counter = $counter +2;
}
$return .= ");\n";
return $return;
}
?>
|
whoisgregg
msg:4118782 | 1:40 pm on Apr 20, 2010 (gmt 0) |
It definitely helps to have a database class or set of functions to make database manipulation easier. However, the method you demonstrate of providing a numeric indexed array of the different statements is a bit frail. All it would take is one missing value and all your fields/values will be out of sync.
An approach like this might make more sense:
function sql_insert_string($db_table, $array){
$keys = array_map("mysql_real_escape_string", array_keys($array));
$values = array_map("mysql_real_escape_string", array_values($array));
$sql = 'INSERT INTO `'.$db_table.'` (`'.implode('`,`', $keys).'`) VALUES (\''.implode('\',\'', $values).'\');';
return $sql;
}
$insertArray = array(
'column1' => 'valueA',
'column2' => 'valueB',
);
echo sql_insert_string('some_table', $insertArray); |
|
This example is just a basic one to demonstrate the concept (I haven't even tested the code). You'd actually need something more complex to handle NULL values and any other exceptions.
|
|
|
