AND'ing results from search

Hi y'all,

I have a somewhat working search for a site that OR's results, but I need it to AND results - for EX: blue (60 results) rug (33 results), but blue rug (21 results - because there are only 21 blue rugs). This is what I have:

$query = "SELECT * FROM search_tbl WHERE ";
for ($i=0; $i<count($keywords); $i++) {
$query .= " column1 LIKE '%".$keywords[$i]."%'".
" OR column2 LIKE '%".$keywords[$i]."%'" .
" OR column3 LIKE '%".$keywords[$i]."%'" .
" OR column4 LIKE '%".$keywords[$i]."%'";

if($i < (count($keywords) - 1)){
$query .= " AND";
}
}

$query .= " ORDER BY item_name".
" LIMIT " . ((($page-1)*$Limit) . ",$Limit");

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

This does return results for multiple keywords, but it doesn't return results that have both (if there are two keywords) in them. For example, blue (returns 30 results), rug (returns 12 results), and blue rug (should return less than 12 results depending on how many rugs are blue).

Any help?

Thanks. =)

 

Hi y'all,

I think I have an idea on how to AND the results, but before I get to that I have another problem. When I enter the queries below, I get two different results. Anyone have a clue why?

They're both the same keywords, just in reverse. Also, it appears that column10 is the problem - blue shows up in column10 in the query with 70 results, but doesn't show up in column10 (so, it's like it's not looking in column10 when it's queried 1st) in the query with 53 results (if that makes sense?).

*****************************************

SELECT * FROM search_tbl WHERE column1 LIKE '%brown%' OR column2 LIKE '%brown%' OR column3 LIKE '%brown%' OR column4 LIKE '%brown%' OR column5 LIKE '%brown%' OR column6 LIKE '%brown%' OR column7 LIKE '%brown%' OR column8 LIKE '%brown%' OR column9 LIKE '%brown%' OR column10 LIKE '%brown%' AND column1 LIKE '%blue%' OR column2 LIKE '%blue%' OR column3 LIKE '%blue%' OR column4 LIKE '%blue%' OR column5 LIKE '%blue%' OR column6 LIKE '%blue%' OR column7 LIKE '%blue%' OR column8 LIKE '%blue%' OR column9 LIKE '%blue%' OR column10 LIKE '%blue%' ORDER BY id

Yields 70 results

******************************************

SELECT * FROM search_tbl WHERE column1 LIKE '%blue%' OR column2 LIKE '%blue%' OR column3 LIKE '%blue%' OR column4 LIKE '%blue%' OR column5 LIKE '%blue%' OR column6 LIKE '%blue%' OR column7 LIKE '%blue%' OR column8 LIKE '%blue%' OR column9 LIKE '%blue%' OR column10 LIKE '%blue%' AND column1 LIKE '%brown%' OR column2 LIKE '%brown%' OR column3 LIKE '%brown%' OR column4 LIKE '%brown%' OR column5 LIKE '%brown%' OR column6 LIKE '%brown%' OR column7 LIKE '%brown%' OR column8 LIKE '%brown%' OR column9 LIKE '%brown%' OR column10 LIKE '%brown%' ORDER BY id

Yields 53 results

 

Never mind. Doing the "AND" makes it return consistent results. =) Would like some help still, though. Right now I have it set to loop for 2 keywords (and please don't make fun of my loops - it's always been a weakness), but I would like it to accept a flexible number of keywords (who knows what a customer will type) and iterate through the loop. Help?

$query = "SELECT * FROM search_tbl WHERE";
$counter=0;
for ($col_inc=1; $col_inc<11; $col_inc++){
$and_col=1;
for ($and_col=1; $and_col<11; $and_col++){
$query .= " column".$col_inc." LIKE '%".$keywords[$i+0]."%'".
" AND column".$and_col." LIKE '%".$keywords[$i+1]."%'";
$counter++;
//echo $counter;
if ($counter < 100){
$query .= " OR";
}
}
}
//echo $query;
$query .= " ORDER BY item_name".
" LIMIT " . ((($page-1)*$Limit) . ",$Limit");

 

Ok, just realized that the number of keywords raises the search code (the part the computer generates) by a factor of 10 (I have 10 columns). So, (and I've echoed this to see it) 2 keywords generates 4 pages (when copied and pasted in to Word) of code, and 3 would generate 40 pages, and 4 - 400 pages, etc. Think I'm going to limit it to 3 or 4 keywords. Still would like it flexible, though.

 

Didn't realize I had a redundant declaration of $and_col=1; Please ignore.