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!
|
Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan.
FREE! Go There Now!
|
|
|
|
As businesses grow increasingly dependent upon Web applications to provide services to customers, employees and partners, these complex applications become more difficult to secure. Although traditional security solutions protect Internet infrastructure layers, they do not guard against HTTP and HTML attacks. Many organizations that conduct security testing still deploy applications that allow attackers to manipulate their logic and wreak havoc on their business. To mitigate this risk, development and delivery teams must address Web application security throughout the lifecycle, addressing the many layers detailed in this paper.
FREE! Go There Now!
|
|
|
|
Achieving true agility is a never-ending effort. We will showcase how you can become agile incrementally, a few practices at the time.Which practices should any agile team strive to adopt? What additional practices should you consider based on your needs to scale? Adopting practices are however made much easier with the right tool support. What about if your tools adapt to your practices? We will take a look at how the Jazz technology can be leveraged to make your process change the behavior of your tools.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download IBM DB2 Express-C 9.5, a no-charge version of DB2 Express 9 database server. DB2 Express-C offers the same core data server base features as other DB2 Express editions and provides a solid base to build and deploy applications developed using C/C++, Java, .NET, PHP, and other programming languages.
FREE! Go There Now!
|
|
|
|
Download a free trial version of IBM Rational Developer for System i V7.1, which provides a complete development environment for traditional i5/OS application development. IBM Rational Developer for System i is a new eclipse-based workstation offering for i5/OS application development that provides a comprehensive Integrated Development Environment for edit/compile/debug of traditional RPG/COBOL/C/C++ i5/OS applications.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial of the Rational Host Access Transformation Services (HATS) Toolkit. The HATS toolkit provides a set of plug-ins for the IBM Rational Software Delivery Platform to help you easily extend your legacy applications. HATS makes your 3270 and 5250 applications available as HTML through the most popular Web browsers, while converting your host screens to a Web look and feel and it also enables you to develop new Web, portal, and rich-client applications.
FREE! Go There Now!
|
|
|
|
This tutorial shows new users of IBM WebSphere Business Monitor Version 6.0.2 how to perform the "Hello World" equivalent for monitoring business process applications. It is intended to help you get familiar with the capabilities of the product.
FREE! Go There Now!
|
|
|
|
Informix Dynamic Server (IDS) Express Edition offers outstanding online transaction processing (OLTP) database performance, while helping to simplify and automate many of the tasks associated with deploying databases for small business applications. IDS 11 further extends the ease of management and applications integration with the Admin API and Scheduler, high availability with Continuous Log Restore for backup server recovery in case of a primary server failure, and column level encryption to protect personal and company private data.
FREE! Go There Now!
|
|
|
|
Join the IBM Watchfire team for an informative discussion on techniques and best practices to proactively manage Web application security and how to effectively build application security testing into the software development lifecycle (SDLC). In this Software Delivery Platform webcast you will learn: How to better understand potential web application security vulnerabilities, best practices and how to effectively integrate application security testing into the software development lifecycle, the importance of detecting and removing software vulnerabilities during application development.
FREE! Go There Now!
|
|
|
|
WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
