homepage Welcome to WebmasterWorld Guest from 54.198.94.76
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Adding additional operators into a WHERE clause
Adding multiple WHERE clauses into a fulltext sql select.
mack




msg:4518105
 8:48 pm on Nov 11, 2012 (gmt 0)

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




msg:4518157
 1:16 am on Nov 12, 2012 (gmt 0)

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




msg:4518164
 1:43 am on Nov 12, 2012 (gmt 0)

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




msg:4518196
 4:07 am on Nov 12, 2012 (gmt 0)

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




msg:4518251
 10:08 am on Nov 12, 2012 (gmt 0)

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




msg:4518375
 5:57 pm on Nov 12, 2012 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved