combining mulitple quereies

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

combining mulitple quereies

Post 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
big_c147
Forum Newbie
Posts: 15
Joined: Wed Jun 22, 2005 9:32 am

Post 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?
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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!
hame22
Forum Contributor
Posts: 214
Joined: Wed May 11, 2005 5:50 am

Post 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!??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

that's what my code should do.....
Post Reply