Composr Supplementary: Using Enums with MySQL
Written by Chris Graham (ocProducts)
We haven't used ENUMs in Composr as they're not well-standard in SQL. However that shouldn't stop you using them in your own code if you wish to (everyone here uses MySQL anyway to be honest).The actual data types coming out of / going into, MySQL for select/insert/update queries, will be strings or integers, so there's actually going to be no complexity in terms of actual input of data values.
Our main problem is that we need to have a way to find all different values of an ENUM, within our PHP code.
According to the MySQL manual:
MySQL manual said
To determine all possible values for an ENUM column, use SHOW COLUMNS FROM tbl_name LIKE 'enum_col' and parse the ENUM definition in the Type column of the output.
In terms of asking MySQL for the possible ENUM values…
The database driver query method is smart enough to know that SHOW queries return a result, so you'll get MySQL rows right back. We try and keep the APIs very simple.
The clunky thing here is the parsing of what MySQL gives out!
I did a test inside data_custom/execute_temp.php which is our official playground file.
I created a test table:
I queried the SHOW stuff:
I wrote a quick parser:
Here's my code…
Code (PHP)
function execute_temp()
{
$r = $GLOBALS['SITE_DB']->query("SHOW COLUMNS FROM test_tbl LIKE 'test_col'");
$enum_vals = array_map('quote_trim', explode(',', preg_replace('#^.*\((.*)\).*$#', '$1', $r[0]['Type'])));
@print_r($enum_vals);
}
function quote_trim($in)
{
return trim($in, "'");
}
{
$r = $GLOBALS['SITE_DB']->query("SHOW COLUMNS FROM test_tbl LIKE 'test_col'");
$enum_vals = array_map('quote_trim', explode(',', preg_replace('#^.*\((.*)\).*$#', '$1', $r[0]['Type'])));
@print_r($enum_vals);
}
function quote_trim($in)
{
return trim($in, "'");
}
Running regular queries
I should have actually had the table prefix on the start of my table name, so I am renaming test_tbl to cms_test_tbl (my prefix is cms_).This allows me to use other parts of Composr's query API, as consistent table prefixing is assumed (i.e. the prefix is auto-added to any supplied table names) when you're not writing manual SQL.
Code (PHP)
$GLOBALS['SITE_DB']->query_delete('test_tbl'); // Wipe current table contents
// Put some stuff in, trying with both the enum values, and enum indexes
$GLOBALS['SITE_DB']->query_insert('test_tbl', array('test_col' => 'a'));
$GLOBALS['SITE_DB']->query_insert('test_tbl', array('test_col' => 2)); // 2nd index relates to 'b' (the second enum value)
// Should the contents
@print_r($GLOBALS['SITE_DB']->query_select('test_tbl'));
// Put some stuff in, trying with both the enum values, and enum indexes
$GLOBALS['SITE_DB']->query_insert('test_tbl', array('test_col' => 'a'));
$GLOBALS['SITE_DB']->query_insert('test_tbl', array('test_col' => 2)); // 2nd index relates to 'b' (the second enum value)
// Should the contents
@print_r($GLOBALS['SITE_DB']->query_select('test_tbl'));
Feedback
Please rate this tutorial:
Have a suggestion? Report an issue on the tracker.