PHP Search Query

[gotornot]

Hi All.

I am looking to search a database of products based upon someone typing the name of the product they are looking for.

I am using the below SQL request:

 

Unfortunately this only shows what ever your typing and anything before or after (%) is there any way that it could also search unplaced string instances?

For example

$q = "SELECT * FROM products WHERE prod_name LIKE '%$id%' order by fee asc LIMIT 20";

 

If i type black ops ps3 it brings back search result until i get to the PS3 part. because entries in the db are "black ops for the PS3" because i havent conformed it says no results.

I need it to show all results.

is there another way? or can someone point me in the right direction?

[Jamdog]

I've never done exactly what you are looking for, but I believe you need to split up the query into separate words, so that you can look for each word individually, like:

function organise_search_terms( $searchstr ) {
    $search_terms = explode(" ", $searchstr);
    if ( ! is_array( $search_terms ) ) return $search_terms;

    $string = array();
    foreach ( $search_terms as $key => $val ) {
        $string .= "+$val* ";
    }

    return $string;
}

$q = "SELECT * FROM products WHERE MATCH (prod_name) AGAINST (" . organise_search_terms($searchstr) . ")";

 

Sorry, the above is completely untested, but might help get you started on the right path...

[gotornot]

this doesn't work

 

[givememore]

There are several ways to solve that problem.

The easiest for programming might be to replace all spaces in the searchstring by %-jokers:

"black ops ps3" => "black%ops%ps3"

 

Depending on the SQL-Server you use there are different SQL dialects, for example in Oracle:

SELECT * FROM products WHERE prod_name LIKE '%' || replace($id, ' ', '%') || '%'

(The || is the concatenate operator in Oracle's syntax)

 

The disadvantage of this solution is that the order of the given keywords must fit the product's discription. In your examle you won't get results if you search for "ps3 black ops"...

 

Consider: Some Databases are case-sensitive. So you might need to spend some thoughts on that issue, too.

 

As you can see: Such a simple question easily becomes a quite complex algorithm.

Please 'givememore' details (type of database, examples of database-entries, examples of searchstring etc) when you need further help.

 

best regards

Burkhard