The most tedious part of coding any db interaction is the defining/initialization of the variables that interact with the db. All those POST/GET elements, the global elements and the development of the sql statements.
This code takes the work out of that. Simply fill in the form to point it to a database and table and run it...the output shown is to :
1. initialize all db variables 2. generate the POST/GET values from the forms. 3. generate the global variables 4. generate the insert statement 5. generate the update statement
It will look for primary keys, adapt to place quotes only around the text elements, and fill in the default db values where needed.
There is always room for improvement, but this really reduces the workload when working on pages that map to one table.
You can copy the code generated from the screen, or for a little more formatted code, view source, copy and remove the tags
enjoy,
bastien
By : bastien
<? /* bastien koert Aug 2004 www.bastienkoert.net
This code writes out all the needed DB fields for insert / update statements as well as generating the $global code, the post/get code and the initialization code with defaults from the db tables
*/
//control code if(!isset($_POST['submit'])){ show_form(); }else{ generate_scripts(); }//end if
//------------------------------------------------------------------------ // show form function //------------------------------------------------------------------------
//sql statement $sql = "show columns from $dbname.$table_name";
//connection info if (!($conn=mysql_connect($host, $uname, $pass))) { printf("error connecting to DB by user = $uname and pwd=$pass"); exit; }
$db=mysql_select_db($dbname,$conn) or die("Unable to connect to database1");
//run query $result = mysql_query($sql, $conn)or die("Unable to query local database <b>". mysql_error()."</b><br>$sql");
if (!$result){ echo "database query failed. try again"; show_form(); die(); }// end if
//do the results and generate the code while ($rows = mysql_fetch_array($result)){
//get the data set and stick into a set of arrays $fields[] = $rows[0]; $types[] = $rows[1]; $keys[] = "". $rows[3]; $nulls[] = "". $rows[2]; $defaults[] = "". $rows[4]; $extras[] = "". $rows[5]; } $cnt = count($fields); //get the primary key for the table foreach($keys as $key => $value){ if ($value=="PRI"){ $pk_id = $key; if (strtolower(substr($types[$pk_id], 0, 6)) != "varcha"){ $pk_num = true; }else{ $pk_num = false; }// end if } // endfor }// end foreach
//get the initial variabales echo "\n\n<p><h3>setting initial variables</h3><br>\n";
echo "\n\n<p><h3>setting post/get values</h3><br>\n"; for ($x=0; $x < $cnt; $x++){ if ($slashes=="yes"){ echo "\$$fields[$x] = addslashes(@\$".$type."['$fields[$x]']);<br>\n"; }else{ echo "\$$fields[$x] = @\$".$type."['$fields[$x]'];<br>\n"; }// end if } // end for
//get the insert statement echo "\n\n<br><p><h3>setting insert statement</h3><br>\n";
$insert_query = " insert into $table_name (";
for ($x=0; $x < $cnt; $x++){
$insert_query .= "$fields[$x], ";
}// end for $insert_query .= ") values (";
for ($x=0; $x < $cnt; $x++){
if (in_array(substr($types[$x],0,3), $numeric_field_types_array)){ $insert_query .= "\$$fields[$x], "; }else{ $insert_query .= "'\$$fields[$x]', "; }// end if
}// end for $insert_query = substr($insert_query, 0, strlen($insert_query)-2) . ");"; echo $insert_query;
//get the update statement echo "\n\n<br><p><h3>setting update</h3><br>\n";
$update_query = "update $table_name set ";
for ($x=0; $x < $cnt; $x++){
if (in_array(substr($types[$x],0,3), $numeric_field_types_array)){ $update_query .= "$fields[$x]=\$$fields[$x], "; }else{ $update_query .= "$fields[$x]='\$$fields[$x]', "; }// end if
//rows id'd by pprimary key if ($pk_num == true){ $update_query .= " where $fields[$pk_id] = \$$fields[$pk_id]"; }else{ $update_query .= " where $fields[$pk_id] = '\$$fields[$pk_id]'"; }//end if
echo $update_query;
//get the primary key for the table echo "\n\n<br><p><h3>setting global variables</h3><br>\n";
for ($x=0; $x < $cnt; $x++){
$my_global .= "\$$fields[$x], ";
} // end for $my_global = substr($my_global,0,strlen($my_global) - 2); echo "$my_global;";
//get the editable values from the db echo "\n\n<br><p><h3>getting edit variables</h3><br>\n ";
for ($x=0; $x < $cnt; $x++){
if ($slashes=="yes"){ echo "\$$fields[$x] \t\t= stripslashes(\$row['$$fields[$x]']);<br>\n "; }else{ echo "\$$fields[$x] \t\t= \$row['$$fields[$x]'];<br>\n "; }// end if
}// end for
}// end function ?>
DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.
