Page 1 of 1

PHP/MySQL - getting all possible enum type values (robustly)

Posted: Sun Mar 16, 2008 12:05 am
by Mark001
Hello,

I have seen several solutions on the Internet about how to get all possible values for a MySQL "enum" datatype, but I can't find one that is robust enough to handle all different situations. They usually use preg_match_all to split the string up by regular expressions, and most of them simply use the single quotation as the delimiter. Here is an example of one I found:

Code: Select all

//copied from http://us3.php.net/function.mysql-query
// Thanks to user JustinB
// Function to Return All Possible ENUM Values for a Field
function getEnumValues($table, $field) {
    $enum_array = array();
    $query = 'SHOW COLUMNS FROM `' . $table . '` LIKE "' . $field . '"';
    $result = mysql_query($query);
    $row = mysql_fetch_row($result);
    preg_match_all('/\'(.*?)\'/', $row[1], $enum_array);
    if(!empty($enum_array[1])) {
        // Shift array keys to match original enumerated index in MySQL (allows for use of index values instead of strings)
        foreach($enum_array[1] as $mkey => $mval) $enum_fields[$mkey+1] = $mval;
        return $enum_fields;
    }
    else return array(); // Return an empty array to avoid possible errors/warnings if array is passed to foreach() without first being checked with !empty().
    
    }
Solutions like these usually work, but I found that if I have an enum value that actually contains a single quote in it, the whole thing crashes. Using the Navicat Lite MySQL client to make my tables, I found that single quotes in SQL strings are actually escaped as two single quotes. For example, let's say I wanted one of my enum values to say this:
Dave's
... then it is stored as a value in the enum column as:

Code: Select all

enum(...,'Dave''s',...)
(Someone please correct me if I am wrong.)

Therefore, I spent some time modifying the above code to handle escaped quotes in values:

Code: Select all

function getEnumValues($table, $field) {
    $enum_array = array();
    $query = 'SHOW COLUMNS FROM `' . $table . '` LIKE "' . $field . '"';
    $result = mysql_query($query);
    $row = mysql_fetch_row($result);
    //preg_match_all('/\'(.*?)\'/', $row[1], $enum_array);
    preg_match_all('/\((.*?)\\)/', $row[1], $enum_array);
 
    if(!empty($enum_array[1])) {
 
    $str1=$enum_array[1][0];
 
    $str2=array();
    
    $tok = strtok($str1, ",");
 
 
while ($tok !== false) {
    $str2[]=$tok;
    $tok = strtok(",");
}
 
    $enumarray2=array();
    foreach ($str2 as $mkey=>$mval){
        preg_match_all('/\'(.*?)\'/', $mval, $istr);
        $enumarray2[$mkey+1]="";
        foreach($istr[1] as $mkey2=>$mval2){
            if ($mkey2>0) $enumarray2[$mkey+1].="'";
            $enumarray2[$mkey+1].=$mval2;
        }
    }
 
    return $enumarray2; 
 
    }
    
    else return array();
    
}
... and guess what? That worked until I had an enum value that contained a comma in it AND/OR a set of parentheses! This is because I first split the row string according to a regexp based on parentheses rather than single quotes, and then I string-tokenize it based on single quotes.

I don't understand why the SQL language doesn't have a built-in construct for getting the possible values and makes you parse the row. I suppose I need a more robust string-splitting function that acts recursively, first finding ONLY the beginning and ending parentheses delimiting the enum value list in the row, then splitting that result based on commas NOT occurring within pairs of single quotes, then stripping those results of the surrounding single quote delimiters, and then unescaping any double-single-quote into merely a single quote. I'm sure this can be done, but what is the best way to approach this? Perhaps there is some other string technique besides regular expressions or string tokenization that acts on recursive delimiters? Something that can recursively handle "bi-directional" delimiters like parentheses, (), and then single-character "dividers" like commas, and then surrounding single-character delimeters like single quotes?

