Database Code
  Home arrow Database Code arrow MySQL table DB PHP code generator
Codewalker Forums 
  Tutorials  
Database Articles  
Miscellaneous  
Navigation Usability  
PEAR Articles  
Programming Basics  
Server Administration  
XML Tutorials  
  Reviews  
Database Book Reviews  
Linux Book Reviews  
Miscellaneous Reviews  
PHP Book Reviews  
PHP Software Reviews  
Server Admin Reviews  
SQL Tool Reviews  
  Code Gallery  
Content Management Code  
Contest Code  
Counters Code  
Database Code  
Date Time Code  
Discussion Board Code  
Email Code  
File Manipulation Code  
GUI Code  
Link Farm Code  
Miscellaneous Code  
Search Code  
Site Navigation Code  
User Management Code  
Forums Sitemap 
Dedicated Servers  
Download TestComplete 
JMSL Numerical Library 
IBM® developerWorks
Weekly Newsletter 
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE CODE

MySQL table DB PHP code generator
By: Codewalkers
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 3
    2004-08-20

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    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

     

    IBM® developerWorks developerWorks - FREE Tools!


    Check out the new Jazz space on developerWorks

    <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!


    NEW! Applying lean thinking to the governance of software development

    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!


    NEW! Best practices for software analysis: An introduction to the IBM Rational Software Analyzer application

    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!


    NEW! Best Practices in Integrated Requirements Management

    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!


    NEW! BlammoSplat: Build a community Web site of OpenLaszlo animations, Part 3: The community animation

    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!


    NEW! Improve your build process with IBM Rational Build Forge, Part 2: Automate builds for a real-world Tomcat project

    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!


    NEW! Rational 'Talks to You' Teleconference Series

    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!


    NEW! Rational Talks to You: Scott Ambler on being agile in a global development environment

    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!


    NEW! Run your first CICS application on a PC using TXSeries for Windows

    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!


    NEW! Webcast: What is new in Viper 2 for developers?

    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!

    DATABASE CODE ARTICLES

    - Examples and Tools for Database Design
    - Relationships, Entities and Database Design
    - Modeling and Designing Databases
    - Data extract to Excel
    - Oracle database class 0.76
    - The opposite of mysql_fetch_assoc
    - On line Thermal Transmitance Calculation
    - pjjTextBase
    - PHP Object Generator
    - FastMySQL
    - RC4PHP
    - SQL function with integrated sprintf()
    - DB Interaction Classes v1.1
    - deeMySQLParser
    - CSV to SQL convertor






    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 5 hosted by Hostway