Automatic Form and Variables Generator

I came to this little article by looking for a solution for following problem;

I have several database tables which consisted of more than 25 fields/columns. before. I handcoded all this in html, but with such a large number of fields, I gathered it should be possible to automate this task.

First I created an “all purpose” general connection script to the mysql backend:

<?php

#this sql.php3 is a general connection and query execution
#script to a MySQL backend

#config.inc includes variables like $host, $user, $dname and
#other general variables

require(“config.inc”);

$open = mysql_connect($host, $user);
#For error checking you can: echo “$open
“;

$open_db = @mysql_select_db($dbname, $open);
#For error checking you can: echo “$open_db
“;

$result = mysql_query($sql, $open_db);
#For error checking you can: echo “$result
“;

?>

Then I decided to wrap the HTML <INPUT> tags from the <FORM> into a <TABLE> and take as field identifiers the respective column name of the database table:

<?php

# This scripts generates an input form for the given table :
# $host/$user/$dbname/$table are fed by a file called config.inc

require(“config.inc”);

$sql = “SELECT * FROM $table”;

$open = mysql_connect($host, $user);
$open_db = @mysql_select_db($dbname, $open);
$result = mysql_query($sql, $open_db);

#Now I have to find out how many fields this table has :

$x = mysql_num_fields($result);

#For errorchecking you can: echo “$x”;

#Here starts the actual script that generates the form

echo “<TABLE COLS = 2 WIDTH = 100% BORDER = 0>”;
echo “<FORM METHOD = POST ACTION = ./ERP/insert.php3>”;
echo “<INPUT TYPE = HIDDEN NAME = table VALUE = $table>”;

for($i = 0 ; $i < $x ; $i++) {
        echo “<TR><TD ALIGN = LEFT><B>”;
        $y = mysql_field_name($result, $i);
        echo “$y”;
        echo “</TD>”;

        echo “<TD>”;
        echo “<INPUT TYPE = TEXT NAME = $y>”;
        echo “</TD></TR>”;
}

echo “<TR><TD ALIGN = CENTER><INPUT TYPE = SUBMIT></TD>”;
echo “<TD ALIGN = CENTER><INPUT TYPE = RESET></TD>”;
echo “</TABLE>”;

?>

For reasons of logic each NAME of the <INPUT> tag is given the same name as the individual fields of the database table.

This will make it easier for us afterwords to construct the general “insert.php3 script” that is invoked by <POST>;

Well, now we have found an easy way of generating a simple input form, especially handy for large database tables.

If we now have to handcode all the HTTP_POST_VARS in the insert script, this would give us quite some work as well (and yes, I’m kind of lazy ..;-))

So following script will generate the variables automatically and correctly for the INSERT query :

<?php

# This scripts handles the insert-query into whatever table

while(list($key, $val) = each($HTTP_POST_VARS)) {

   if($val != “$table”) {
      empty($second) ? $second =
         “VALUES (‘”. $val .”‘ ” : $second .= “,’ ” . $val . “‘ “;
   }

}

$args = $second . “)”;

#for debugging you can: echo “$args
“;

$args = strtoupper($args);

# This puts all your form input into UPPERCASE CHARACTERS
# in the database (just for consistency reasons)

$sql = “INSERT INTO $table $args”;

#for debugging you can: echo “$sql”;

require(“./sql.php3”);   #see above

?>

I hope someone can use these scripts for his or her setup, it works for me … (I also especially would like to thank Chad Cunningham for his help on creating the insert.php3 script (especially the automatic creation of the posted variables array)