ok. im answering my own question here. answer is "this sucks". I wrote some
nasty ruff code just to get the job done. but it invoves alot of sql queries
and alot o recursion.
<?php
include_once('database_class.egn');
function search_string($field, $keyword, $join = 'OR')
{
if (!is_array($keyword))
$keyword = explode(' ', $keyword);
if (!is_array($field) OR !is_array($keyword))
return ;
foreach($field as $f_pos => $f_val)
{
unset($query);
foreach($keyword as $k_pos => $k_val)
if ($k_val)
$query[] = "$f_val LIKE '%$k_val%'";
$return[] = implode(" $join ", $query);
}
if (isset($return))
return 'WHERE ( ('. implode(') OR (', $return) .') )';
}
function relevent_query($search, $keyword, $first = 1 )
{
$keyword = explode(' ', $keyword);
$tmp_key = $keyword;
static $query;
if ( $first )
{
$tmp = implode(' ', $tmp_key);
$query[strlen($tmp)][$tmp] = search_string($search, $tmp, 'AND');
}
if ( count($tmp_key) <= 1 )
{
$tmp = array();
foreach( $query as $pos => $val )
foreach( $val as $q_pos => $q_val )
$tmp[] = $q_val;
return $tmp;
}
for( $count = 0; $count <= count($tmp_key); $count++ )
{
$tmp_key = $keyword;
unset($tmp_key[$count]);
$tmp = implode(' ', $tmp_key);
$query[strlen($tmp)][$tmp] = search_string($search, $tmp, 'AND');
relevent_query($search, implode(' ', $tmp_key), $first + 1);
}
$tmp = array();
foreach( $query as $pos => $val )
foreach( $val as $q_pos => $q_val )
$tmp[] = $q_val;
return $tmp;
}
$search[] = 'search_query';
$search[] = 'search_engine';
$keyword = 'ab cc ba';
foreach( relevent_query($search, $keyword) as $pos => $val )
foreach( $db->select_array('', 'search', $val) as $db_pos => $db_val )
$id[$db_val['search_id']] = $db_val['search_query'];
foreach( $db->select_array('', 'search', '') as $pos => $val )
{
$search_query[$val['search_id']] = $val['search_query'];
$search_engine[$val['search_id']] = $val['search_engine'];
}
foreach( $id as $pos => $val )
echo "{$search_engine[$pos]} : {$search_query[$pos]} <br>";
?>
that does these queries
WHERE ( (search_query LIKE '%ab%' AND search_query LIKE '%cc%' AND
search_query LIKE '%ba%') OR (search_engine LIKE '%ab%' AND search_engine
LIKE '%cc%' AND search_engine LIKE '%ba%') )
WHERE ( (search_query LIKE '%cc%' AND search_query LIKE '%ba%') OR
(search_engine LIKE '%cc%' AND search_engine LIKE '%ba%') )
WHERE ( (search_query LIKE '%ab%' AND search_query LIKE '%ba%') OR
(search_engine LIKE '%ab%' AND search_engine LIKE '%ba%') )
WHERE ( (search_query LIKE '%ab%' AND search_query LIKE '%cc%') OR
(search_engine LIKE '%ab%' AND search_engine LIKE '%cc%') )
WHERE ( (search_query LIKE '%ba%') OR (search_engine LIKE '%ba%') )
WHERE ( (search_query LIKE '%cc%') OR (search_engine LIKE '%cc%') )
WHERE ( (search_query LIKE '%ab%') OR (search_engine LIKE '%ab%') )
nasty eh? any better idea's. I hope so ..
--
Chris Lee
[EMAIL PROTECTED]
"Chris Lee" <[EMAIL PROTECTED]> wrote in message
[EMAIL PROTECTED]">news:[EMAIL PROTECTED]...
> if I give the user a search and he enters 'cat dog' I am going to want to
> find all the results that have the word 'cat' or 'dog' in them, but I want
> all the results that have 'cat' and 'dog' at the beginning of the results
> because these would be more relevent. now how I see it is this, it aint
> pretty.
>
> $result = select * from product where product_feature like '%cat%' and
> product_feature like '%dog%'
> foreach( $result as $pos => $val )
> $name[$val['product_id']] = $val['product_name']
> $result = select * from product where product_feature like '%cat%' or
> product_feature like '%dog%'
> foreach( $result as $pos => $val )
> $name[$val['product_id']] = $val['product_name']
> foreach( $name as $pos => $val )
> echo "$val <br>";
>
> this will put the 'AND' before the 'OR'. but its not nice, would be nice
if
> I could do this with one simple query. this is only with two keywords.
with
> three its gets exponentially nasty.
>
> 'cat dog mouse'
>
> $result = select * from product where product_feature like '%cat%' and
> product_feature like '%dog%' and product_feature like '%mouse%'
> foreach( $result as $pos => $val )
> $name[$val['product_id']] = $val['product_name']
> $result = select * from product where product_feature like '%cat%' and
> product_feature like '%dog%'
> foreach( $result as $pos => $val )
> $name[$val['product_id']] = $val['product_name']
> $result = select * from product where product_feature like '%cat%' and
> product_feature like '%mouse%'
> foreach( $result as $pos => $val )
> $name[$val['product_id']] = $val['product_name']
> $result = select * from product where product_feature like '%dog%' and
> product_feature like '%mouse%'
> foreach( $result as $pos => $val )
> $name[$val['product_id']] = $val['product_name']
> $result = select * from product where product_feature like '%cat%' or
> product_feature like '%dog%' or product_feature like '%mouse%'
> foreach( $result as $pos => $val )
> $name[$val['product_id']] = $val['product_name']
> foreach( $name as $pos => $val )
> echo "$val <br>";
>
> ouch, this is just nasty, not good. there must be a better way. accually
> writing code to take a dynamic number of keywords is guna be ugly.
>
> any SQL idea's ? yes I know this is a php forum, but its related.
>
> --
>
> Chris Lee
> [EMAIL PROTECTED]
>
>
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
