Welcome to WebmasterWorld Guest from 54.196.223.42

Forum Moderators: open

Message Too Old, No Replies

PHP MySQL facilitator

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

New User

5+ Year Member

joined:Jan 8, 2010
posts:18
votes: 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;
}
?>
1:40 pm on Apr 20, 2010 (gmt 0)

Senior Member from US 

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

joined:Dec 9, 2003
posts:3416
votes: 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.