How to write mysql query to check multiple variabl

Question

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))
{

Answer 1

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

Answer 2

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

Answer 3

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.