Thanks in advance for your help.

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Sun Mar 16, 2008 12:33 am
by RobertGonzalez
What does the return string look like when it comes back from the query?

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Sun Mar 16, 2008 3:46 pm
by Mark001
Good question: here I have a field called carStatus, which asks the user whether they own or have use of a car. The three enum values are either that they own a car, No, or that they borrow a friend's car. The values are populated into an HTML form Menu selection. Right now I have used hyphens as a workaround. Here is the query and result:

Code: Select all

mysql> SHOW COLUMNS FROM PersonTable LIKE 'carStatus';
+-----------+--------------------------------------------------------------+------+-----+---------+-------+
| Field     | Type                                                         | Null | Key | Default | Extra |
+-----------+--------------------------------------------------------------+------+-----+---------+-------+
| carStatus | enum('','Yes -- own car','No','Yes -- borrow from a friend') | YES  |     | NULL    |       |
+-----------+--------------------------------------------------------------+------+-----+---------+-------+
1 row in set
Now if I use values like:
Yes, I borrow a friend's car.
and
Yes, I own my own car.
... or:
Yes (borrow friend's car)
and
Yes (own my own car)
... which have commas and parentheses respectively within the string, it malfunctions.

If I make the values have all of quotes, commas, and parentheses, the query and result look like this:

Code: Select all

mysql> SHOW COLUMNS FROM PersonTable LIKE 'carStatus';
+-------+-----------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type                                                                  | Null | Key | Default | Extra |
+-------+-----------------------------------------------------------------------+------+-----+---------+-------+
| carStatus | enum('','Yes, (own my own car)','No','Yes, (borrow a friend''s car)') | YES  |     | NULL    |       |
+-------+-----------------------------------------------------------------------+------+-----+---------+-------+
1 row in set
Actually, one thing I will say about just the comma situation in particular (not the case for parentheses), is that my SQL client and/or the SQL server themselves seem to behave strangely when I use commas in the entries when designing the table. Basically Navicat Lite lets you design the table and then submit it. If I put as a value:
Yes, I borrow a friend's car.
... then the next time I view the available values or edit the table entries, the values split into two new ones like this:
Ye
,
I borrow a friend's car.
I think that this is probably really a bug in Navicat rather than commas themselves being invalid to the SQL server. The reason for that is because a) the above query worked, and b) in the table design window, if I look in the "Values" box with the carStatus enum field selected, it looks like:
'','Yes, (own my own car)','No','Yes, (borrow a friend''s car)'
(That's both before and after I've saved the changes to the SQL server.)
If I open the values-editor window (even if I haven't yet saved the definition to the SQL server), the values have the anomaly I described, and then if I click "OK" on that, the values box changes to:
'','Ye','(own my own car)','No','Ye','(borrow a friend''s car)'
It appears that the string-splitting problem is so nearly intractable that it wasn't even solved in the creation of this SQL client. (And other than this bug it is a pretty good client, too.)

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Sun Mar 16, 2008 4:03 pm
by RobertGonzalez
I was thinking that if they come out comma separated that you should be able to fetch the value of the string by removing the "enum(" and final ")" from it using a regexp then treat the string like a comma separated value string to get its components.

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Sun Mar 16, 2008 4:15 pm
by Mark001
The string "enum(" would be easy to find in a regular expression, except for one thing: it would have to be ONLY the outer "enum(" (i.e. let's say one of the values actually had the string/substring "enum(" in it.) Also for the ending parenthesis, is there a way to get the regexp to only get the last one?

As for the CSV (comma-separated value) list, is there a library function to help with this? Or is there some process like searching for all commas and all single quotes (i.e. with regexp or string tokenizer (strtok)) and piecing back commas that occur after an odd number of single quotes (but not after an even number)? ... and if so, it would be followed by string-tokenizing the single quotes in-between commas outside of even sets, to find consecutive single quotes (which are the escaped single quotes) and then unescaping them by piecing those tokens back together with one single quote in-between? It is confusing how to begin approaching this.

Oh yes, there is also the SET datatype -- I suppose it would be a very similar process to get those values, except for the part when you assign indexes to the array elements. I believe the numerical value (i.e. what is made into the index) is the bitwise concatenation of the true/false (1/0) value of each of the values in the SET, in order. (I think I read something to that effect somewhere but I haven't tested it out just yet.)

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Sun Mar 16, 2008 4:33 pm
by RobertGonzalez
Getting the value of the string that is in the enum() should be easy enough (though I am no regexp guru I'd imagine that it is something along the lines of enum([\w*]) or something). I think there are csv functions in the PHP core, but I am not that sure of their robustness.

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Sun Mar 16, 2008 4:45 pm
by Mark001
That regexp might work if it strictly works on the outer one. Also, as for the comma-separated values, PHP does have functions but they probably don't work on strings. I have found the same issue of odd numbers of quotes being discussed at this page:

http://us2.php.net/split

There, the person who wrote that CSV function (Dave Walter) also makes a reference to someone named "Justin" at a different site, who may or may not be the same one. It looks like the function is made for double quotes, which would be fixed by a direct substitution. What do you think of it? I have replicated it here:

Code: Select all

To fix this, I changed the second substr_count to look for an odd number of quotes, as opposed to any quotes at all:
 
<?php
function getCSVValues($string, $separator=",")
{
    $elements = explode($separator, $string);
    for ($i = 0; $i < count($elements); $i++) {
        $nquotes = substr_count($elements[$i], '"');
        if ($nquotes %2 == 1) {
            for ($j = $i+1; $j < count($elements); $j++) {
                if (substr_count($elements[$j], '"') %2 == 1) { // Look for an odd-number of quotes
                    // Put the quoted string's pieces back together again
                    array_splice($elements, $i, $j-$i+1,
                        implode($separator, array_slice($elements, $i, $j-$i+1)));
                    break;
                }
            }
        }
        if ($nquotes > 0) {
            // Remove first and last quotes, then merge pairs of quotes
            $qstr =& $elements[$i];
            $qstr = substr_replace($qstr, '', strpos($qstr, '"'), 1);
            $qstr = substr_replace($qstr, '', strrpos($qstr, '"'), 1);
            $qstr = str_replace('""', '"', $qstr);
        }
    }
    return $elements;
}
?>

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Sun Mar 16, 2008 7:43 pm
by RobertGonzalez
Have you tried that function? If not, give it a run and see if it does what you need it to.

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Tue Mar 18, 2008 12:33 pm
by Mark001
Aha, there's a similar discussion going on http://www.webdeveloper.com/forum/showt ... p?t=143941. Still need more time to test the preceding script.

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Tue Mar 18, 2008 12:47 pm
by RobertGonzalez
This may sound like an odd question, but why can't you just maintain a table of options then select from that table and join the data table on option_id?

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Tue Mar 18, 2008 6:55 pm
by Mark001
Good point -- I was thinking of a possibility of that yet it adds to the complexity. This still doesn't answer the larger issue, though, of all-purpose enum/set value parsing.

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Tue Mar 18, 2008 6:57 pm
by RobertGonzalez
True, it doesn't answer the question. However, it does allow extensibility later without the need for a database table rebuild.

Imagine being able to add, in essence, an enum value to the possible values after the database is in production. Seems to make sense. No?

Re: PHP/MySQL - getting all possible enum type values (robustly)

Posted: Wed Mar 19, 2008 12:54 am
by Mark001
Yeah. I will also concede that enum/set values kind-of violate the guildlines of database normalization because of that lack of extensibility (see http://dev.mysql.com/tech-resources/art ... atype.html). Come to think of it, I suppose that for using a separate options table, presenting the option values is a simple matter of doing a query of the mapping of all the values to their primary key numbers plus a query for the foreign key of the user's selection (or at least an Outer Join of both in favor of the options table and including the key in the query), and updating the selected value is pretty straightforward.

I still don't understand why the makers of SQL/MySQL didn't see fit to provide a special query command for getting possible values of an enum/set datatype. Or maybe I'm wrong, maybe the query to get those vaues returns just like a query for anything else, and the issue is with the PHP/MySQL API library, in that no function was ever written for it. Either way, it should have been, and it's somewhat annoying and tedious that it's not.