使用PHP变量创建动态MySQL查询 [英] Create a dynamic mysql query using php variables

问题描述

我有一个HTML表加载一切都在一个MySQL数据库表。我有一个涉及到的MySQL表的列下拉菜单 - 当用户选择它使用AJAX来查询数据库的下拉菜单中的一个

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.

我需要弄清楚如何动态地构建查询，因为有时下拉列表是空的（也就是他们不希望通过该列进行筛选）。

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?

目前我有这样的事情：

    $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?

推荐答案

只检查是否变量包含一个值，如果他们这样做，构建查询像这样：

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

这篇关于使用PHP变量创建动态MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！