php script to retrieve enum values from sql table

Question

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);
?> 

Answer 1

You can use describe queries in mysql to find column types

for example DESCRIBE courses returns the table information including column type

Answer 2

I finally found a solution and here it is:

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:

http://akinas.com/pages/en/blog/mysql_enum/

Hope this helps out some people.