finding keywords or phrases in one field

[codenature]

I am trying to make a form search through user input, and link it up with existing data  in the database.

 

so if in the text field, the person puts " flowers,  red roses, white roses, daisies, sunflowers, cars"

 

then they press "submit"

 

the code then runs sql to search the database with matching terms. So that if the terms in the database field row are

 

things->DBentry1->flowers, trucks, cars, red roses,

things->DBentry2->dogs, cats, flowers

 

it will echo out

 

" 2 entries have matches

 

"entry 1 has flowers, cars and red roses"

"entry 2 has flowers"

 

I want the results to be in order of number of matches returned for that database entry.

it will skip "trucks because trucks was not entered and so did not match.,

 

Basically I want the search form to let the user put the terms in separated by a comma and a space or without a space, and then php and mysql find the matching terms, then echo out the results in matching order from most to least.

 

I know that is a lot to ask of you all, but I would appreciate any help. Thanks in advance.

[Jessica]

You'll need to take that list and put it in an array using explode. Then use the array to build a WHERE clause using field LIKE ('%term%') OR field LIKE('%other term'%) etc.

[codenature]

Thank you. Also, I found this code on the net. Will this do it?

 

 

 

 

<?php
// checks if a search has been submitted
if(!empty($_REQUEST['search']))
{
  // the table to search
  $table = "yourTable";
  // explode search words into an array
  $arraySearch = explode(" ", $search);
  // table fields to search
  $arrayFields = array(0 => "title", 1 => "content");
  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $query = "SELECT * FROM ".$table." WHERE (";
  $countFields = count($arrayFields);
  while ($a < $countFields)
  {
    while ($b < $countSearch)
    {
      $query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $query = $query." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $query = $query.") OR (";
    }
  }
  $query = $query.")";
  $query_result = mysql_query($query);
  // print title
  echo '<h1>Your Search Results</h1>'."\n\n";
  if(mysql_num_rows($query_result) < 1)
  {
    echo '<p>No matches found for "'.$search.'"</p>';
  }
  else
  {
    echo '<p>Search Results for "'.$search.'":</p>'."\n\n";
    // output list of articles
    while($row = mysql_fetch_assoc($query_result))
    {
      // output whatever you want here for each search result
      echo '<a href="index.php?id='.$row['id'].'">'.$row['title'].'</a><br />';
    }
  }
}
else
{
  // display a welcome page
}
?>

 

 

[Jessica]

That does look like exactly what I described. Try it and see how it works for you.

 

Although that is searching more than one field and using AND.

 

If you want to match ALL words use AND. If you want to match any word, use OR.

[codenature]

Ok, thanks. I will try it with OR also. I may have a problem with sorting the results based on how many matches there are though.

[codenature]

I have completed adjusting the code, and creating the database fields. "color" and "shape". the page reloads upon the "search " button submit, and echoes out nothing, and does nothing. Please help.

 

here is the php to process the query of the database.

 

<?php	
// checks if a search has been submitted
if(!empty($_POST['search']))
{

// Connects to your Database
				mysql_connect("xxxxxxxxxxxx", "xxxxxxxxxxxxx", "xxxxxxxxx") or die(mysql_error()); 
				mysql_select_db("ealiketwo") or die(mysql_error()) ;
  // the table to search
  $table = "users";
  // explode search words into an array
  $arraySearch = explode(" ", $search);
  // table fields to search
  $arrayFields = array(0 => "colors", 1 => "shapes");
  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $query = "SELECT * FROM ".$table." WHERE (";
  $countFields = count($arrayFields);
  while ($a < $countFields)
  {
    while ($b < $countSearch)
    {
      $query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $query = $query." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $query = $query.") OR (";
    }
  }
  $query = $query.")";
  $query_result = mysql_query($query);
  // print title
  echo '<h1>Your Search Results</h1>'."\n\n";
  if(mysql_num_rows($query_result) < 1)
  {
    echo '<p>No matches found for "'.$search.'"</p>';
  }
  else
  {
    echo '<p>Search Results for "'.$search.'":</p>'."\n\n";
    // output list of articles
    while($row = mysql_fetch_assoc($query_result))
    {
      // output whatever you want here for each search result
      echo '<a href="basicSearch.php?id='.$row['id'].'">'.$row['title'].'</a><br />';
    }
  }
}
else
{
  echo "oops";// display a welcome page
}




}
?>		

 

and here is the form for the search.

 

 <form id="search" name="search" method="post" action="">
             <input name="search" type="text" value="search" size="12" maxlength="200" />
    <input type="submit" name="search" id="search" value="search" />
  </form>

 

[codenature]

I got it to work by removing the last "}", but it says upon the page reload after submit

 

Your Search Results

 

Search Results for "":

 

when I enter in a color like red.

 

I echoed out the array, it says this:

 

 

SELECT * FROM users WHERE (colors LIKE '%%') OR (shapes LIKE '%%')

 

looks wrong to me

[Jessica]

Rather than this business:

  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $countFields = count($arrayFields);
  while ($a < $countFields)
  {
    while ($b < $countSearch)

 

 

...

 

Use foreach();

 

foreach($arraySearch AS $searchKey=>$searchValue){
echo $searchValue;
//your code here to put in LIKE().
}

 

 

As for the problem,

$arraySearch = explode(" ", $search);

 

$search is never defined.