RE: Converting MySQL into Form

["Jay Blanchard" <jblanchard@xxxxxxxxxx>]

[snip]
Anyone know of a way to can take Mysql tables/fields from phpMyAdmin or
.sql file and quickly make into HTML forms?
[/snip]

Here is that function...(there are many mods that can be made to this,
but it works)

/*
 * SQL Extactor
 * Author: jblanchard
 *
 * Business Rule:	<if applicable>
 *
 * REVISION:	Jun 19, 2006 
 * USAGE:		used to create form based on sql database info.
 *     		dis be da ugly version, needs to be funkionized
 * 
 */


/* database connection */ 
if(!$dc = mysql_connect('yourdatabase', 'username', 'password')){
	echo mysql_error();
	exit();
}


function formCreate($database, $table, $action, $excludeCols,
$recordID){
	/* 
	 * This function is used to create forms on the fly based on
tables within 
	 * the database. The minimal arguments are database name and
table name.
	 * Additional arguements may be supplied to indicate columns to
be excluded
	 * from form and an action (CRUD) to be performed once the form
	 * is filled out. If wanting to do an update, read or delete you
can specify
	 * a record to retrieve to populate the form. Default values
will be provided 
	 * for arguements not included.
	 */
	 /* database connection in global variable */
	 global $dc;
	 /* number of arguements sent to function */
	 $numArgs = func_num_args();
	 /* test to make sure that you have the minial two arguements */
	 if(2 > $numArgs){
	 	/* not enough arguments */
	 	$errMsg = "not enough arguements supplied, please supply
database and table name";
	 	return($errMsg);
	 } else {
	 	/* 
	 	 * Supply default values for optional arguements if they
are not set.
	 	 * An interesting note here: the action can be anything
that the user
	 	 * specifies, it is not strictly limited to CRUD and it
will be output
	 	 * in a hidden form field; 
	 	 * <input type="hidden" name="action" value="whatever
action is called"> 
	 	 * That way when the user clicks 'Submit' the programmer
can have a 
	 	 * switch action in his or her processing script to
handle this form. 
	 	 */
	 	 if(!isset($action))          { $action = 'read'; }
	 	 if(!isset($recordID))        { $recordID = ''; }
	 	 if(!isset($excludeCols)){
	 	 	$excludeCols = '';
	 	 } else {
	 	 	/* create an array of excluded columns */
	 	 	$arrExcludeCols = explode(",", $excludeCols);
	 	 }
	 	
	 	/* describe the table */
	 	$sqlDesc = "DESCRIBE `".$database."`.`".$table."` ";
	 	if(!($dbInfo = mysql_query($sqlDesc, $dc))){
	 		return mysql_error();
	 	} else {
	 		while($tableInfo = mysql_fetch_array($dbInfo)){
				/* 
				 * regular expression - we need the data
that exists between the
				 * parentheses in the Type column of the
database being described
				 * so that we can use for length values
of form fields
				 */
				 if(!(in_array($tableInfo['Field'],
$arrExcludeCols))){
				 	if(preg_match (
"/\((\d{1,}.*?)\)/", $tableInfo[1], $regs )){
						/* handle numerical
values in parentheses to create form element lengths */
			   			echo
"<label>".$tableInfo['Field']."</label>";
						echo "<input
type=\"text\" name=\"".$tableInfo['Field']."\" size=\"".$regs[1]."\"
maxlength=\"".$regs[1]."\"><br />\n";
					} elseif("text" ==
$tableInfo[1]) {
						/* handle text columns
*/
						echo
"<label>".$tableInfo['Field']."</label>";
						echo "<textarea
name=\"".$tableInfo['Field']."\" cols=\"80\" rows=\"10\"></textarea><br
/>\n";
					} elseif("enum" ==
substr($tableInfo[1], 0, 4)){
						/* handle enumerated
columns and creat drop downs */
						echo
"<label>".$tableInfo['Field']."</label>&nbsp;";
						/*
						 * regular expression -
we need the data that
						 * exists between the
single quotes in the Type column of the
						 * database being
described so that we can use for option 
						 * values in a drop-down
on the form
						 */
						preg_match_all(
"/'(.*)'/U", $tableInfo[1], $matches);
						echo "<select
name=\"".$tableInfo['Field']."\">\n";
						echo
"<option></option>\n";
						for($i = 0; $i <
count($matches[1]); $i++){
							echo
"<option>".$matches[1][$i]."</option>\n";
						}
						echo "</select><br
/>\n";
					}//end if preg
	 			}//end if in_array
	 		}//end while tableInfo
	 		/* set up the hidden action field */
	 		echo "<input type=\"hidden\" name=\"action\"
value=\"".$action."\">\n";
	 		/* provide a submit and reset button */
	 		echo "<input type=\"submit\" name=\"submit\"
value=\"Submit\">&nbsp;&nbsp;";
	 		echo "<input type=\"reset\" name=\"reset\"
value=\"Clear\"><br />\n";
	 		return;
	 	}// end if dbInfo
	}// end if numArgs
}//end function formCreate



-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php