如何将PHP中的SQL结构改写成长尾关键词？
- 内容介绍
- 文章标签
- 相关推荐
本文共计1570个文字，预计阅读时间需要7分钟。
将SQL结构如下：
phppublic static $table_big=['AuthItemChild', 'AuthItem', 'AuthAssignment'];public static $not_params=['db1'=> ['table1'=> ['']]];public function __construct() {
将sql结构拆分
['table1'],
"db1" => [],
];
//需要转化为大写的表
public static $table_big = ["AuthItemChild", "AuthItem", "AuthAssignment"];
public static $not_params
= [
"db1" => [
"table1" => [""],
]
];
public function __construct($db, $sql)
{
$this->database = $db;
$this->old_sql = $sql;
$this->sql = str_replace('`', "", strtolower($sql));
$this->db = Yii::app()->$db;
$this->commend = $this->db->createCommand();
}
public function strNpos($str, $find, $n)
{
$pos_val = 0;
for ($i = 1; $i <= $n; $i++) {
$pos = strpos($str, $find);
$str = substr($str, $pos + 1);
$pos_val = $pos + $pos_val + 1;
}
return $pos_val - 1;
}
public function getChildrenVal($sql)
{
$children = false;
preg_match('/(s*(select)/i', $sql, $children_select);
if ($children_select) {
$select_pos = strpos($sql, $children_select[0]);
$children_all = substr($sql, $select_pos);
$count = substr_count($children_all, "(");
if ($count == 1) {
$end = $this->strNpos($children_all, ")", 1);
$children = substr($children_all, 0, $end + 1);
} else {
for ($i = 2; $i <= $count; $i++) {
$pos = $this->strNpos($children_all, "(", $i);
$r = substr($children_all, 0, $pos + 1);
if (substr_count($r, ")") == ($i - 1)) {
$end = $this->strNpos($children_all, ")", $i - 1);
$children = substr($children_all, 0, $end + 1);
break;
}
}
if ($children == false) {
$end = $this->strNpos($children_all, ")", $count + 1);
$children = substr($children_all, 0, $end);
}
}
}
return $children;
}
//sql 拆解
public function explainSql()
{
$sql = $this->sql;
$children = $this->getChildrenVal($sql);
if ($children) {
$children_data = $this->getChildrenData(trim(trim($children, ")"), "("));
$sql = str_replace($children, "(" . $children_data . ")", $sql);
}
$sql_from = explode("|", preg_replace('/from/', "|", $sql));
if (count($sql_from) < 2) {
$this->error = "查询错误";
} else {
//CString::echof($sql_from);exit;
$sql_from[1] = $this->explainLimit($sql_from[1]);
$this->select = str_replace("select", "", $sql_from[0]);
$sql_from_where_array = explode("|", preg_replace('/where/', "|", $sql_from[1]));
$from = explode("|", preg_replace(['/(left|right)/', '/(join)/'], ["", "|"], $sql_from_where_array[0]));
$this->where = isset($sql_from_where_array[1]) ? $sql_from_where_array[1] : false;
$this->table = $this->formChange(array_shift($from));
$this->join_string_array = $from;
}
}
//大写表替换
public function formChange($table)
{
$tab = self::$table_big;
foreach ($tab as $v) {
$table = preg_replace('/(' . $v . ')/i', $v, $table);
}
return $table;
}
/**
* @desc 拆分limit 和 order by
*/
public function explainLimit($sql)
{
$order_match = '/(order)s+(by)s+w+s*(?
本文共计1570个文字，预计阅读时间需要7分钟。
将SQL结构如下：
phppublic static $table_big=['AuthItemChild', 'AuthItem', 'AuthAssignment'];public static $not_params=['db1'=> ['table1'=> ['']]];public function __construct() {
将sql结构拆分
['table1'],
"db1" => [],
];
//需要转化为大写的表
public static $table_big = ["AuthItemChild", "AuthItem", "AuthAssignment"];
public static $not_params
= [
"db1" => [
"table1" => [""],
]
];
public function __construct($db, $sql)
{
$this->database = $db;
$this->old_sql = $sql;
$this->sql = str_replace('`', "", strtolower($sql));
$this->db = Yii::app()->$db;
$this->commend = $this->db->createCommand();
}
public function strNpos($str, $find, $n)
{
$pos_val = 0;
for ($i = 1; $i <= $n; $i++) {
$pos = strpos($str, $find);
$str = substr($str, $pos + 1);
$pos_val = $pos + $pos_val + 1;
}
return $pos_val - 1;
}
public function getChildrenVal($sql)
{
$children = false;
preg_match('/(s*(select)/i', $sql, $children_select);
if ($children_select) {
$select_pos = strpos($sql, $children_select[0]);
$children_all = substr($sql, $select_pos);
$count = substr_count($children_all, "(");
if ($count == 1) {
$end = $this->strNpos($children_all, ")", 1);
$children = substr($children_all, 0, $end + 1);
} else {
for ($i = 2; $i <= $count; $i++) {
$pos = $this->strNpos($children_all, "(", $i);
$r = substr($children_all, 0, $pos + 1);
if (substr_count($r, ")") == ($i - 1)) {
$end = $this->strNpos($children_all, ")", $i - 1);
$children = substr($children_all, 0, $end + 1);
break;
}
}
if ($children == false) {
$end = $this->strNpos($children_all, ")", $count + 1);
$children = substr($children_all, 0, $end);
}
}
}
return $children;
}
//sql 拆解
public function explainSql()
{
$sql = $this->sql;
$children = $this->getChildrenVal($sql);
if ($children) {
$children_data = $this->getChildrenData(trim(trim($children, ")"), "("));
$sql = str_replace($children, "(" . $children_data . ")", $sql);
}
$sql_from = explode("|", preg_replace('/from/', "|", $sql));
if (count($sql_from) < 2) {
$this->error = "查询错误";
} else {
//CString::echof($sql_from);exit;
$sql_from[1] = $this->explainLimit($sql_from[1]);
$this->select = str_replace("select", "", $sql_from[0]);
$sql_from_where_array = explode("|", preg_replace('/where/', "|", $sql_from[1]));
$from = explode("|", preg_replace(['/(left|right)/', '/(join)/'], ["", "|"], $sql_from_where_array[0]));
$this->where = isset($sql_from_where_array[1]) ? $sql_from_where_array[1] : false;
$this->table = $this->formChange(array_shift($from));
$this->join_string_array = $from;
}
}
//大写表替换
public function formChange($table)
{
$tab = self::$table_big;
foreach ($tab as $v) {
$table = preg_replace('/(' . $v . ')/i', $v, $table);
}
return $table;
}
/**
* @desc 拆分limit 和 order by
*/
public function explainLimit($sql)
{
$order_match = '/(order)s+(by)s+w+s*(?