[RESOLVED] Keyword Search

[rsmith]

Hey all,
I'm working on a keyword search and I have it working very well if the user only enters one keyword. However, if they enter multiple words (separated by a comma), I'm running into problems.

 I put the keywords into an array and the foreach() through the array to the MySQL query on each keyword then display the results. 

 My problems are that sometimes it will pull the results and sometimes it won't. If the first word is found, it will always find a record but it may or may not find the 2nd word, even when I know for a fact the word is in one of the columns searched. The other problem I'm having is that it if both keywords are in the same record, it will display the record twice.

 My question is, what is the best way to go about creating a reliable keyword search that will display a record only once, even if it is found to have more than one keyword that was searched? I also need it to be able to tell me how many results (total) were found. My script right now is something like this:

     $keywords = mysql_real_escape_string($_GET['keywords']);
     $keywordArray = explode(",", $keywords);

 foreach($keywordArray as $keyword){
      $query = "SELECT id, title FROM `table` WHERE (`column1` LIKE '%$keyword%' OR `column2` LIKE '%$keyword%') ORDER BY id";
      $result = mysql_query($query);
      $info = mysql_fetch_assoc($result);

      echo "<tr><td>".$info['id']."</td><td>".$info['title']."</td></tr>";
 }

Any and all help is greatly appreciated.

[rsmith]

Okay, I have resolved the search and the totalling of results. I still have an issue with it echoing a duplicate record. Is there a way to only print the unique records to the screen?

[mpb001]

There are 2 DataBase ways, the pure pHP way will end up fugly using ids for array indexes etc...

1. Create a loop for the like clause so it ends up
   (column1 LIKE '%$keyword%' OR column2 LIKE '%$keyword%')
   OR (column1 LIKE '%$keyword%' OR column2 LIKE '%$keyword%')
   OR (column1 LIKE '%$keyword%' OR column2 LIKE '%$keyword%')

   or

   WHERE column1 IN ( '%$keyword%','%$keyword%','%$keyword%') OR column2 IN ( '%$keyword%','%$keyword%','%$keyword%')
   //Could create a wrapper for implode that handles the escaping/quoting/% things. Quite nice to have lying around at a later point.

2. Loop througn and create a union, this is the easiest

   (
   SELECT id, title , 1 AS key_word_index FROM table WHERE (column1 LIKE '%$keyword%' OR column2 LIKE '%$keyword%')
   )
   UNION
   (
   SELECT id, title , 2 AS key_word_index FROM table WHERE (column1 LIKE '%$keyword%' OR column2 LIKE '%$keyword%')
   )
   UNION
   (
   SELECT id, title , 3 AS key_word_index FROM table WHERE (column1 LIKE '%$keyword%' OR column2 LIKE '%$keyword%')
   ) ORDER BY key_word_index,id

The key_word_index bit just shows a way of forcing results to be in the order of selects. Probably not needed but somtimes it is a prerequisite.

[rsmith]

Thanks, a question on the UNION method. I see you have 3 queries through 2 unions. Does this mean that the user can only have 3 keywords input before I run into the same problem again?

[mpb001]

Have as many UNIONS as you like, the amount of UNION statements should be the array length-1.

By default they return distinct but if the key_word_index bit I said before is used then you will be back to sqaure one( always forget that ). You will have to follow the example here
http://dev.mysql.com/doc/refman/5.0/en/union.html

SELECT song_id FROM
(SELECT song_id FROM play_immediate
ORDER BY play_id DESC) AS t1

UNION

SELECT song_id FROM
(SELECT song_id FROM play_later
ORDER BY play_id) AS t2

To get them to come out in the order the keywords were entered if it is important.

eg

(
SELECT id, title FROM table WHERE (column1 LIKE '%$keyword%' OR column2 LIKE '%$keyword%')
)
UNION
(
SELECT id, title FROM table WHERE (column1 LIKE '%$keyword%' OR column2 LIKE '%$keyword%')
)
UNION
(
SELECT id, title FROM table WHERE (column1 LIKE '%$keyword%' OR column2 LIKE '%$keyword%')
) ORDER BY id

will return distinct but does no care about the order of the keywords were entered. Ie if I type monkey as my first keyword is it important that the monkey results are first in the list?

UNIONS get a bit tricky and are hard to do without the database beyond the top example due to having to work around not being able to use the GROUP BY clause before the last ORDER BY. They always catch me out for bigger things but come in very useful, but often what I mentally assume to work needs to be played with.

What it sounds like what you are doing is an older version of a tag cloud engine as a tag is pretty much the new fangled name of keyword. Could use a like clause on the tag list. Though tag clouds can get more head banging as well 🙂

[rsmith]

The only thing that determines relevancy is that I want the results ordered by rating where all 5-star are first, 4-start 2nd etc. etc. Is there a way to set the number of unions in the script by finding how many items are in the array and subtracting 1 so that it dynamically builds unions as necessary?

Thanks for all the help so far. If necessary I can post my actual snippet of code so you can see what my production script looks like.

[rsmith]

Got it all worked out. Thanks!