Page 1 of 1

100% possitive this should work

Posted: Thu Aug 06, 2009 11:57 pm
by orchid1
this is killing me because it should definitly work but it does not
Im simply trying to add a "AND" conditional to a sql query before a "LIKE" conditional

Code: Select all

 
//this is the basic set up in generic form so you can see it with a little less code and grasp the concept 
 
        $sWhere = "WHERE status = 'Active' AND label LIKE  '%".mysqli_real_escape_string($connect, $xVariable)."%' OR ".
                        "status = 'Active' AND point LIKE '%".mysqli_real_escape_string($connect, $x2Variable )."%' OR ".
                        "status = 'Active' AND city LIKE '%".mysqli_real_escape_string($connect, $y2variable)."%'; ";
    }
    
    $sQuery = "
        SELECT * 
        FROM   camp 
        $sWhere 
    ";
 
//this is the real form
 
 
        $sWhere = "WHERE status = 'Active' AND label LIKE '%".mysqli_real_escape_string($gaSql['link'], $_GET['sSearch'] )."%' OR ".
                        "status = 'Active' AND point LIKE '%".mysqli_real_escape_string($gaSql['link'], $_GET['sSearch'] )."%' OR ".
                        "status = 'Active' AND city LIKE '%".mysqli_real_escape_string($gaSql['link'], $_GET['sSearch'] )."%'; ";
    }
    
    $sQuery = "
        SELECT *
        FROM   camp 
        $sWhere 
    ";
 
 
query works great when I don't incorporate the "AND" conditional statement
basicly the AND statement isn't even considered I don't get an error or anything
it basicly ignores it so it could have it or not have and the statement is overlooked entirely

any ideas???

Re: 100% possitive this should work

Posted: Fri Aug 07, 2009 12:02 am
by orchid1
to be clearer
the AND statement has no effect on the query result

if I add the AND statement I get the exact same result as the statement when I remove the AND conditional


very frusterated

please help

Re: 100% possitive this should work

Posted: Fri Aug 07, 2009 1:52 am
by cpetercarter
You need some brackets, I think!

Code: Select all

WHERE
(status = 'a' AND label LIKE 'x') OR
(status = 'b' AND point LIKE 'y') OR
(status = 'c' AND city LIKE 'z')

Re: 100% possitive this should work

Posted: Fri Aug 07, 2009 2:40 am
by orchid1
I think i tried that "Brackets" already I might try it in the morning again when I have a fresh head on my shoulders will get back to this by tomorrow evening
thank you for the idea i think I have a new approach I might just cut it up the code some more almost like exploding it and combining the little parts again
I find that this helps more often then it should :dubious:

I'll post again tomorrow evening PST

Re: 100% possitive this should work

Posted: Fri Aug 07, 2009 3:27 am
by VladSun
cpetercarter wrote:You need some brackets, I think!

Code: Select all

WHERE
(status = 'a' AND label LIKE 'x') OR
(status = 'b' AND point LIKE 'y') OR
(status = 'c' AND city LIKE 'z')
Nope. It's the same as
[sql]WHERESTATUS = 'a' AND label LIKE 'x' ORSTATUS = 'b' AND point LIKE 'y' ORSTATUS = 'c' AND city LIKE 'z'[/sql]
because the AND operator has higher precedence than the OR operator.

@orchid1
if I add the AND statement I get the exact same result as the statement when I remove the AND conditional
Maybe because the status of all records is set to 'Active'?

Your query should work, though it could be rewritten in a shorter form:

Code: Select all

"WHERE 
   status = 'Active' 
   AND 
   (
      label LIKE  '%".mysqli_real_escape_string($connect, $xVariable)."%' 
      OR 
      point LIKE '%".mysqli_real_escape_string($connect, $x2Variable )."%' 
      OR 
      city LIKE '%".mysqli_real_escape_string($connect, $y2variable)."%'; 
   )
";
PS: Please, use [ sql ], [/ sql] or [ php ] [/ php ] BB code tags instead of [ code ] BB code tags.

Re: 100% possitive this should work

Posted: Fri Aug 07, 2009 12:45 pm
by orchid1
the last comment worked

thought I had tried this already

maybe I'm just too tired or i left a dot out or something maybe a ' or " :?:
who know thanks for the help

Re: 100% possitive this should work

Posted: Fri Aug 07, 2009 1:30 pm
by prometheuzz
orchid1 wrote:the last comment worked

thought I had tried this already
...
Obviously not!