MysqlHelper can get select fields full info with no result 获取 select 结果集的字段信息

example.php

<?php
// example in Yii2
$db = \Yii::$app->db;
$dbHelper = new \app\helpers\db\MysqlHelper();
$connInfo = $dbHelper->parseDsn(
        \Yii::$app->db->dsn . ";user=" . $db->username .";password=" . $db->password );
$sql = "select * from database.table";
$dbHelper->fieldsInfo($connInfo, $sql);
$numFields = $dbHelper->getNumFieldsName();

// other
$connInfo = [
        'user' => 'dbuser',
        'password' => 'dbpassword',
        'host' => 'localhost',
        'port' => 3306,
        'dbname' => 'database'
    ];
$dbHelper = new \app\helpers\db\MysqlHelper();
$sql = "select * from database.table";
$dbHelper->fieldsInfo($connInfo, $sql);

MysqlHelper.php

<?php
/**
 * Created by PhpStorm.
 * User: yantze.yang
 * Date: 2015/11/7
 * Time: 18:05
 */

namespace app\helpers\db;
// user:password@host:port\database
define ('DSN_REGEX', '/^(?P<user>\w+)(:(?P<password>\w+))?@(?P<host>[.\w]+)(:(?P<port>\d+))?\\\\(?P<database>\w+)$/im');
// mysql:host=localhost;dbname=database
define ('YII2_DSN_REGEX', '/[:;]([^;]+)=([^;]+)/i');

class MysqlHelper
{

    public $defaultOptions = [
        'user' => '',
        'password' => '',
        'host' => 'localhost',
        'port' => 3306,
        'dbname' => ''
    ];

    public $fetch_fields_result = [];

    /**
     * Parse a DSN-string, mysql:host=localhost;dbname=database, and break it into it's components.
     * Password is optional.
     *
     * Many thanks to Vision.
     *
     * @param string $dsn DSN string to parse.
     * @return array|bool Array on success, false on error.
     */
    public function parseDsn($dsn)
    {

        if (strlen($dsn) == 0)
        {
            return false;
        }
        if (!preg_match_all(YII2_DSN_REGEX, $dsn, $matches))
        {
            return false;
        }
        if (count($matches) < 1)
        {
            return false;
        }

        $result = [];
        foreach ($this->defaultOptions as $key => $value)
        {
            $idx = array_search($key, $matches[1]);
            if ($idx > -1) {
                $result[$key] = $matches[2][$idx];
            }
        }
        return $result;
    }

    /**
     * 获取结果集的字段信息
     * $db = \Yii::$app->db;
     * $ret = \app\helpers\db\MysqlHelper::parseDsn(
     *      \Yii::$app->db->dsn . ";user=" . $db->username .";password=" . $db->password);
     * $sql = '';
     * $ret = \app\helpers\db\MysqlHelper::fields_info($ret, $sql);
     *
     * @param $conn
     * @param $data_source
     * @return array|bool
     */
    public function fieldsInfo($conn, $data_source) {

        // 也可以使用 PDO 来做表的获取信息渠道
        // $result = \Yii::$app->db->pdo;
        // $ret = $result->query($sql);
        // var_dump($ret->getColumnMeta(0));

        $conn = array_merge($this->defaultOptions, $conn);
        $data_source = trim($data_source);
        $link = mysqli_connect($conn['host'], $conn['user'], $conn['password'], $conn['dbname']);


        /* check connection */
        if (mysqli_connect_errno()) {
            printf("Connect failed: %s\n", mysqli_connect_error());
            exit();
        }

        if (strpos($data_source, ' ')) {
            // SELECT clause
        } else {
            // data tables
            $data_source = "SELECT * FROM " . $data_source . " LIMIT 1";
        }

        $query_info = mysqli_query($link, $data_source);
        $this->fetch_fields_result = $query_info->fetch_fields();
        return $this->fetch_fields_result;
        // return mysqli_fetch_fields($query_info);
    }

    /**
     * 根据 fetch_fields 获取数据类型，参考类型列表
     * http://fr2.php.net/manual/en/mysqli-result.fetch-fields.php
     * @param $fetch_fields_result
     * @return array
     */
    public function getNumFieldsName() {
        $num_result = [];
        foreach ($this->fetch_fields_result as $k=>$v) {
            if ($v->type < 10) {
                $num_result[] = $v->name;
            }
        }

        return $num_result;
    }

    public function getFieldsName() {
        $fieldsName = [];
        foreach ($this->fetch_fields_result as $k=>$v) {
            $fieldsName[] = $v->name;
        }

        return $fieldsName;
    }
}