我有一个提交以下内容的表单:
州城市类别
我的查询需要执行以下操作:
如果只选择状态-显示状态结果
如果选择了状态和cat-显示状态和cat结果
如果选择了州和城市-显示州和城市的结果
如果选择了州、市和猫-显示州、市、猫的结果
问题是当他们提交城市时,需要检查3个不同的字段是否匹配
这是我的问题,它不起作用,我已经尝试了100种不同的变体。很抱歉我是个新手。感谢您提前提供帮助。
$sql = "SELECT * FROM items WHERE state=$st AND city1='$_GET[city] OR
city2='$_GET[city]' OR city3='$_GET[city]' OR cat='$_GET[cat]'
AND active='1'
ORDER BY item_id DESC LIMIT $limitvalue, $limit ";
$result=mysql_query($sql);
while ($prow=mysql_fetch_array($result))
{
我个人不太擅长MySQL查询字符串,我通常使用类似的sql编译器类(请记住,这只是一个快速的类)。它允许绑定,这是您所需要的,尤其是使用$_GET
值,如
class ItemQueue
{
protected $sql;
protected $wherevals;
public $bind;
public $compiled;
public function select($values = false)
{
$this->sql[] = "select";
if($values == false)
$this->sql[] = "*";
else
$this->sql[] = $values;
return $this;
}
public function from($table = false)
{
if($table ==false)
return $this;
$this->sql[] = "from `$table`";
return $this;
}
public function where($values = array(),$op = 'and')
{
if(!empty($values)) {
$this->sql[] = 'where';
foreach($values as $key => $values) {
$this->wherevals[] = $key.' = :'.$key;
// Bind values for injection protection
$this->bind[":$key"] = $values;
}
$this->sql[] = implode(" $op ",$this->wherevals);
// This part is a bit jenky but you get the idea
$this->sql[] = "and active = '1'";
}
return $this;
}
public function customsql($values = false)
{
if($values != false) {
$this->sql[] = $values;
}
return $this;
}
public function Fetch()
{
// Implode entire sql statement
$this->compiled = implode(" ", $this->sql);
return $this;
}
}
// Post/Get values
$_POST['cat'] = 'cattest';
$_POST['city'] = 'Reno';
$_POST['state'] = 'Nevada';
// Arbitrary limits
$limit = 1;
$limitvalue = 1;
// Create instance
$tester = new ItemQueue();
// Just set some array filtering/validating
if(isset($_POST['cat']) && !empty($_POST['cat']))
$array['cat'] = $_POST['cat'];
if(isset($_POST['city']) && !empty($_POST['city']))
$array['city'] = $_POST['city'];
if(isset($_POST['state']) && !empty($_POST['state']))
$array['state'] = $_POST['state'];
// Make the query
$query = $tester->select()->from("items")->where($array,"or")->customsql("ORDER BY item_id DESC LIMIT $limitvalue, $limit");
// Here is the sql statement
echo $query->Fetch()->compiled;
// Bind array
print_r($query->bind);
为您提供:
select * from `items` where cat = :cat or city = :city or state = :state and active = '1' ORDER BY item_id DESC LIMIT 1, 1
Array
(
[:cat] => cattest
[:city] => Reno
[:state] => Nevada
)
注意,这需要使用正确的连接(PDO在这种情况下效果最好)
看起来像运算符优先级?当你对相关条件进行分组时,其中只有一个必须为真,你需要这种形式的东西:
(column1 = 'value' OR column2 = 'value')
AND (column3 = 'value' OR column4 = 'value')
您可能还想了解SQL注入。
您的代码包含拼写错误,请使用
"SELECT * FROM items WHERE state=$st AND city1='$_GET[city]' OR
city2='$_GET[city]' OR city3='$_GET[city]' OR cat='$_GET[cat]'
AND active='1'
ORDER BY item_id DESC LIMIT $limitvalue, $limit ";
在代码的第一行检查您的city1='$_GET[city] OR .......
是错误的city1='$_GET[city]' OR
是正确的