I have a form that submits the following:
State
City
Category
My query needs to do the following:
If only state selected - show state results
If state and cat selected - show state and cat results
if state and city selected - show state and city results
if state, city and cat are selected - show state, city, cat results
The catch is when they submit the city, 3 different fields need to be checked for a match
This is my query and it doesnt work, I have tried 100 different variations. Sorry for being such a newbie. Thanks for help in advance.
$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))
{
I personally am not very good at doing MySQL query strings, I generally use an sql compiler class similar to this (keep in mind, this is just a quick one). It allows for binding, which you require, especially with $_GET values like you have:
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);
Gives you:
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
)
Note, this requires you to use a proper connection (PDO works best in this case).
Looks like operator precedence? When you are grouping related conditions where only one of which must be true, you want something of this form:
(column1 = 'value' OR column2 = 'value')
AND (column3 = 'value' OR column4 = 'value')
You may want to read up on SQL injection, too.
your code contains typo , use
"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 ";
Check on 1st line of your code you have city1='$_GET[city] OR ....... which is wrong city1='$_GET[city]' ORis correct
