Skip to content

Instantly share code, notes, and snippets.

@yantze
Last active November 10, 2015 14:01
Show Gist options
  • Save yantze/bf3aa5a0ec35bebcb8c4 to your computer and use it in GitHub Desktop.
Save yantze/bf3aa5a0ec35bebcb8c4 to your computer and use it in GitHub Desktop.
MysqlHelper can get select fields full info with no result 获取 select 结果集的字段信息
<?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);
<?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;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment