{"id":517,"date":"2009-04-04T20:19:08","date_gmt":"2009-04-04T19:19:08","guid":{"rendered":"http:\/\/t-machine.org\/?p=517"},"modified":"2009-04-04T20:19:08","modified_gmt":"2009-04-04T19:19:08","slug":"php-how-to-fetch-all-possible-values-of-an-enum-from-mysql","status":"publish","type":"post","link":"http:\/\/new.t-machine.org\/index.php\/2009\/04\/04\/php-how-to-fetch-all-possible-values-of-an-enum-from-mysql\/","title":{"rendered":"PHP: how to fetch all possible values of an ENUM from MySQL"},"content":{"rendered":"<p>Sadly, the code snippets on MySQL&#8217;s main website for PHP are mostly untested and buggy (try running them &#8211; half of them don&#8217;t execute because of silly mistakes).<\/p>\n<p>After much trial and error, here&#8217;s one that *actually works*:<\/p>\n<blockquote>\n<pre>\/\/ Missing feature (?) from MySQL: find the list of valid ENUM values for a given ENUM\r\n\/\/ (actually, returns all the value-arrays for ALL the enum fields in a given table, by name)\r\n\/\/ ---------------------------------------------------------\r\nfunction fetchEnumValuesForTable( $tablename )\r\n{\r\n\tglobal $db; \/\/ assuming you're using PEAR:DB here, and throughout (I use it, or MDB, exclusively)\r\n\t\r\n\t$enumresult = $db->query(\"SHOW COLUMNS FROM $tablename\");\r\n\r\n\t\/\/ Makes arrays out of all ENUM type fields.\r\n\t\/\/ Uses the field names as array names and skips non-ENUM fields\r\n\twhile( $enumrow = $enumresult->fetchRow() )\r\n\t{\r\n\t\textract($enumrow);\r\n\t\tif (substr($Type, 0, 4) != 'enum') continue;\r\n\r\n\t\t$Type = str_replace('enum', 'array', $Type);\r\n\t\t\r\n\t\t\/\/ Add to array\r\n\t\teval( '$tmp = '.\"$Type;\" ); \/\/ I'm not sure why, but I had to do this\r\n\t\t\/\/ intermediate step to get it to work\r\n\t\t\r\n\t\t$results[$Field] = $tmp;\r\n\t}\r\n\t\r\n\treturn $results; \/\/ returns an array mapping each enum's \"column name\"\r\n\t\/\/ to \"array of elements valid fo that ENUM\"\r\n}\r\n<\/pre>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"<p>Sadly, the code snippets on MySQL&#8217;s main website for PHP are mostly untested and buggy (try running them &#8211; half of them don&#8217;t execute because of silly mistakes). After much trial and error, here&#8217;s one that *actually works*: \/\/ Missing feature (?) from MySQL: find the list of valid ENUM values for a given ENUM [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[55,20],"tags":[],"_links":{"self":[{"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/posts\/517"}],"collection":[{"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/comments?post=517"}],"version-history":[{"count":0,"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/posts\/517\/revisions"}],"wp:attachment":[{"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/media?parent=517"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/categories?post=517"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/new.t-machine.org\/index.php\/wp-json\/wp\/v2\/tags?post=517"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}