Adding additional operators into a WHERE clause

Adding multiple WHERE clauses into a fulltext sql select.

[mack]

I appreciate this is not entirely php specific, but I think the fact that I am using $variables as values may effect the syntax...

$result = mysql_query("SELECT *,
MATCH (ROW1, ROW2, ROW3)
AGAINST ('$term' IN BOOLEAN MODE) AS SCORE
FROM MY_TABLE
WHERE (item1 LIKE ('%$something%') OR
item2 = '$somethingelse' OR
item3 = '$anotherthing') AND
MATCH (ROW1, ROW2, ROW3)
AGAINST ('$term' IN BOOLEAN MODE)
ORDER BY SCORE LIMIT 10");

The problem I am having is with the OR operators. As you can see I have enclosed them all within a parenthesis But the syntax I am using must need tweaking, It never gets past a LIKE.

What is the correct syntax to use when grouping multiple OR operators with some having a LIKE requirement.

Thanks in advance.

Mack.

[vincevincevince]

A few things to change:-
1) Enclose field names with ` `
2) Catch the error, if any, using:

$result=mysql_query("....") or die(mysql_error().' at line '.__LINE__.' of '.__FILE__);

3) The () isn't required for ('%$something%')

However, your example should run okay, so far as I can see

Try chopping it up and testing each bit on its own:-

select count(*) from MY_TABLE where item1 LIKE ('%$something%')

select count(*) from MY_TABLE where item2 = '$somethingelse'

select count(*) from MY_TABLE where item3 = '$anotherthing'

select count(*) from MY_TABLE where (item1 LIKE ('%$something%') OR item2 = '$somethingelse' OR item3 = '$anotherthing')

select count(*) from MY_TABLE where MATCH (ROW1, ROW2, ROW3)
AGAINST ('$term' IN BOOLEAN MODE)

Basically, check that you have results at each stage - and if you don't - then go into depth in fixing that stage.

[mack]

Thanks vincevincevince :)

I'm gland I am not the only one that can't see a reason why it doesn't run, It appears that all the OR operations aren't being analysed unless the LIKE is matched.

Your suggestion of chopping it up to make sure the values are being gathered. I echo the values within a loop just after the select, so I know they are being gathered from the query.

What I have done as a debug measure is to build two separate queries. The first only checks for LIKE within the WHERE clause, them if no rows returned it runs the second query. Problem with this approach is there are over a million rows, so the fewer SELECT * the better.

Would it be a good idea to try a sub query within the where clause simple for the LIKE part?

Mack.

[vincevincevince]

Hi mack; you might change the order of the OR clauses, to put the LIKE last, seeing you think it is not checking past that first LIKE?

I'd try to avoid a subquery, unless this is a small data set and performance isn't a priority.

This is what I'd do at this point:
1) Change mysql_query() for die(), so I see the exact string being sent to MySQL, copy this and...
2) Log into my MySQL command line interface;
Run:

EXPLAIN EXTENDED ...the query I copied... \G

Then:

SHOW WARNINGS \G

3) Check what's happening, and see how the query was internally rewritten (under SHOW WARNINGS). Pay careful attention to the row counts at each stage. If you can't figure it out - try to paste this output for review.

[penders]

Presumably the executed query (with values replaced) looks as expected and is valid? PHP vars are suitably escaped?

As vincevincevince suggests, I would certainly remove the parenthesis around ('%$something%'). It should be

LIKE '%$something'

. In fact, the parenthesis around the other parts of the WHERE clause might also be unnecessary (although they make it easier to read) since AND has precedence over OR.

Just a minor confusion... MATCH (ROW1, ROW2, ROW3) - ROW1 etc. are presumably column names?

[mack]

Thank you all for the replies :)

WHERE (item1 LIKE ('%$something%') OR
item2 = '$somethingelse' OR
item3 = '$anotherthing')

It appears the problem wasn't so much the query, but my logic as to how it would work, specifically the LIKE statement.

In the above example I use a wild-card on either side of the variable. In the event of that field being empty, it would still be LIKE ('%%') so technically that OR would always be true, because it could literally be anything, That is why it was always true, even if the other OR operators where also true, hence my confusion.

Can't believe it took me almost 4 days to spot that :)

Mack.