OR sticking it's or in

hinksta · 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

Raven · Posted: Wed Dec 06, 2006 9:28 pm

That cannot and does not happen Wink

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

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.

montego · 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?

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

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

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

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

montego · 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 ...

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

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

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

I see, that's made everything into AND.

evaders99 · 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

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

I think I have it, still testing

Code:

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

montego · 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".

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

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')

djmaze · 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%')

Only registered users can see links on this board!
Get registered or login to the forums!

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

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";

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

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

montego · 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]%'