PHP/MySQL - getting all possible enum type values (robustly)
Posted: Sun Mar 16, 2008 12:05 am
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:
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:
(Someone please correct me if I am wrong.)
Therefore, I spent some time modifying the above code to handle escaped quotes in values:
... 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.
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().
}... then it is stored as a value in the enum column as:Dave's
Code: Select all
enum(...,'Dave''s',...)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();
}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.