Multiple term search engine query

Question

I have this code which converts hopefully my words that are entered into an array and then is supposed to search the array in a database. The database has rows for words which are scanned in a website by a crawler.

The array code does not seem to be working :( My query is below:

        if( isset($_POST['keyword']) ){
        $k = $_POST['keyword'];
        }else{
        $k = '';
        }
        $k = ( isset($_POST['keyword']) )? trim($_POST['keyword']) : '';
        $terms = (strlen($k) > 0)? explode(' ', $k) : Array();

          $keyword = addslashes( $k);
          $results = addslashes( $_POST['results'] );

          /* Execute the query that performs the actual search in the DB: */
             $result = mysql_query(" SELECT p.page_url AS url,
                       COUNT(*) AS occurrences 
                       FROM page p, word w, occurrence o
                       WHERE p.page_id = o.page_id AND
                       w.word_id = o.word_id AND
                       w.word_word = \"$keyword\"
                       GROUP BY p.page_id
                       ORDER BY occurrences DESC
                       LIMIT $results" ); 

Answer 1

Your question is titled "Multiple term search engine" but this code (the SQL part implies a rather well thought out indexing) cannot accomodate multiple terms. In fact the PHP code is gibberish. It starts by setting the value of $k using an if expression. It then sets it to the same value using a ternary operator.

You have not provided an example of the search term you are using, nor the query generated from it. Nor do you provide any explanation of "does not seem to be working".

I suggest you go speak to the person who wrote the crawler and search database - and they'll explain that if you have multiple terms then you either need to splice the literal values into the query in a very different way or load them (temporarily) into a table and join that with the search query. They should also slap you with a big fish and tell you to go learn about SQL injection.

e.g.

...
$qry=searchQuery($_POST['keywords'], $dbh);
if ($qry) {
    $limit = (integer) $_POST['results'] ? (integer) $_POST['results'] : 20;
    $qry.=" LIMIT $limit";
    result=mysql_query($qry, $dbh);
} else {
    ...
    exit;
}
...

function searchQuery($keywords $dbh)
{
   if (''==$keywords) return false;
   $keywords=str_replace('  ', ' ', $keywords); // remove consecutive spaces
   $keywords=str_replace('  ', ' ', $keywords); // cos you might have an odd number of consecutive spaces
   $parts=explode(' ', $keywords);
   foreach ($parts as $x=>$word) {
       $parts[$x]="'" . mysql_real_escape_string($word, $dbh) . "'";
   }
   $keywords=implode(',', $parts);
   return " SELECT p.page_url AS url,
                   COUNT(*) AS occurrences 
                   FROM page p, word w, occurrence o
                   WHERE p.page_id = o.page_id AND
                   w.word_id = o.word_id AND
                   w.word_word in ($keywords)
                   GROUP BY p.page_id
                   ORDER BY COUNT(DISTINCT w.word_id)*COUNT(*) DESC";

}