Mysql field information for use with auto-form builder

[mindtonic]

Hello,

 

I am working with database mapping objects.  I am trying to develop a way for the object to read information from the db about itself and then use this information to automatically build an edit form.  I have tried using both mysql_field_type, and the object mysql_fetch_field.  The problem is that the field type option reporting is limited: "int", "real", "string", "blob", "time", "date".  I can work with these limits so long as I can get additional information.

 

For example, to do a boolean, I have tried both tinyint and enum.  Tinyint returns a type of int, while enum returns a type of string.  I know that I can make assumptions based on int length (if it has a length of 1 it must be a boolean), but I would like to find something safer and free of assumptions.

 

Also, if using an enum or a set, is there a way to pull out the valid options for the field.  If so, I could always use enum fields and have the table builder auto-generate the valid options.

 

All of this would save hours if not days and weeks off of my life.

 

I appreciate any and all help that can point me in the right direction!

 

    // Create Edit Table
    function edit_table()
    {
        // Pull field names from database        
        $table_data = mysql_query("SELECT * FROM ".$this->database." LIMIT 1");
        $i = 0;
        // Cycle through field names
        while ($i < mysql_num_fields($table_data))
        {
            // Set Field Name
            $name = mysql_field_name($table_data, $i);
            // Set Field Type
            $type = mysql_field_type($table_data, $i);
            // Set Field Length
            $maxLength = mysql_field_len($table_data, $i);
            
            // Check results for errors && build error array
            if (eregi("^(war|err)", $this->results[$name], $matches) && !empty($matches[1]))
                $error[$name] = ($matches[1] ? substr($this->results[$name],3) : $this->results[$name]);
                        
            // Write output method based on field type
            switch (strtoupper($type))
            {
                case "STRING":
                    $return = '<input type="text" name="'.$name.'" value="'.$this->$name.'" maxlength="'.$maxLength.'">';
                    break;                    
                case "INT":
                    // Must never be able to edit ID
                    if ($name == "ID") $return = $this->$name.'<input type="hidden" name="'.$name.'" value="'.$this->$name.'">';
                    else $return = '<input type="text" name="'.$name.'" value="'.$this->$name.'" maxlength="'.$maxLength.'">';
                    break;                    
                case "BLOB":
                    $return = '<textarea name="'.$name.'">'.$this->$name.'</textarea>';
                    break;                    
                default:
                    $return = false;
                    break;
            }
            
            // If there is an output method, build the table row.
            if ($return)
            {
                $table .= "<tr>\n";
                $table .= "\t<th>".$name."</th>\n";
                // If error, display message and assign class
                if ($error[$name]) $table .= "\t<td class='error'>".$return."<br>".$error[$name]."</td>\n";
                else $table .= "\t<td>".$return."</td>\n";
                $table .= "</tr>\n";            
            }
            
            // Advance the counter
            $i++;
        }
        // Return table rows    
        return '<table method="get" class="adminTable">'.$table.'</table>';    
    }

[Wildbug]

Try parsing the output of DESCRIBE <tablename>.  I've used that to generate OPTION lists for ENUMs.