php 使用php变量创建动态mysql查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15794179/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a dynamic mysql query using php variables
提问by Hanny
I have an html table that loads everything in a mySQL database table. I have dropdowns that relate to columns of that mySQL table - when the user selects one of the dropdowns it uses AJAX to query the database.
我有一个 html 表，它加载了 mySQL 数据库表中的所有内容。我有与该 mySQL 表的列相关的下拉列表 - 当用户选择其中一个下拉列表时，它使用 AJAX 来查询数据库。
I need to figure out how to build the query dynamically because sometimes the dropdowns will be empty (i.e. they don't want to filter by that column).
我需要弄清楚如何动态构建查询，因为有时下拉列表将为空（即他们不想按该列进行过滤）。
What is the best way to do this?
做这个的最好方式是什么？
Currently I have something like this:
目前我有这样的事情：
$stationFilter = $_GET['station'];
$verticalFilter = $_GET['vertical'];
$creativeFilter = $_GET['creative'];
$weekFilter = $_GET['week'];
$result = mysql_query("SELECT * FROM $tableName WHERE STATION_NETWORK = '$stationFilter' AND VERTICAL = '$verticalFilter' AND CREATIVE = '$creativeFilter' AND WK = '$weekFilter'");
$data = array();
while ($row = mysql_fetch_row($result) )
{
$data[] = $row;
}
$finalarray['rowdata'] = $data;
Which you can imagine doesn't work because if any of those fields are empty - the query fails (or returns nothing, rather).
您可以想象这是行不通的，因为如果这些字段中的任何一个为空 - 查询失败（或者什么都不返回）。
Obviously creating such a 'static' query like that really makes it difficult if certain variables are empty.
显然，如果某些变量为空，那么创建这样的“静态”查询确实会变得很困难。
What is the best way to dynamically create that query so that it only enters the ones that are not empty get added to the query so it can successfully complete and display the appropriate data?
动态创建该查询的最佳方法是什么，以便它只输入非空的查询并添加到查询中，以便它可以成功完成并显示适当的数据？
回答by SeanWM
Just check if the variables contain a value and if they do, build the query like so:
只需检查变量是否包含一个值，如果包含，请像这样构建查询：
unset($sql);
if ($stationFilter) {
$sql[] = " STATION_NETWORK = '$stationFilter' ";
}
if ($verticalFilter) {
$sql[] = " VERTICAL = '$verticalFilter' ";
}
$query = "SELECT * FROM $tableName";
if (!empty($sql)) {
$query .= ' WHERE ' . implode(' AND ', $sql);
}
echo $query;
// mysql_query($query);
回答by CHORTik
$filter = array();
if ($_GET['station'] != '')
{ $filter[] = 'STATION_NETWORK = '.$_GET['station'];}
if ($_GET['vertical'] != '')
{ $filter[] = 'VERTICAL = '.$_GET['vertical'];}
if ($_GET['creative'] != '')
{ $filter[] = 'CREATIVE = '.$_GET['creative'];}
if ($_GET['week'] != '')
{ $filter[] = 'WK = '.$_GET['week'];}
$query = 'SELECT * FROM $tableName WHERE '.implode(' AND ', $filter);
$result = mysql_query($query);
...
but better if in GET you pushed name of tables rows; $_GET['STATION_NETWORK'] --- some like this;
但如果在 GET 中推送表行名称会更好；$_GET['STATION_NETWORK'] --- 有些像这样；
then you make
foreach ($_GET as $key => $value)
{
if ($value != '')
{ $filter[] = $key.' = '.$value;}
}
or try
或尝试
$filter = array('STANTION_NETWORK' => $_GET['station'],
'VERTICAL' => $_GET['vertical'],
'CREATIVE' => $_GET['creative'],
'WK' => $_GET['week']);
$query_array = array();
foreach ($filter as $key => $value)
{
if ($value != '')
{ $query_array[] = $key.' = '.$value;}
}
$query = 'SELECT * FROM $tableName WHERE '.implode(' AND ', $query_array);
$result = mysql_query($query);
