homepage Welcome to WebmasterWorld Guest from 54.211.34.105
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
PHP MySQL facilitator
kajje



 
Msg#: 4106830 posted 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

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



 
Msg#: 4106830 posted 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved