p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

beginning_php thread: Setting null and empty spaces in searches


Message #1 by Jefferis Peterson <jefferis@p...> on Wed, 25 Sep 2002 17:14:06 -0400
I'm setting up a search of a database using keywords  [ $keysearch] and it
works if I put only one word in the keysearch field, but fails if I put two
legitmate words in it.  How do I go about creating multiple word search
criteria from a single field?

~~~~~~~~~~~~
Jefferis Peterson, Pres.
Web Design and Marketing
http://www.PetersonSales.net
Tel .  xxx-xxx-xxxx
ICQ 19112253

http://www.Slippery-Rock.com - 7,000 hits per year

Message #2 by "Nikolai Devereaux" <yomama@u...> on Wed, 25 Sep 2002 14:29:29 -0700
At the simplest form, you'd typically say

SELECT ... WHERE your_col LIKE '%$keysearch%'

If you have several words to search for, you'll need a separate WHERE
expression for each.

  $patt = '/\s+/';
  $keywords = preg_split($patt, $keysearch);

  $wheres = array();
  foreach($keywords AS $kw)
  {
     $wheres[] = "col LIKE '%{$kw}%'";
  }

  $where_clause = "\n  WHERE " . join("\n    OR", $wheres);
  $query        = "SELECT ... $where_clause";


If $keysearch was "Hello   world  how	are you";

$keywords would be set to an array with 5 items: 'Hello', 'world', 'how',
'are', and 'you'.

For each of these words, we create a SQL WHERE expression.
     col LIKE '%Hello%'
     col LIKE '%world%'
       etc...

Finally, we join all these separate expressions with OR.  The final query will
look something like

"SELECT ...
  WHERE col LIKE '%Hello%'
     OR col LIKE '%world%'
     OR col LIKE '%how%'
     OR col LIKE '%are%'
     OR col LIKE '%you%'"



Take care,

Nik

Message #3 by Jefferis Peterson <jefferis@p...> on Wed, 25 Sep 2002 19:59:04 -0400
 
> From: "Nikolai Devereaux" <yomama@u...>
  
> At the simplest form, you'd typically say
> 
> SELECT ... WHERE your_col LIKE '%$keysearch%'
> 
> If you have several words to search for, you'll need a separate WHERE
> expression for each.
> 
> $patt = '/\s+/';
> $keywords = preg_split($patt, $keysearch);
> 
> $wheres = array();
> foreach($keywords AS $kw)
> {
>    $wheres[] = "col LIKE '%{$kw}%'";
> }
> 
> $where_clause = "\n  WHERE " . join("\n    OR", $wheres);
> $query        = "SELECT ... $where_clause";
> 
...
> "SELECT ...
> WHERE col LIKE '%Hello%'
>    OR col LIKE '%world%'
>    OR col LIKE '%how%'
>    OR col LIKE '%are%'
>    OR col LIKE '%you%'"
> 
> 

Ah, Nik, my problem is that the user may enter any number of words that
create combinations to look for like " yellow diamonds" or "yellow gold"
which if I used %yellow%" as one result, it would bring up both items when
the user only wants one...

Or they may be searching for multiple items like diamonds or sapphires and
gold... 



~~~~~~~~~~~~
Jefferis Peterson, Pres.
Web Design and Marketing
http://www.PetersonSales.net
Tel .  xxx-xxx-xxxx
ICQ 19112253

http://www.Slippery-Rock.com - 7,000 hits per year


  Return to Index