OR sticking it's or in

Worker Joined: Dec 23, 2005 Posts: 226 Location: UK

Having a problem with OR sticking it's or in

I'm trying to get this query to search two fields, it seems to work but I think It's changing the AND to an OR.

Code:




$query = "SELECT date,title,description FROM " . $prefix . "_news  " .


"WHERE description LIKE '%".$keywords['0']."%' OR title LIKE '%".$keywords['0']."%'";


for ($i=1; $i<count($keywords); $i++) {


$query = $query." AND description LIKE '%".$keywords[$i]."%'";


$query = $query." AND title LIKE '%".$keywords[$i]."%' ";


}


$query = $query." ORDER BY date DESC "." LIMIT $offset, $rowsPerPage";


$result2 = mysql_query($query) or die(mysql_error());


$keywords = explode(" ", $search);


$query = "SELECT COUNT(date) AS numrows FROM " . $prefix . "_news  " .


"WHERE description LIKE '%".$keywords['0']."%' OR title LIKE '%".$keywords['0']."%' ";





for ($i=1; $i<count($keywords); $i++) {


$query = $query." AND description LIKE '%".$keywords[$i]."%'";


$query = $query." AND title LIKE '%".$keywords[$i]."%' ";


}

hope you can help

Site Admin/Owner Joined: Aug 27, 2002 Posts: 17088

That cannot and does not happen Wink

Posted: Thu Dec 07, 2006 5:09 am

Do you mean the code or that AND cannot become OR?

If I do a search for a known title, somehow I'm getting the title plus other OR results.

registered · Posted: Thu Dec 07, 2006 6:16 am

How come your for loop is adding other keywords using "AND" if you want these "OR"d?

Posted: Thu Dec 07, 2006 6:21 am

I want them to be AND but I'm getting OR results

Posted: Thu Dec 07, 2006 6:25 am

This only happens when I add a second field to search so i'm thinking my SELECT is wrong

Posted: Thu Dec 07, 2006 6:33 am

Try something like this instead:

WHERE (description LIKE '%keyword0%' OR title LIKE '%keyword0%) AND (description LIKE '%keyword1%' OR title LIKE '%keyword1%) AND ...

Posted: Thu Dec 07, 2006 8:57 am

I'm prety sure that stoped the problem with OR but I didn't get any results from title

Posted: Thu Dec 07, 2006 9:10 am

I see, that's made everything into AND.

registered · Posted: Thu Dec 07, 2006 11:18 am

Yep the order of operations is changed with the parenthesis. You should be careful when mixing AND and OR - parenthesis makes it easier to figure out

Posted: Thu Dec 07, 2006 4:20 pm

I think I have it, still testing

Code:

"WHERE (description LIKE '%$keywords[0]%' || title LIKE '%$keywords[0]%')";

Posted: Fri Dec 08, 2006 6:53 am

Well, || is the same as OR, so not sure that is going to help you. You really have to think out what it is you want this to do. Use the parenthesis to make it easier to see the logic.

Post here in words what a typically query should look like, results-wise. Good logic always starts with a good statement of what I call the "use model".

Posted: Fri Dec 08, 2006 7:43 am

It looks to be working ok, I guess replacing OR with || eradicates the confusion between AND and OR

The idea is to find any number of keywords (using AND) in the table fields 'title' and/or 'description' (not AND/OR)

Example

(Search 'title' AND 'word1') also search ('description' AND 'word1')
then
(Search 'title' AND 'word2') also search ('description' AND 'word2')

Posted: Fri Dec 08, 2006 8:34 am

Code:




SELECT * FROM table


WHERE title LIKE IN ('word1', 'word2')


OR description LIKE IN ('word1', 'word2')

Code:




SELECT * FROM table


WHERE (title LIKE '%word1%' OR title LIKE '%word2%')


OR (description LIKE '%word1%' OR description LIKE '%word2%')

http://dev.mysql.com/doc/refman/4.1/en/functions.html
http://dev.mysql.com/doc/refman/4.1/en/string-comparison-functions.html

Posted: Fri Dec 08, 2006 2:20 pm

ok I've gone for boolean but still can't get OR and AND to work together without getting OR results.

It may be better to use OR with BOORLEAN sorting the results, that means more reading.

This works ok, is using || a problem or wrong ?

Code:

$query = "SELECT date,title,description FROM " . $prefix . "news  " .


"WHERE MATCH (description,title) AGAINST('%$keywords[0]%' IN BOOLEAN MODE)";





for ($i=1; $i<count($keywords); $i++) {


$query = $query." AND (description LIKE '%$keywords[$i]%' OR title LIKE '%$keywords[$i]%')";


}


$query = $query." ORDER BY date DESC LIMIT $offset, $rowsPerPage";

Posted: Fri Dec 08, 2006 2:37 pm

and then as I read the post back I looked up and saw djmaze's OR

That must have been the only way I didn't try

Posted: Sun Dec 10, 2006 9:46 am

Yes, dj's second example could work as well. But, really, is the bottom line here that you really want to find the keywords in title or in description? If so, there is no reason why you cannot just string them altogether to straight ORs like this:

WHERE title LIKE '%keyword[0]%'
OR title LIKE '%keyword[1]%'
OR title LIKE '%keyword[n]%'
OR description LIKE '%keyword[0]%'
OR description LIKE '%keyword[1]%'
OR description LIKE '%keyword[n]%'