CODEWITHSUNDEEP
phpmysql
Kmiles1990123
Kmiles1990123

Reputation: 189

SQL; OR syntax not working

Alright So I have a sql command. Please see below.

$sql = "SELECT * FROM orders 
        WHERE loggedtime BETWEEN :time AND :times 
        AND name LIKE :keyword OR odryname LIKE :keyword OR pdryname LIKE :keyword 
        OR jlc LIKE :keyword OR odrphone LIKE :keyword 
        OR biid LIKE :keyword OR dstreet LIKE :keyword ";
$q=$con->prepare($sql);
$q->bindvalue(":keyword", "%".$keyword."%");

So the command works until the first OR in it... It will check if the logged time is what I put in and the NAME is like the keyword..

The only part of the command that should be constant is WHERE loggedtime = :time the rest should be based on if the keyword matches... But it should not display any results at all if the time on that order is incorrect.

Upvotes: 1

Views: 78

Answers (3)

RomanPerekhrest
RomanPerekhrest

Reputation: 92854

As you specified, there are two main conditions which are regarded to logged time and other fields that compare with keyword.
You need to group logically related operators with braces.
When combining these conditions(AND condition and OR condition ), it is important to use round brackets so that the database knows what order to evaluate each condition.
Try this query:

$sql = "SELECT * FROM orders 
        WHERE loggedtime BETWEEN :time AND :times 
        AND (name LIKE :keyword OR odryname LIKE :keyword OR pdryname LIKE :keyword 
        OR jlc LIKE :keyword OR odrphone LIKE :keyword 
        OR biid LIKE :keyword OR dstreet LIKE :keyword) ";

Upvotes: 1

helmbert
helmbert

Reputation: 37984

The logical AND operator has a higher precedence than the logical OR. This is documented in detail in the MySQL documentation.

So without explicit braces, your current statement actually reads like:

SELECT * FROM orders 
    WHERE (loggedtime BETWEEN :time AND :times 
    AND name LIKE :keyword) OR odryname LIKE :keyword OR pdryname LIKE :keyword 
    OR jlc LIKE :keyword OR odrphone LIKE :keyword 
    OR biid LIKE :keyword OR dstreet LIKE :keyword

As OR has a lower precedence than AND, you'll need braces to group all the ORed conditions together:

SELECT * FROM orders 
    WHERE loggedtime BETWEEN :time AND :times 
    AND (name LIKE :keyword OR odryname LIKE :keyword OR pdryname LIKE :keyword 
    OR jlc LIKE :keyword OR odrphone LIKE :keyword 
    OR biid LIKE :keyword OR dstreet LIKE :keyword)

Upvotes: 1

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

put this into () loggedtime BETWEEN :time AND :times so your query should be :

$sql = "SELECT * FROM orders 
WHERE (loggedtime BETWEEN :time AND :times) 
AND name LIKE :keyword OR odryname LIKE :keyword OR pdryname LIKE :keyword 
OR jlc LIKE :keyword OR odrphone LIKE :keyword 
OR biid LIKE :keyword OR dstreet LIKE :keyword ";

Upvotes: 1

Related Questions