使用 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).

最好的方法是什么?

目前我有这样的事情:

    $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屋！