php脚本从sql表中检索枚举值

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

2个回答



我终于找到了一个解决方案,在这里(希望这对某人有用):</ p>

< pre> 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值
#这些值用单引号括起来
#并用逗号分隔
$ regex =“/' (。*?)'/“;
preg_match_all($ regex,$ row [1],$ enum_array);
$ enum_fields = $ enum_array [1];

return($ enum_fields);
}

</ code> </ pre>

所以基本上没有必要通过information_schema!</ p>

来自此博客的信用:</ p>

http://akinas.com/pages/en/blog/mysql_enum/ < / a> </ p>
</ div>

展开原文

原文

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:

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



由于原始问题中提到了CodeCharge,因此在利用MySQL枚举字段类型。 示例代码与上面类似,但也有多种语言版本。</ p>
</ div>

展开原文

原文

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.

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问