PHP和MySQL的可选WHERE条件 [英] PHP and MySQL optional WHERE conditions

问题描述

我有以下问题:我想让用户将过滤器应用于数据库搜索. 我有三个过滤器，A，B和C.所有过滤器都可以是空"的，因为用户不关心它们，因此选择了任何".现在，我想针对数据库记录检查此查询.我使用普通的mysql查询，如

$db_q = "Select * from table where row1 = '" . $A . "' and row2 = '" . $B . "' and row3 =
'" . $C . "'";

只要用户输入特定于A，B，C的任何内容(！="any")，此方法就可以正常工作.当任何"时，我得到的是这样的:select * from表，其中ROW1 =‘任何/所有’等."我似乎无法弄清楚正确的语法(如果可能的话)，并且我想避免区分大小写(如果A == any，请执行select；如果B == empty ..依此类推).

非常感谢您的帮助.

解决方案

在查询之前，您可以使用:

$tmp = "where ";
if($A and $A!="any" and $A!="not used")
 $tmp .= "row1 = '".$A."'";
if($B and $B!="any" and $B!="not used")
 $tmp .= "AND row2 = '".$B. "'";
if($C and $C!="any" and $C!="not used")
 $tmp .= "AND row3 = '".$C."'";
$db_q = "Select * from table $tmp";

I have the following problem: I want to let a user apply filters to a DB search. I have three filters, A, B and C. All of them can be "empty", as in, the user doesn't care about them and selects "Any". Now I want to check this query against the DB records. I use a normal mysql query as in

$db_q = "Select * from table where row1 = '" . $A . "' and row2 = '" . $B . "' and row3 =
'" . $C . "'";

This works fine as long as the user enters anything specific for A,B,C (!= "any"). When "any" is selected, i get something like this: "Select * from table where row1 = "any/all" etc." I can't seem to figure out the correct syntax (if it's even possible) and I would like to avoid messy case distinction (if A == any, perform select; if B == empty.. and so on).

Any help is much appreciated.

解决方案

Before your query you could use:

$tmp = "where ";
if($A and $A!="any" and $A!="not used")
 $tmp .= "row1 = '".$A."'";
if($B and $B!="any" and $B!="not used")
 $tmp .= "AND row2 = '".$B. "'";
if($C and $C!="any" and $C!="not used")
 $tmp .= "AND row3 = '".$C."'";
$db_q = "Select * from table $tmp";

这篇关于PHP和MySQL的可选WHERE条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！