Re: [PHP] peer review (was php framework vs just php?)

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 .= "&nbsp;&nbsp;\$$fields[$x] \t\t=
stripslashes(\$row['$$fields[$x]']);<br>\n ";
}else{
$edit_variables .= "&nbsp;&nbsp;\$$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

.

Relevant Pages

  • Re: [PHP] peer review (was php framework vs just php?)
    ... those POST/GET elements, the global elements and the development of the sql ... generation of a SELECT query ... checkbox to enable htmlspecialchars protection ... //get the primary key for the table ...
    (php.general)
  • Re: Problem with Access concatenate query
    ... records in the final query. ... You probably need to INNER JOIN the tables, although on what column, I am not sure. ... PriceID -- Primary Key ... ItemID --- Foreign Key ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Jerry Whittle, Microsoft Access MVP ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • RE: Processing thousands of records
    ... Access automatically creates an index for primary key fields. ... that the query is working faster, you don't need the 1stVisit02 query. ... Jerry Whittle, Microsoft Access MVP ... where do I read about fundamental indexing and normalization? ...
    (microsoft.public.access.queries)
  • Re: Determining if a form has a table or query recordset source
    ... how to obtain the primary key of a query? ... the generic answer is: "who says any query has one?". ... > set for insertion into an audit trail table, ... > Dim audID As Long ...
    (microsoft.public.access.security)