Page 1 of 1

php/mysql multi-column search help please. I have code here

Posted: Mon Apr 19, 2004 7:52 pm
by idotcom
Hi,

I am trying to get the best search results I can get with the current DB/site. The code I wrote works but it does not work properly.

Example:

the word ( website ) is in the database
the word ( killer ) is not in the database

I search for( website )and I get results

I search for( killer website ) and I get results

I search for( website killer ) and get no results

What seems to be happening is that the query is in a loop and if the last search word in the string was not found, it comes back with nothing. Even if I search for 3 words I know exist and come up individually, but add a word I know is not in the database and it comes up with nothing.


How could I fix this?????? Its driving me nuckin futts!

Heres the code Ive tried.

Code: Select all

<?php
if(isset($search)) 
{
$search_words = explode(" ", $search);
$sql = "SELECT * FROM products";
foreach ($search_words as $word) 
{
$sql .= " WHERE product_title LIKE '%$word%' OR product_desc LIKE '%$word%'";
}
$sql .= " AND in_stock='1'"; 
}
?>
And tried this too..

Code: Select all

<?php
if(isset($search)) 
{
$search_words = explode(" ", $search);
foreach ($search_words as $word) 
{
$sql = "SELECT * FROM products WHERE product_title LIKE '%$word%' OR product_desc LIKE '%$word%' AND in_stock='1'";
} 
}
?>
I think its the same...

Any helpful comments or ideas would be greatly appreciated.

Thank you in advance.
Chris

PS.. got code example?

Posted: Mon Apr 19, 2004 8:07 pm
by feyd
I'm no sql guru but I think this may work, although might be a bit brute force :)

Code: Select all

if(isset($search))
{
  $search_words = preg_split("/\s+/",trim($search));
  $sql = "SELECT * FROM products WHERE";
  $first = true;
  foreach($search_words as $word)
  {
    if(!empty($word))
    {
      if(!$first) $sql .= " OR";
      $sql .= " product_title LIKE '%$word%' OR product_desc LIKE '%$word%'";
      $first = false;
    }
  }
  $sql .= " AND in_stock='1'";
}