damn reply-all On 4/25/08, Ray Hauge <ray.hauge.lists@xxxxxxxxx> wrote: > > Jay Blanchard wrote: > >> I did a quick and dirty of just one of those functions. This function >> takes a table and creates a form based on the table. It needs quite a >> bit of refining, but I am willing to share and let you guys and gals >> throw suggestions; >> > > I use a very similar style of approach, but it's more on a field by field > basis rather than a whole form. I also wrote mine on company time, so > here's an example: > > <?php $table = 'customers'; ?> > <form id='search' name='search' action='' method='post'> > <table align='center'> > <tr> > <td align='right'><?php echo UI::getFieldLabel($table, > 'first_name'); ?></td> > <td colspan='3'><?php echo UI::getBlankTextField($table, > 'first_name'); ?></td> > </tr> > <tr> > <td align='right'><?php echo UI::getFieldLabel($table, > 'city'); ?></td> > <td><?php echo UI::getBlankTextField($table, 'city'); > ?></td> > </tr> > <tr> > <td align='right'><?php echo UI::getFieldLabel($table, > 'state'); ?></td> > <td><?php echo UI::getBlankSelectField($table, 'state'); > ?></td> > <td align='right'><?php echo UI::getFieldLabel($table, > 'zip'); ?></td> > <td><?php echo UI::getBlankTextField($table, 'zip'); ?></td> > </tr> > </table> > </form> > > The UI class figures out the max-length of the field, and there are also > configurations to determine what type of field it is (text, phone, date, > textarea, etc.) and security settings based on the user logged in. This has > helped keep my template files very clean, and I still have full control over > the form by not including the fields I don't want. > > -- > Ray Hauge > www.primateapplications.com > > > <? /* 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 http://codewalkers.com/seecode/494.html 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 10 September 2005 -------------------------------------------------- A few tweaks added by a much lazier guy than Bastien... - a place to set db variables for that db you're always using - generation of a SELECT query - PHP code snippets for extracting ONE Value and MANY values - checkbox to enable htmlspecialchars protection - generation of a table-formatted form in two versions: - all of it tucked into a single php variable - html with embedded php variables - checkbox to color every other row in the table - checkbox to add a "t" to form variable names AND retrieved vari names (useful when form used to set session variables... which CANNOT be directly by a form so you have to give them another name first) - handy table format Unfortunately, the option to view the source for better formatted code not an option: to show the HTML I had to convert it to its special characters... source code is therefore a MESS!! Best Regards, Neil A. Garra Owner & Despot for Life www.S2company.com <http://www.s2company.com/> Mind Tools for Tactical Intelligence --------------------------------------------------------------------- */ //control code if(!isset($_POST['submit'])){ show_form(); }else{ generate_scripts(); }//end if //------------------------------------------------------------------------ // show form function //------------------------------------------------------------------------ function show_form() { //================================================= //Variables for your usual MySQL Server // $dbname = ""; $uname = ""; $pass = ""; $host = ""; // //================================================= echo " <html><body> <form action=".$_SERVER['PHP_SELF']." method=post> <table> <tr> <td colspan=2 bgcolor=blue align=center><font size=5 color=yellow><b>PHP-O-Matic</b></font> <tr><td align=right>Table Name:</td><td> <input type='text' name='tablename' value=\"\" size='25'></td></tr> <tr><td align=right>DB Name:</td><td> <input type='text' name='dbname' value=\"$dbname\" size='25'></td></tr> <tr><td align=right>User Name:</td><td> <input type='text' name='uname' value=\"$uname\" size='25'></td></tr> <tr><td align=right>Password:</td><td> <input type='text' name='pass' value=\"$pass\" size='25'></td></tr> <tr><td align=right>Host:</td><td> <input type='text' name='host' value=\"$host\" size='25'></td></tr> <tr><td align=right>Get / Post:</td><td><select name='gp_type'> <option value='_POST'>Post <option value='_GET'>Get </select> </td></tr> <tr><td align=right>Protect with addslashes / stripslashes: </td><td><input type='checkbox' alt='click to add' name='slashes' value='yes'> <tr><td align=right>Protect with htmlspecialchars: </td><td><input type='checkbox' alt='click to add' name='specs' value='yes' checked> <tr><td align=right>Table Type</td><td><select name='table_type'> <option value='php'>PHP Variable <option value='html'>HTML with embedded variables </select> <tr><td align=right>Table alternate rows colored:</td><td><input type='checkbox' alt='click to add' name='altrows' value='yes' > <input type='text' name='altrowcolor' value=\"#FFFFAA\" size='15'></td></tr> <tr><td align=right>Table has Session Variables:</td><td><input type='checkbox' alt='click to add' name='sesvars' value='yes' > <font size=2>Form variable names AND retrieved values variable names will be preceded by a 't'</font> <tr><td align=center><td ><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']; if (isset($_POST['specs'])) $specs = $_POST['specs']; //GARRA Added $table_type = $_POST['table_type']; //GARRA Added $altrows = $_POST['altrows']; //GARRA Added $altrowcolor = $_POST['altrowcolor']; //GARRA Added $sesvars = $_POST['sesvars']; //GARRA Added $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 "<font size=4 color=red><b>setting initial variables</b></font><br>"; for ($x=0; $x < $cnt; $x++){ $initial_varis .= "\$$fields[$x] = \"$defaults[$x]\";<br>\n"; } //echo $initial_varis; //set post/get variables==================================================== //echo "<p><font size=4 color=red><b>setting post/get values</b></font><br>"; for ($x=0; $x < $cnt; $x++){ //"session varis in table" checked // add a "T" to each INCOMMING vari name if ($sesvars == "yes"){ $addt = "t"; } else { $addt = ""; } if ($slashes=="yes"){ $post_varis .= "\$$fields[$x] = addslashes(@\$".$type."['$addt$fields[$x]']);<br>\n"; } else if ($specs == "yes") {//Garra Addition $post_varis .= "\$$fields[$x] = htmlspecialchars(@\$".$type."['$addt$fields[$x]']);<br>\n"; }else{ $post_varis .= "\$$fields[$x] = @\$".$type."['$addt$fields[$x]'];<br>\n"; }// end if } // end for //echo $post_varis; //SELECT query statement GARRA============================================== $select_query = "\$sql = \"SELECT "; for ($x=0; $x < $cnt; $x++){ $select_query .= "$fields[$x], "; }// end for //strip last comma... $select_query = substr($select_query, 0, strlen($select_query)-2) . "<br>FROM $table_name"; //rows id'd by pprimary key if ($pk_num == true){ $select_query .= "<br>WHERE $fields[$pk_id] = \$$fields[$pk_id]"; }else{ $select_query .= "<br>WHERE $fields[$pk_id] = '\$$fields[$pk_id]'"; }//end if $select_query .="<br>ORDER BY $fields[$pk_id]<br>\";"; //get the insert statement ================================================= //echo "<p><font size=4 color=red><b>setting insert statement</b></font><br>"; $insert_query = "\$sql = \"INSERT INTO $table_name ("; for ($x=0; $x < $cnt; $x++){ $insert_query .= "$fields[$x], "; }// end for //strip last comma $insert_query = substr($insert_query, 0, strlen($insert_query)-2) . ") <br>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 //strip last comma $insert_query = substr($insert_query, 0, strlen($insert_query)-2) . ")<br>\";"; //echo $insert_query; //get the update statement================================================== //echo "<p><font size=4 color=red><b>setting update</b></font><br>"; $update_query = "\$sql = \"UPDATE $table_name<br> 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 .= "<br>WHERE $fields[$pk_id] = \$$fields[$pk_id]"; }else{ $update_query .= "<br>WHERE $fields[$pk_id] = '\$$fields[$pk_id]'"; }//end if $update_query .="<br>\";"; //echo $update_query; //get the primary key for the table ======================================= //echo "<p><font size=4 color=red><b>setting global variables</b></font><br>"; 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 "<p><font size=4 color=red><b>getting edit variables</b></font><br> "; for ($x=0; $x < $cnt; $x++){ if ($slashes=="yes"){ $edit_variables .= " \$$fields[$x] \t\t= stripslashes(\$row['$$fields[$x]']);<br>\n "; }else{ $edit_variables .= " \$$fields[$x] \t\t= \$row['$$fields[$x]'];<br>\n "; }// end if }// end for //echo $edit_variables; //Basic Form================================================================ //two styles... // - php: embedded within a php variable // - html: straight html with embedded php variables //"session varis in table" checked // add a "T" to each OUTGOING vari name // and add to heading if ($sesvars == "yes"){ $sesvarstxt = "-Session Variables-"; } if ($table_type == "php") { $formdescription = "Embedded in PHP Variable"; $formname = $table_name.'form'; $basic_form = "\$$formname = \" <form action=\".\$_SERVER['PHP_SELF'].\" method=post> <table width=90% align=center border=0 cellspacing=1 cellpadding=0>"; //create table rows for ($x=0; $x < $cnt; $x++){ $formfield = $fields[$x]; //"session varis in table" checked // add a "T" to each OUTGOING vari name if ($sesvars == "yes"){ $addt = "t"; } else { $addt = ""; } //don't allow edit of id field if ($fields[$x] == $fields[$pk_id]){ $formpart = "\$$formfield"; $iddescr = "ID"; } else { $formpart = "<input size=50 type=text name=\\\"$addt$formfield\\\" value=\\\"\$$formfield\\\">"; $iddescr = ""; } //color alt rows if ($altrows == "yes" && fmod($x+1,2) == 0) { $rowcolor = "bgcolor=$altrowcolor"; } else { $rowcolor = ""; } $basic_form .= " <tr $rowcolor> <td width=50% align=right valign=top>$iddescr <td width=50% valign=top>$formpart"; }// end for //end form $basic_form .= " <tr> <td align=right valign=top><input type=reset value=\\\"Reset\\\" > <td align=left valign=top><input type=submit value=\\\"Submit\\\" > </table> <input type=hidden name=\\\"\\\" value=\\\"\\\" > </form> \"; "; } else if ($table_type == "html") { $formdescription = "in HTML with Embedded PHP Variables"; $basic_form = "<form action=\"<?php echo \$_SERVER['PHP_SELF']; ?>\" method=post> <table width=90% align=center border=0 cellspacing=1 cellpadding=0> "; //create table rows for ($x=0; $x < $cnt; $x++){ $formfield = $fields[$x]; //"session varis in table" checked // add a "T" to each OUTGOING vari name if ($sesvars == "yes"){ $addt = "t"; } else { $addt = ""; } //don't allow edit of id field if ($fields[$x] == $fields[$pk_id]){ $formpart = "<?php echo \$$formfield; ?>"; $iddescr = "ID"; } else { $formpart = "<input size=50 type=text name=\"$addt$formfield\" value=\"<?php echo \$$formfield; ?>\">"; $iddescr = ""; } //color alt rows if ($altrows == "yes" && fmod($x+1,2) == 0) { $rowcolor = "bgcolor=$altrowcolor"; } else { $rowcolor = ""; } $basic_form .= " <tr $rowcolor>\n <td width=50% align=right valign=top>$iddescr\n <td width=50% valign=top>$formpart\n"; }//for //end form $basic_form .= " <tr > <td align=right valign=top><input type=reset value=\"Reset\" > <td align=left valign=top><input type=submit value=\"Submit\" > </table> <input type=hidden name=\"\" value=\"\" > </form> "; }//if table_type //convert <,>, etc to &xyzt; format so the html will show up $basic_form = nl2br(htmlspecialchars($basic_form)); /* -$initial_varis -$post_varis -$insert_query -$update_query -$my_global -$edit_variables */ echo "<table border=1> <tr> <td colspan=2 bgcolor=blue align=center><font size=3 color=#FFFF66><b>PHP-O-Matic</b></font><font size=6 color=yellow> - - <b>$table_name</b> - - </font><font size=3 color=#FFFF66><b>PHP-O-Matic</b></font> <tr> <td colspan=2 bgcolor=yellow align=center><font size=4 color=blue><b>Get data from Table</b></font> <tr> <td colspan=2> <table> <tr> <td valign=top width=33%><font size=4 color=red><b>The Query</b></font><br>$select_query <td valign=top width=33%><font size=4 color=red><b>Many Values</b></font><br> \$sql_result = mysql_query(\$sql,\$connection) or die (\"Couldn't execute\");<br> while (\$row = mysql_fetch_array(\$sql_result)) { <br> $edit_variables <br> }//while \$row <br> <td valign=top width=33%><font size=4 color=red><b>One Value</b></font><br> \$sql_result = mysql_query(\$sql,\$connection) or die (\"Couldn't execute \");<br> \$row = mysql_fetch_array(\$sql_result);<br> if ( mysql_numrows(\$sql_result) == 1 ) { <br> $edit_variables <br> }//if mysql_numrows </table> <tr> <td colspan=2 bgcolor=yellow align=center><font size=4 color=blue><b>Get Data from Form</b></font> <tr> <td valign=top width=50%><font size=4 color=red><b>Basic Form $formdescription $sesvarstxt</b></font><br>$basic_form <td valign=top width=50%><font size=4 color=red><b>Process the retrieved Values $sesvarstxt</b></font><br>$post_varis <tr> <td colspan=2 bgcolor=yellow align=center><font size=4 color=blue><b>Update the Table</b></font> <tr> <td valign=top width=50%><font size=4 color=red><b>Insert into New Record</b></font><br>$insert_query <td valign=top width=50%><font size=4 color=red><b>Update Existing Record</b></font><br>$update_query <tr> <td colspan=2 bgcolor=yellow align=center><font size=4 color=blue><b>Other</b></font> <tr> <td valign=top width=50%><font size=4 color=red><b>Set Initial Values</b></font><br>$initial_varis <td valign=top width=50%><font size=4 color=red><b>Set variables as Global</b></font><br>$my_global "; }// end function ?> -- Bastien Cat, the other other white meat
