Page 1 of 1

[SOLVED]Creating OPTIONS on SELECT tag from mySQL ENUM field

Posted: Wed Nov 22, 2006 5:14 am
by Jaxolotl
Hi everyone!
I'm trying to do the following thing but in a HI QUALITY way.
The point is to create a list of options on a SELECT tag form feeding the options available from a mySQL ENUM field.

I made this rudimental "and horrible" function

STEP 1

Code: Select all

ALTER TABLE `my_table` ADD `colors` ENUM( 'red', 'blue', 'white', 'black' ) AFTER `name` ;
then the function

Code: Select all

/// THE FUNCTION
function enum_values_to_options($table,$field){
	$field_query = "SHOW COLUMNS FROM ".$table." LIKE '".$field."'";
	sql_select($field_query,$field_results);//this is my abstraction to make queries

	while($line = mysql_fetch_row($field_results)){
		$line = str_replace("enum('","",$line['1']);
		$line = str_replace("')","",$line);
		$line = explode("','",$line);
		if(count($line)>1){
			foreach($line as $key => $val){
				$options .="\n<option value=\"".$val."\">".$val."</option>";
			}
			return "\n<select name=\"the_name\">".$options."</select>";
			$options ="";
		}
	}
}

//// PRINT THE HTML

echo enum_values_to_options("my_table","colors");
This is a tranining project with no objects, only functions.
Is there anyone that can explain me how to do it in a better way? I'm shure my code is really horrible, but is usefull.

System specifications
PHP 4.3.10
mySQL 4.1.9

Posted: Wed Nov 22, 2006 6:35 am
by timvw
Your function does two things: Lookup the values in the database.. And generate a html select control...

I would make two functions...

$enum_values = GetValues("mytable", "colors");
$html = GetHtmlSelect($enum_values);

retrive the available ENUM values in an ellegant way

Posted: Wed Nov 22, 2006 10:00 am
by Jaxolotl
to split the whole thing into 2 functions is a good idea, but which code would you write?
to make a branch of html select options from an array we can do it in many wright ways, but how do you do to retrive the available values from an ENUM field in an ellegant way?
my script is functional but is far away from being ellegant.

any expert suggestion?

Posted: Thu Nov 23, 2006 1:15 am
by timvw
I'd probably have a peek at adodb to see how they do it.. But i like your approach (str_replace, str_replace, explode) too.. So i would use that...

Posted: Thu Nov 23, 2006 2:26 am
by Jaxolotl
I'll try to take a look @ phpMyAdmin code to see how they implement this behaviour, but the rule is"no objects" and "use php 4.3.x"
If anyone has an allegant way of doing it and want to explain why he/she use that method it will be welcome!!!

new code

Posted: Thu Nov 23, 2006 3:50 am
by Jaxolotl
starting from this code available on phpmyadmin 2.6.1 libraries , file common.lib.php line 1934

Code: Select all

     * Extracts ENUM / SET options from a type definition string
     *
     * @param   string   The column type definition
     *
     * @return  array    The options or
     *          boolean  FALSE in case of an error.
     *
     * @author  rabus
     */
    function PMA_getEnumSetOptions($type_def) {
        $open = strpos($type_def, '(');
        $close = strrpos($type_def, ')');
        if (!$open || !$close) {
            return FALSE;
        }
        $options = substr($type_def, $open + 2, $close - $open - 3);
        $options = explode('\',\'', $options);
        return $options;
    } // end of the 'PMA_getEnumSetOptions' function
I made this set of script

Code: Select all

<?php
############################################### RETURNS AVAILABLE
function available_posted_rowed($posted,$rowed){
	//if there is a $posted information then return it
	if(strlen(trim($posted))>0){
		return trim($posted);
	}
	else{
		return trim($rowed);
	}
}

############################################### RETURNS SELECTED STRING FOR FORM OPTIONS
function is_selected_option($id_string,$id_posted){
	// if the $id_string string and the $id_posted string are identic
	if($id_string == $id_posted){
		return " selected ";
	}
}

############################################### CREATE OPTIONS FROM ENUM DB FIELD
function enum_values_to_options($table,$field,$select_name,$posted,$rowed){
	// $table is the table of my db
	// $field is the table field i'd like to get the enum values
	// $select_name is the name of the select form (html output)
	// $posted is the $_POST informations if exists
	// $rowed is the current db stored information if exits

	$query = "SHOW COLUMNS FROM ".$table." LIKE '".$field."'";
	sql_select($query,$results); // this is my db connection and query abstraction

	while($line = mysql_fetch_row($results)){
		$open = strpos($line['1'], '(');
		$close = strrpos($line['1'], ')');
		// check if there are a string with () and if it conttains "enum" string at first
		if (!$open || !$close || !preg_match("#^enum#",$line['1'])) {
			return FALSE;
		}
		$options_array = substr($line['1'], $open + 2, $close - $open - 3);
		$options_array = explode("','", $options_array);

		if(count($options_array)>1){
			foreach($options_array as $key => $val){
				$options .="\n<option value=\"".$val."\" "
				.is_selected_option($val,available_posted_rowed($posted,$rowed))
				." >".$val."</option>";
			}//end foreach
			return "\n<select name=\"".$select_name."\">".$options."\n</select>";
			$options = "";
		} //end if($count)
	} //end while
}

?>
<form action="" method="POST">
<?php
// On a CMS context we may have the $row['the_field'] information available
// the available_posted_rowed() function is usefull when using data error control and may have an
// error message before storing the new info, by doing this we don't loose
// the form selections and don't need to start all over again
echo enum_values_to_options("my_table","the_field","the_field",$_POST['the_field'],$row['the_field']);
?>
<input type="submit">
</form>
comments and questions and suggestions and also corrections are WELCOME!

Posted: Thu Nov 23, 2006 4:13 am
by jmut
This is what I use to fetch the enum values.
But usually I hardcode the values in dbenum nomenclature so I don't have need to make db connection for just getting enum fields.
Of course for this to work smoothly....in my testcases I test to see if nomenclature actually isEqual to DB.
So as soon as someone add/remove enum value in the db...the testcase will fail and nomenclature should be updated.
Benefit of this is just less db connections/queries - in my case it's important.

Code: Select all

$final = array();
$row= $db->getRow("SHOW COLUMNS FROM `table_name` WHERE Field = 'column_Name'");
if (empty($row)) {
    return array();
}
$row = explode("','",preg_replace("/(enum|set)\('(.+?)'\)/","\\2",$row['Type']));
foreach($row as $value) {
    $final[$value] = $value;
}
return $final;