使用php变量创建动态mysql查询

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?

动态创建该查询的最佳方法是什么，以便只输入非空的查询，将其添加到查询中，以便它可以成功完成并显示相应的数据？

2 个解决方案

#1

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);

#2

$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);

#1