Page 1 of 1

combining mulitple quereies

Posted: Tue Aug 16, 2005 5:58 am
by hame22
Hi

I am making a custom search engine within my site, part of this feature is to search for records within my database which contain ALL words entered into the search box.

at the moment the keywords are split up and stuck into an array: $q_array

I then aim to do a for loop to go through each keyword entered, so at the mo I have something like ;

Code: Select all

for ($d=0; $d<$q_num; $d++)
{
 sql = "Select * from activities where title = $q_array[$d] or intro =$q_array[$d] or text=$q_array[$d]......etc"
}
but obviously as I am looking for activities that contain all keywords entered I need to have an AND statement (if there are 2 or more) there and replicate the above query for each keyword.

I'm not to sure how to do this and would appreciated if anyone could help

thanks in advance

Posted: Tue Aug 16, 2005 7:38 am
by big_c147
Hi

This splits all words seperated by a space, puts AND in between them, and then removes the extra AND at the end. Can't take credit for this is was posted to me a while back :)

Code: Select all

$wordArray = split(' ',$Key);
foreach($wordArray as $key => $value) 
{
$newSentence .= "\"%" . $value . "%\" AND Field_Name LIKE ";
} 
$newSentence = trim($newSentence, " AND Field_Name LIKE "); 

$wordArray = split(' ',$split);
foreach($wordArray as $key => $value) 
{
$crit .= "\"$value\" AND ";
} 
$crit = trim($crit, " AND ");
Then in you SQL do something like

Code: Select all

"$sql = SELECT * from tblTest WHERE NAME $crit"
Is this what you are after?

Posted: Tue Aug 16, 2005 8:55 am
by hame22
No as I want the query to check each field in a row for the keyword

for example if i have 2 keywords; word1 and word2

for a record to be returned both word1 and word2 MUST appear in atleast 1 field in the row.


So what i need is a query that does the following:

sql = "select a record where word1 appears in field1 OR appears in field2 OR appears in field3 AND Where Word2 appears in field1 OR appears in field2 OR appears in field3"

do you see what i mean

any ideas welcome

Posted: Tue Aug 16, 2005 9:12 am
by feyd
well.. there's the automated way:

Code: Select all

$tableName = 'someTable';
$schema = mysql_query("DESCRIBE `{$tableName}`") or die(mysql_error());
$fields = array();
while($field = mysql_fetch_assoc($schema))
{
  $fields[] = $field['Field'];
}

$search = array();
foreach($words as $word)
{
  $pos = count($search);
  $search[$pos] = array();
  $word = str_replace(array('%','_'),array('\\%','\\_'),mysql_real_escape_string($word));
  foreach($fields as $field)
  {
    $search[$pos][] = '`'.$field.'` LIKE \'%'.$word.'%\'';
  }
  $search[$pos] = implode(' OR ',$search[$pos]);
}
$search = '('.implode(') AND (',$search).')';

$sql = 'SELECT * FROM `'.$tableName.'` WHERE '.$search;
complete untested!

Posted: Tue Aug 16, 2005 10:02 am
by hame22
ok so if i was just to write out the sql as standard the query i would have would look like this:

Code: Select all

"Select * from activities where (title like '%word1%' OR intro like '%word1%' OR text like '%word1%') AND (title like '%word2%' OR intro like '%word2%' OR text like '%word2%') AND(title like '%word3%' OR intro like '%word3%' OR text like '%word3%')"
is that what ur thinking or am I completely way off the mark!??

Posted: Tue Aug 16, 2005 10:17 am
by feyd
that's what my code should do.....