I posted the below on stackexchange for code review. But I just realized that what I am asking could be a legit question for SO. Please let me know if you think otherwise.
I have an sql table with multiple fields and 4 of them are enums. I wrote a script that runs thought the table and retrieve the enums and put them in a 2 dimension array.
Unfortunately this script is extreamly slow and I can't fix it.
<?php
require_once('mySQL_Connect.php');
$con = ConnectToDataBase();
if ($con == false)
{
//data returned will be null
exit;
}
$db = 'courses_db';
$table = 'courses';
$fields = array(
'training_field',
'speciality_field',
'type',
'language');
$enums = array();
foreach ($fields as $colomn) {
$sq1 = "SELECT
column_type
FROM
information_schema.columns
WHERE
table_schema = '$db'
AND
table_name = '$table'
AND
column_name = '$colomn'";
$query = mysqli_query($con,$sq1);
$stack = array();
$i = 0;
$stack[$i]=$colomn;
if ($fetch = mysqli_fetch_assoc($query) )
{
$enum = $fetch['column_type'];
$off = strpos($enum,"(");
$enum = substr($enum, $off+1, strlen($enum)-$off-2);
$values = explode(",",$enum);
// For each value in the array, remove the leading and trailing
// single quotes, convert two single quotes to one. Put the result
// back in the array in the same form as CodeCharge needs.
for( $n = 0; $n < Count($values); $n++) {
$val = substr( $values[$n], 1,strlen($values[$n])-2);
$val = str_replace("''","'",$val);
$stack[$i+1]=$val;
$i++;
}
}
// return the values array to the caller
//echo json_encode( $stack);
array_push($enums,$stack);
reset($stack);
}
echo json_encode($enums);
?>
I finally found a solution and here it is (Hope this will be useful for someone):
function get_enum_values($connection, $table, $field )
{
$query = " SHOW COLUMNS FROM `$table` LIKE '$field' ";
$result = mysqli_query($connection, $query );
$row = mysqli_fetch_array($result , MYSQL_NUM );
#extract the values
#the values are enclosed in single quotes
#and separated by commas
$regex = "/'(.*?)'/";
preg_match_all( $regex , $row[1], $enum_array );
$enum_fields = $enum_array[1];
return( $enum_fields );
}
So basically there's no need to go through information_schema!
Credit goes to this blog:
As CodeCharge was mentioned in original question, there is an example in CCS online help under Utilize MySQL Enum Field Type. Sample code is similar to above, but also available in several languages.