PHP DB - Re: [PHP-DB] RE: How do I find products when a user types freeform strings like 'Sony

Author

Re: [PHP-DB] RE: How do I find products when a user types freeform strings like 'Sony

bedul

2007-05-11, 7:57 am

i'm currious about how goggle logic to find product..
that's something to thinking of
----- Original Message -----
From: "Daevid Vincent" <daevid@daevid.com>
To: <php-db@lists.php.net>
Sent: Friday, May 11, 2007 11:50 AM
Subject: [PHP-DB] RE: How do I find products when a user types freeform
strings like 'Sony 20" TV' or '20" Sony TV'? [SOLVED]

>
> I'll attach a .php file, but this list server may strip it off, so I'll
also paste it below, sorry for any formatting issues in
> advance...
>
> <?php
> if ($_POST['keywords'])
> {
> $_POST['keywords'] = stripslashes($_POST['keywords']);
> $words = preg_split("/\s+/",$_POST['keywords'], -1, PREG_SPLIT_NO_EMPTY);
> }
>
> $sql = 'SELECT products.* FROM product_table WHERE 1 ';
> $sql .= keyword_filter($words, array('products.model%', 'products.upc',
'%products.name%', 'companies.name%', '%categories.name%'),
> true);
> $sth = SQL_QUERY($sql);
>
> /**
> * Builds the WHERE portion of a SQL statement using the keywords in
various columns with wildcard support.
> *
> * @return string SQL statement fragment
> * @param mixed $words either a string of words space deliminated or an
array of words
> * @param array $columns an array of table.column names to search the
$words in. Use % as a wildcard for example pass in
> 'username%' or '%username%'.
> * @param boolean $and (true) whether the words have to be ANDed or ORed
together.
> * @author Daevid Vincent [daevid@symcell.com]
> * @since 1.0
> * @version 1.4
> * @date 05/10/07
> * @todo This should handle +, - and "" just like google or yahoo or other
search engines do.
> */
> function keyword_filter($words, $columns, $and = true)
> {
> // this maybe useful
> //
http://wiki.ittoolbox.com/index.php...SQL_sele

ct_statement
> // http://www.ibiblio.org/adriane/queries/
> //
http://www.zend.com/zend/tut/tutori...torial-ferrara1
&kind=t&id=8238&open=1&anc=0&view=1
>
> //
http://evolt.org/article/ Boolean_F... /> MySQL/18/15
665/index.html
> // http://www.databasejournal.com/feat...cle.php/3512461
>
> // this would be great, but the dumb-asses don't work with InnoDB tables.
GRRR!
> // http://dev.mysql.com/doc/refman/5.0...xt-boolean.html
> //$sql .= " AND MATCH (".implode(',',$columns).") AGAINST ('".implode('
',$words)."' IN BOOLEAN MODE)";
>
> if (!is_array($columns) or !$words) return;
>
> if (is_string($words))
> $words = preg_split("/\s+/",$words, -1, PREG_SPLIT_NO_EMPTY);
>
> if(count($words) < 1) return '';
>
> if ($and) //AND the words together
> {
> $sql = " AND ";
> $sqlArray = array();
> foreach($words as $word)
> {
> $tmp = array();
> foreach($columns as $field)
> {
> $col = str_replace('%','',$field);
> //[dv] read the http://php.net/preg_replace carefully. You must use this
format,
> // because otherwise $words that are digits will cause undesired
results.
> $myword = preg_replace("/(%)?([\w\.]+)(%)?/", "\${1}".$word."\${3}",
$field );
> $tmp[] = $col." LIKE '".SQL_ESCAPE($myword)."'";
> }
> $sqlArray[] = " (".implode(" OR ",$tmp).") ";
> }
> $sql .= implode(" AND ", $sqlArray);
> }
> else //OR the words together
> {
> $sql = " AND ( ";
> $sqlArray = array();
> foreach($columns as $field)
> {
> $col = str_replace('%','',$field);
>
> $tmp = array();
> foreach($words as $word)
> {
> //[dv] read the http://php.net/preg_replace carefully. You must use this
format,
> // because otherwise $words that are digits will cause undesired
results.
> $myword = preg_replace("/(%)?([\w\.]+)(%)?/", "\${1}".$word."\${3}",
$field );
> $tmp[] = $col." LIKE '".SQL_ESCAPE($myword)."'";
> }
> $sqlArray[] = "(".implode(" OR ",$tmp).") ";
> }
> $sql .= implode(" OR ", $sqlArray);
> $sql .= ") ";
> }
>
> return $sql;
> }
> ?>
>
>

----------------------------------------------------------------------------
----

> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php

Sponsored Links