多个下拉列表动态查询AJAX PHP MySQL [英] Multiple Dropdowns Dynamic Query AJAX PHP MySQL

问题描述

我当前正在创建一个多重下拉查询，用户可以通过(3)个因素查询返回结果，我的问题是如何以一种有效的方式做到这一点，所以我不会模糊地编写多个可能的MySQL查询.

I am currently creating a multiple dropdown query where the user can query by (3) factors for returning results, my issue is how can I do this in an efficient manner so I am not obscurely writing multiple possible MySQL Queries.

<select name="class"> 
<OPTION VALUE='any'>Choose Class
<option value="a">Block A</option>
<option value="b">Block B</option>
<option value="c">Block C</option>
</select>

 <select name="category"> 
 <OPTION VALUE='any'>Choose Type
 <option value="math">Math</option>
 <option value="science">Science</option>
 <option value="history">History</option>
 </select>

如何成功创建一个MySQL查询，该查询将在缺少变量的情况下正确选择正确的参数.例如，如果他们选择执行第一个下拉菜单，而仅搜索类"，而不选择第二个下拉菜单.我希望能够执行第一个查询，第二个查询或同时执行这两个查询.我已经写了PHP，Ajax，只是对MySQL查询的正确结构感到困惑.

How can I successfully create a MySQL query that will correctly select the right parameters in the case that a variable is missing. In example, if they choose to do the first dropdown and only search for the "class" and not choose the second dropdown. I want to be able to do the first query, the second query or both of them. I have the PHP, Ajax written, I'm just stumped as to the correct structure of the MySQL query.

推荐答案

将"WHERE"子句条件放在一个数组中.我会这样:

Put your "WHERE" clause conditions in one array. I would do like this:

// filter out invalid values is important
$valid_class = array('a', 'b', 'c');
$valid_category = array('math', 'science', 'history');

// initialize array for WHERE clause conditions
$where = array('TRUE');

if (in_array($_POST['class'], $valid_class))
{
    $where[] = 'class = "' . $_POST['class'] . '"';
}

if (in_array($_POST['category'], $valid_category))
{
    $where[] = 'category = "' . $_POST['category'] . '"';
}

// use the array with the "implode" function to join its parts
$sql = 'SELECT * FROM table WHERE ' . implode(' AND ', $where);

您可能希望使用比"TRUE"更有趣的东西来初始化 $ where 数组(在这种情况下，如果用户既不按类别也不按类别进行过滤，则是因为我们不希望使用空的$ where数组到达最后一行).例如:

You may want to initialize the $where array with something more interesting than "TRUE" (which is there in case the user does not filter by class neither category, because we don't want an empty $where array reaching the last line). For example:

$where = array();
$where[] = 'name = "' . mysql_real_escape_string($_POST['name']) . '"';

这篇关于多个下拉列表动态查询AJAX PHP MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！