Welcome to WebmasterWorld Guest from 107.21.175.43

Forum Moderators: open

Message Too Old, No Replies

PHP MySQL facilitator

     

kajje

7:41 am on Mar 30, 2010 (gmt 0)

5+ Year Member



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

1:40 pm on Apr 20, 2010 (gmt 0)

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month