0
\$\begingroup\$

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);
?> 
\$\endgroup\$
0

2 Answers 2

3
\$\begingroup\$

You can use describe queries in mysql to find column types

for example DESCRIBE courses returns the table information including column type

\$\endgroup\$
3
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.