动态创建的 WHERE 子句 PHP/MySQL [英] Dynamically created WHERE clause PHP/MySQL
问题描述
我正在动态生成 MySQL 查询的 WHERE 部分.到目前为止，我下面的代码运行良好
I am dynamically generating the WHERE portion of a MySQL query. My code below works perfectly so far
请注意: _GET 字符串在我的代码中都是经过验证的 elswhere，但为了将此代码保持在合理的长度，我将它们直接放在下面.对于任何想要做类似于我正在做的事情并在此处使用我的代码作为基础的人，请务必验证您的字符串以避免 mysql 注入.
PLEASE NOTE: _GET strings are all validated elswhere in my code but in order to keep this code down to a reasonable length for this question I have put them in directly below. For anyone looking to do something similar to what I am doing and is using my code here as a base, please be sure to validate your strings to avoid mysql injections.
/* Loop through each column in the table */
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
/* check if the column has been marked as searchable and that the param sent from the client contains data */
if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
if ( $sWhere == "" )
{
$sWhere = "WHERE ";
}
else
{
$sWhere .= " AND ";
}
/* RANGE FILTER CODE - This part is not important to this question but included for completenes */
$columnFilterValue = mysql_real_escape_string($_GET['sSearch_' . $i]);
// check for values range
$rangeSeparator = "~";
if (!empty($rangeSeparator) && strstr($columnFilterValue, $rangeSeparator)) {
// get min and max
$columnFilterRangeMatches = explode('~', $columnFilterValue);
// get filter
if (empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
$sWhere .= " 0 = 0 ";
else if (!empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
$sWhere .= $aColumns[$i] . " BETWEEN '" . $columnFilterRangeMatches[0] . "' and '" . $columnFilterRangeMatches[1] . "' ";
else if (empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
$sWhere .= $aColumns[$i] . " < '" . $columnFilterRangeMatches[1] . "' ";
else if (!empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
$sWhere .= $aColumns[$i] . " > '" . $columnFilterRangeMatches[0] . "' ";
} else {
/* Begin building WHERE clause */
$sWhere = "WHERE (";
$aORs = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
$value = $_GET['sSearch_'.$i];
array_push($aORs, $aColumns[$i]." IN ($value)");
}
}
$sWhere .= implode(" OR ",$aORs);
$sWhere .= ')';
}
}
}
现在这段代码做了什么，它需要从客户端发送的逗号分隔值的字符串，并基于这些构建 WHERE 类.
Now what this code does it it takes strings of comma seperated values sent from the client and builds the WHERE clase based on those.
- $aColumns 是一个包含表中列的数组
示例:-
如果参数...
- sSearch_1 包含值 1,3,5,6
- sSearch_2 包含 1,2,3 的值
- sSearch_4 包含的值为 4,5,6
- sSearch 6 包含 7,8,9 的值
然后此代码将生成以下 WHERE 子句:
Then the following WHERE clause would be generated by this code:
WHERE genre_id IN (1,3,5,6) OR instruments IN (1,2,3) OR emotions IN (4,5,6) OR ratings IN (7,8,9)
这很好用，但是我想通过发送另一个包含正确顺序的 OR 和 AND 列表的字符串来使 OR 或 AND 动态化.
This works fine however I want to make the OR's or AND's dynamic also by sending another string containing a list of OR's and AND's in the correct sequential order.
例如，如果 $_GET['filtertype'] = 像这样的字符串:-
so for instance if $_GET['filtertype'] = a string like this:-
OR,OR,AND
然后它应该返回而不是上面的:
then instead of the above it should return:
WHERE genre_id IN (1,3,5,6) OR instruments IN (1,2,3) OR emotions IN (4,5,6) OR ratings IN (7,8,9)
正如您在上面的代码中看到的，我目前正在通过 implode 函数将 OR 注入到我的数组中.(相关部分代码如下重复)
As you can see in my code above I am currently injecting the OR's in to my array via the implode function. (The relevant part of code is repeated below)
$sWhere = "WHERE (";
$aORs = array();
for ( $i=0 ; $i<count($aColumns) ; $i++ )
{
if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
{
$value = $_GET['sSearch_'.$i];
array_push($aORs, $aColumns[$i]." IN ($value)");
}
}
$sWhere .= implode(" OR ",$aORs);
$sWhere .= ')';
如何修改它以根据正确的循环顺序添加正确的 AND 或 OR?
How can I modify this to sequrntially add the correct AND or OR based on the correct loop?
推荐答案
我不会创建一个 where 字符串，而是首先创建一个包含 where 部分的数组.
Instead of creating a where string, I would first create an array with where parts.
$whereParts = array();
foreach($aColumns as $i => $column)
{
<logic goes here>
$whereParts[] = 'genre_id IN (1,3,5,6)'; // sample :)
}
$where = 'WHERE ' . implode(' OR ', $whereParts); // note the spaces around OR
然后很容易将 ' OR ' 替换为 ' AND '
Then it's easy to replace ' OR ' with ' AND '
允许用户为所有 where 部分在 AND 和 OR 之间进行选择很容易，但如果您想对每个单独的项目都这样做，则不然.这也是一个逻辑问题.当用户指定a OR b AND c时，他想要(a OR b) AND c还是a OR (b AND c)?
It's easy to allow users to select between AND and OR for all where parts, but not if you want to do that for each individual item. That's also a logic issue. When the user specifies a OR b AND c, would he want (a OR b) AND c or a OR (b AND c)?
这篇关于动态创建的 WHERE 子句 PHP/MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
