Use PHP and MySQL DESCRIBE command to build checkbox and radio button menus dynamically

I'm making a data-entry web page in PHP that validates and parses data for entry into a MySQL database. There are more than 30 columns in the database that consist of MySQL SET or ENUM data. If you don't know much SQL, an ENUM datatype column can only hold one value from a collection of ENUM variables that you define when you create the database. SET is also a collection of variables that you define when you create the database, but the SET column can hold one or more of the SET variables.

This is an ENUM datatype: car_titled_in_state = enum('Alabama','Alaska','Arkansas','California') You can only title a car in one state. MySQL will not let you enter more than one state in car_titled_in_state. (The error handling code for titling a car in more state is the function prison_term(num_years). ;-)

This is a SET datatype: ice_cream_toppings = set('chocolate syrup','marsh mellow cream','cherries','butterscotch'). You can have any number of ice cream toppings, but each member of the set can only be entered and counted once. (The error handling function for too many ice cream topping is body_weight = body_weight++.)

Radio buttons, which allow only one selection from the group, are suitable for ENUM variables. Checkboxes, which allow for any number of selections from the group, are suitable for SET variables.

Making these menus line by line in my PHP would be a hassle because the groups of allowed variables in many of these MySQL columns are going to change during beta and all the way into the early production months; some of them will probably need to change again in 2011. These changes (lots of changes) would have to made in the PHP code, plus all those requests would thicken the bandwidth between client and server.

I decided I would use a PHP function that queries the MySQL database and dynamically builds the menus. This would need to use the MySQL command DESCRIBE, which, when used on ENUM or SET datatype columns, returns the group of strings allowed in the column. (See above for their format.) Now, I am certain that somebody, somewhere faced this problem before, but I searched the Internet twice for an hour each time, and I did not find anything even close, especially not in PHP. So I decided to write my own, which took me two days. It probably shouldn't have taken me that long, but I was working on my laptop in a van with my wife and my in-laws, which was not an environment in compliance with Joel Spolsky's rules for efficient programmer habitats.

I call my function choicemaker. (You will no doubt change this in your code.) It takes three arguments: a MySQL table name ($table), a MySQL column name ($col_name) and an integer called $row_l开发者_高级运维ength. The $row_length is a convenience. I have some very large SET and ENUM columns in the database; one of the ENUMs lists every Federal Agency! I limit the number that display on each line for the sake of neatness.

Choicemaker() inspects the results of the DESCRIBE query and uses them to decide whether to build radio buttons (ENUM) or checkboxes (SET). This saves you having to put thid decision in code!

function choicemaker($table, $col_name, $row_length)
  #connect to the database         
        {db_connect();  
  #build the string for the DESCRIBE query -- note the absence of SELECT
         $qry_list="DESCRIBE " . $table . " " . $col_name . ";";
  #submit the query
         $listdisplay=mysql_query( $qry_list );     
  #get the query results        
         while ($row = mysql_fetch_array( $listdisplay )){
  #there will only be one row of results
            $make_list=$row[1];
  #we need to get rid of some punctuation from the results     
            $remove = array("(", ")","'");                  
           $replace  = array("", "", "","");
  #important -- we inspect the query results to find out if we
  #have SET or ENUM variables and then assign a value to $choice_type and
  #get ready to remove either 'set' or 'enum' from the results string  
               if (preg_match("/^set/", $make_list)){
                  array_unshift($remove, 'set');
                  $choice_type="checkbox";}
                    elseif (preg_match("/^enum/", $make_list)){
                           array_unshift($remove, 'enum');
                           $choice_type="radio";}
  #here we clean up the query results
               $arr_list = str_replace($remove, $replace, $make_list);
  #make get redy to loop through the clean results
               $arr_list = explode(',',$arr_list);  
               $row_pos=$row_length;
  #finally, we get to make some buttons or boxes               
                       foreach ($arr_list as $item) {
                              $str_choice = $item . ' ';
                              $str_choice .= '<input type="' . $choice_type . '" '; 
                              $str_choice .= 'name="' . $col_name . '" ';
                              $str_choice .= 'value="' . $item . '" /> ';
  #output our button / box here                                            
                              echo $str_choice;
  #decrement $row_pos to decide if it is time to output a line break                              
                              $row_pos--;
                              if ($row_pos == 0) {
                                 echo('<br />');
  #reset $row_pos after every break                              
                                 $row_pos = $row_length;}}}
  #Help stop connection pollution!  Always close your connections!                           
           mysql_close(); }


Here's the uncommented version for cut and paste:

            function choicemaker($table, $col_name, $row_length)           
                    {db_connect();  
                     $qry_list="DESCRIBE " . $table . " " . $col_name . ";";
                     $listdisplay=mysql_query( $qry_list );                                 
                     while ($row = mysql_fetch_array( $listdisplay )){
                           $make_list=$row[1];
                           $remove = array("(", ")", "'");                  
                           $replace  = array("", "", "","");
                           if (preg_match("/^set/", $make_list)){
                              array_unshift($remove, 'set');
                              $choice_type="checkbox";}
                                elseif (preg_match("/^enum/", $make_list)){
                                       array_unshift($remove, 'enum');
                                       $choice_type="radio";}
                           $arr_list = str_replace($remove, $replace, $make_list);
                           $arr_list = explode(',',$arr_list);  
                           $row_pos=$row_length;
                                   foreach ($arr_list as $item) {
                                          $str_choice = $item . ' ';
                                          $str_choice .= '<input type="' . $choice_type . '" '; 
                                          $str_choice .= 'name="' . $col_name . '" ';
                                          $str_choice .= 'value="' . $item . '" /> ';                  
                                          echo $str_choice;
                                          $row_pos--;
                                          if ($row_pos == 0) {
                                             echo('<br />');
                                             $row_pos = $row_length;}}}
                       mysql_close(); } 

Here's my question: given that this code will run about 30 times to build the page, would it really be any quicker to do this in AJAX? It will take the users about 30 minutes at least, so there is no real performance gain associated with having the top part built more quickly. I don't think the user experience will be much better. But what would an AJAX version look like -- get the results back in JSOBN and parse them?

If I were you I'd cache this into a template or use the php file as a generator, unless there are quite a few table that will be queried. What is the purpose of dynamically querying enum types? Ajax won't make it load any faster, probably even slower, but might allow you to load the first 10, then the second 10, etc., so the user sees it actually working, and can interact with it before it is done completely loading. I wouldn't think loading time would be a big problem though.

The reason I need to build the menu from enum and set datatypes is that for at least the first two years of my application, these datatypes will expand, and there are too many of them (more than 30) to make it efficient to keep adding new ones in code. Adding them in the database and letting the code generate the menus dynamically will save LOTS of time.