Help to get search results to work

[beanymanuk]

Hi I have a search facility on a site and it works all ok if someone types in "Ford" or "focus" separately but if someone searches "ford focus" then no results are results

 

I have tried a few variations of SQL but to no avail

 

Does anyone know the correct structure to get my search working

 

(((UPPER(node_data_field_code.field_make_value)) LIKE ('%$filters%')) OR
((node_data_field_code.field_carmodel_value) LIKE ('%$filters%')) OR

 

Thankyou in advance 

[monkeytooth]

I would say take your search query and explode() using the space. ie:

<?php
$mySearchString = explode(" ", $originalString);
?>

 

Then based on that I would run a foreach to create a query string dynamicly something like:

 

<?php
$sqlQueryString = "SELECT * FROM my_table WHERE ";
$filterCount = count($mySearchString);
$i = 0;
foreach($mySearchString as $value)
{
    if($i == $filterCount){break;}
    $sqlQueryString .= "my_column LIKE '%".mysql_real_escape_string($value)."%'";
    if(($filterCount > 1) AND ($i < $filterCount)){$sqlQueryString .= " OR ";}
    $i++;
}
echo $sqlQueryString; //just to see what the query looks like after its created
?>

[beanymanuk]

Problem with that way is the searched words must tally up with the order of the SQL query

In total there is about 14 fields in the database that I want to search by the users inputted search whatever it may be

 

eg search of 'Ford Focus' or 'turbo focus' the search input could be anything

(((UPPER(node_data_field_code.field_make_value)) LIKE ('%$filters%')) OR
                                    ((node_data_field_code.field_carmodel_value) LIKE ('%$filters%')) OR
                                    ((node_data_field_code.field_engine_value) LIKE ('%$filters%')) OR
                                    ((node_data_field_code.field_year_value) LIKE ('%$filters%')) OR
                                    ((node_data_field_code.field_key_value) LIKE ('%$filters%')) OR
                                    ((node_data_field_code.field_code_value) LIKE ('%$filters%')) OR .....

[monkeytooth]

in essense what I said still stands. You just end up with a super sized query string which really isnt a problem per say. You would just have to redo the line

 

$sqlQueryString .= "my_column LIKE '%".mysql_real_escape_string($value)."%'";

 

to better fit your needs as you specify something like

 

$sqlQueryString .= "my_column1 LIKE '%".mysql_real_escape_string($value)."%' OR ";
$sqlQueryString .= "my_column2 LIKE '%".mysql_real_escape_string($value)."%' OR ";
$sqlQueryString .= "my_column3 LIKE '%".mysql_real_escape_string($value)."%'";

 

because as you have it right now. $filters is what it is. its either Ford, Ford Focus, Turbo Ford, something else.. with no breakage of the independent values as I am trying to show with the use of explode and foreach. As you have it, if you type Ford and theres values on any of those columns with ford in them anywhere you will yield a return result. Otherwise Something like Ford Focus Turbo won't show up cause your looking for that term specificly with anything on either side of it but none the less that exact term all the while. Of course again your going to have to refine the results in the example I show as you may get duplicate results Searching for Ford and then Focus may show return the same row.

 

Bottom line is there is no special means of making the query much shorter and simpler like some tutorials would have you believe as those tutorials really go into the basics of the basics. So in the end your going to have a search query a mile long if someone types out a full range of keywords in the one text box. Only other way to go per say is rebuild your form from a single text input that someone can put anything in, to a multi part form thats got hardcoded constraints to search from. The possibility of the supersized query isn't a problem though. If it gets big enough it may take an extra second or three to run the query but in the end anything you do to try an harness the concept the way you want is going to take process time.