过于具体的mysql查询无法正常工作

Question

I have written a query to find similar ids based on tags from a current id, much like amazons, you would also like. The problem is, in my example I want to exclude bookid 30 from this search.

Here is my query:

note: $similar is basically a string filled with tags built up with a few "like %item% or"

$query = "SELECT * FROM books
          WHERE bookid !=30
          AND {$similar}
          AND visible ='1'
          AND level ='2'
          LIMIT 3";

The part that is not working is where bookid!= I've tried IS NOT, <> reordering and what ever else I could find. This query still works it outputs three rows but still includes bookid 30

Can someone explain what is going here? is my query becoming too intricate and need to be restructured?

The poor alternative i've came up with was making the limit 4 items sending it to a while loop to fetch each row and exclude the bookid i don't need. This just seems pointless when I know it can be done in the query.

Ive fixed this by rebuilding my query and $similar string Before

SELECT * FROM books WHERE bookid !=30 AND tags LIKE '%one%' OR tags LIKE '% two%' OR tags LIKE '% three%' AND visible ='1' AND level ='2' LIMIT 3

After

SELECT * FROM books WHERE bookid !=30 AND (bookid!=30 AND tags LIKE '%one%') OR (bookid!=30 AND tags LIKE '% two%') OR (bookid!=30 AND tags LIKE '% three%') AND visible ='1' AND level ='2' LIMIT 3

One thing I did notice which is strange is bookid!= only works when there is one at the beginging and between each like or... i tried swaping each out and the query would not run, but current string works perfectly!

Thanks,

Answer 1

Make sure that your $similar variables are wrapped in parentheses.

if an OR is in the middle, it will be evaluated as

(bookid != 30 AND item like '%item1%') OR (item like '%item2%' AND visible = 1 AND level= 2)

instead of

bookid != 30 AND (item like '%item1%' OR item like '%item2%') AND visible = 1 AND level= 2