Hi All,
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
//------------------------------------------------------------------------
function show_form()
{
echo "
<html><body>
<form action=".$_SERVER['PHP_SELF']." method=post>
<table>
<tr><td>Table Name:</td><td><input type='text' name='tablename' size='25'></td></tr>
<tr><td>DB Name:</td><td><input type='text' name='dbname' size='25'></td></tr>
<tr><td>User Name:</td><td><input type='text' name='uname' size='25'></td></tr>
<tr><td>Password:</td><td><input type='text' name='pass' size='25'></td></tr>
<tr><td>Host:</td><td><input type='text' name='host' size='25'></td></tr>
<tr><td>Get / Post:</td><td><select name='gp_type'>
<option value='_POST'>Post
<option value='_GET'>Get
</select>
</td></tr>
<tr><td>Require addslashes / stripslashes:</td><td><input type='checkbox' alt='click to add' name='slashes' value='yes'>
<tr><td colspan=2><input type='submit' name='submit' value='generate scripts'></td></tr>
</table>
</form>
</body>
</html>";
}
//------------------------------------------------------------------------
// generate code function
//------------------------------------------------------------------------
function generate_scripts()
{
global $dbname;
//initialize variables
$table_name = '';
$dbname = '';
$uname = '';
$pass = '';
$host = '';
$type = '';
$slashes = '';
$pk_id = 0;
$pk_num = 0;
$sql = '';
$update_query = '';
$insert_query = '';
$cnt = 0;
$my_global = 'global ';
//get form data
$table_name = $_POST['tablename'];
$dbname = $_POST['dbname'];
$uname = $_POST['uname'];
$pass = $_POST['pass'];
$host = $_POST['host'];
$type = $_POST['gp_type'];
if (isset($_POST['slashes'])) $slashes = $_POST['slashes'];
$numeric_field_types_array = array('int','tin','flo','dec','big, dou','sma','med');
//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";
for ($x=0; $x < $cnt; $x++){
echo "\$$fields[$x] = \"$defaults[$x]\";<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
}// end for
$update_query = substr($update_query, 0, strlen($update_query)-2);
//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. |
More Database Code Articles
More By Codewalkers
 developerWorks - FREE Tools!
|
<a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts.
FREE! Go There Now!
|
|
|
|
Effective governance for lean development isn’t about command and control. Instead, the focus is on enabling the right behaviors and practices through collaborative and supportive techniques. Hear from Scott Ambler on how it is far more effective to motivate people to do the right thing than it is to force them to do so. Learn how to form a lightweight, collaboration-based framework that reflects the realities of modern IT organizations.
FREE! Go There Now!
|
|
|
|
This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer.
FREE! Go There Now!
|
|
|
|
Poor Requirements Management capabilities in an Enterprise have been linked to excessive project failures, escalating IT costs, and failure to deliver competitive advantage into the marketplace. Join Brianna M Smith from IBM Rational and learn about how successful organizations align IT and Business stakeholders through collaborative processes and tools for effective requirements management, and how an integrated approach across the IT lifecycle can provide unparalleled visibility and traceability to ensure that project teams are delivering on the business vision by "doing the right things" and "doing things right."
FREE! Go There Now!
|
|
|
|
Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo.
FREE! Go There Now!
|
|
|
|
Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available.
FREE! Go There Now!
|
|
|
|
This Fall, IBM Rational talks to you directly through a special teleconference series giving you access to the best minds in IBM Rational - product experts and market thought leaders who will answer your questions during these pre-scheduled telephone conference calls. Register today!
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
FREE! Go There Now!
|
|
|
|
Learn the basics of the IBM Customer Information Control System (CICS). With a hands-on exercise, learn how to get your first CICS application up and running on your desktop using TXSeries V6.1 for Windows. The tutorial shows you how to download and install a free trial version of TXSeries V6.1.
FREE! Go There Now!
|
|
|
|
Viper 2 brings a great value to developer communities including SQL, XML, PHP, Ruby, .NET and Java. You probably already know that DB2 Express-C is free for developers to develop, deploy and distribute. Viper 2 provides a variety of means that help move your application from the development stage to deployment more rapidly. This webcast shows how to best utilize the latest tools available for developing DB2 applications.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
