Author |
Message |
hinksta
Worker
Joined: Dec 23, 2005
Posts: 226
Location: UK
|
Posted:
Wed Dec 06, 2006 9:06 pm |
|
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
Site Admin/Owner
Joined: Aug 27, 2002
Posts: 17088
|
Posted:
Wed Dec 06, 2006 9:28 pm |
|
That cannot and does not happen |
|
|
|
|
hinksta
|
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. |
|
|
|
|
montego
Site Admin
Joined: Aug 29, 2004
Posts: 9457
Location: Arizona
|
Posted:
Thu Dec 07, 2006 6:16 am |
|
|
|
|
hinksta
|
Posted:
Thu Dec 07, 2006 6:21 am |
|
I want them to be AND but I'm getting OR results |
|
|
|
|
hinksta
|
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 |
|
|
|
|
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
|
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 |
|
|
|
|
hinksta
|
Posted:
Thu Dec 07, 2006 9:10 am |
|
I see, that's made everything into AND. |
|
|
|
|
evaders99
Former Moderator in Good Standing
Joined: Apr 30, 2004
Posts: 3221
|
Posted:
Thu Dec 07, 2006 11:18 am |
|
|
|
|
hinksta
|
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]%')";
|
|
|
|
|
|
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
|
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') |
|
|
|
|
djmaze
Subject Matter Expert
Joined: May 15, 2004
Posts: 727
Location: http://tinyurl.com/5z8dmv
|
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! ]
[ Only registered users can see links on this board! Get registered or login! ] |
|
|
|
|
hinksta
|
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";
|
|
|
|
|
|
hinksta
|
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 |
|
|
|
|
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]%' |
|
|
|
|
|