I don't even know WHERE to being on this. Way over my head.

basically a google like Keyword search for a memo field in the database.

I have a text type field for users to copy and paste resumes and notes into. They wish to have a search ability to look through for all the records that contain words in their search. Very much like a google search but just for this text field.

    You marked this thread resolved... did you find an answer to your question?

      Yes I did. it was much simpler than i had thought as MySQL has a built in tool to already do this!

      here is the code in case someone is looking for it in the future

      $searchstring= $_GET['searchstring'];
$query = "SELECT * FROM job_tracking.board INNER JOIN job_tracking.users ON board.user = users.USER_ID WHERE MATCH (subject, body, emp_ref, user, NAME) AGAINST ('$searchstring' IN BOOLEAN MODE)";
$row = mysql_query($query, $connection);

      This does a search for whatever string is entered into the $searchstring variable. I get this from a form. It checks for the individual keywords in the search string against the fields subject, body, emp_ref, user, NAME and rreturns the records with the them. I chose to use the BOOLEAN mode as this will also do some more advanced resume searching and i want the users to allow for more complex searching.

      I Then added a function to my header to highlight the keywords in the output text.
      $body is the text to be searched and outputed and $keys are the search words taken from the $searchstring variable used in the SQL Statement.

      First it checks for anything in the $keys field, just in case you're loading the particular field without search criteria (whcih mine does on occasion) and then searches the text and replaces it with a new formatted output. It then passes the formatted output back to my main script.

      function highlight($body, $keys)
{
$search_results = $body;
if($keys)
{
$words= str_replace(" ","|",trim($keys)); 
$text = eregi_replace($words,"<font color='red'><strong>\\0</font></strong>",$search_results); 
return $text;
}
else
{
return $body;
}
}

      When all is said and done, I call upon it to print very simply

      print(highlight($text, $searchstring));

      and the output has the text i want highlighted.

        Thanks for the followup - just wanted to make sure you didn't hit the button by mistake! :p

          perhaps one further question though i can't work out.

          With the bolean search, I want it to automatically assume a wildcard after every word.

          For example if i type in "quit" i want it to find anything with "QUIT" in it. so "Quits" or "quiter". Currently this way it's exact word for word and the users i have generally aren't smart enough to remember the * after each word

            Write a Reply...