Wow, that's umm hideous. Cheers, Rob. On Fri, 2008-04-25 at 22:43 -0400, Bastien Koert wrote: > 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 > ?> > > -- http://www.interjinn.com Application and Templating Framework for PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
