Welcome to WebmasterWorld Guest from 54.167.46.29

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

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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members