[php/mysql]search queries and handling

[Outpour]

I'm having some problems implementing my concept of searching on my blog [www.binaryelysium.com].

This is the way I am attempting to run a query. Searcher submits terms ie: "large red clowns."

What I want to do is give results in the following order (top with highest priority):

1. results that include all terms
2. results that include all terms minus one
3. results that include all terms minus two
4. and so on until one term remains this one is displayed at the very end

So... for the above example the user would get:

• posts with the the striing "large red clowns"
• posts with the string "large red"
• posts with the string "large clowns"
• posts with the string "red clowns"
• posts with the string "large"
• posts with the string "red"
• posts with the string "clowns"

But the last three ordered in such a manner of being most recent.
Does anyone have any idea how I could go about doing it?

[bacon]

You could seperate the search string into words, and then generate a really bigass where clause with ANDs and ORs....

Or, seperate the searches into multiple queries and use mysql's UNION operation to concat the results together.

[rukkyg]

When I was writing my first search engine, I thought of doing this. Then I realized that Google only includes results that have all terms. The difference is that they show results that are pages that link to pages that have your search terms. Basically, you don't need to do what you're saying, because you can just construct people to search more generally (less terms).

[ratbastid]

There are really two issues here. Querying matches from the database is one. The other is ranking the responses by (let's call it) keyword density.

The first is easy--just split your query terms on spaces and do an OR query for each of them.

Then run a post-query ranking process where you test each record returned from your query for the presence of each of your search terms, and scores them. Then sort your responses by score, and bob's yer uncle.

Like this:

//Let's say that array $responses has strings of response values.

foreach ($responses as $record) {
   $result[$i++][record] = $record;
   foreach ($keyword as $word) {
       if (ereg($word, $record)) {
           ++$result[$i][score];
       }
    }
}

function user_sort ($a, $b) {
     if ($a[score] > $b[score]) {
         return 1;
      } elseif ($b[score] > $a[score]) {
         return -1;
      } else { return 0; }
}

sort($result, 'user_sort');

Note that I didn't actually run this code--this is strictly off-the-top-of-my-head. YMMV greatly.

[Outpour]

So to do the actual query would you do seperate queries for each term?

PHP Code:

//Let $_GET['q'] be the search terms
$search = explode(" ",$_GET["q"]);
foreach ($search as $term) {
   $query = "SELECT DISTINCT * FROM table WHERE field LIKE '%$term%' OR field2 LIKE '$term%' OR field3 LIKE '%$term%'";
} 


Obviously that itself won't work.. $query would be over written each time the foreach loops. How would you do a query on all the terms and combine them into one large array?

[Outpour]

egh ugly highlight colors.

Anyways i figured out how to combine all the queries:

$search = explode(" ",$_GET["q"]);
$query = '';
foreach ($search as $term) {
	$query .= "SELECT DISTINCT * FROM table WHERE field LIKE '%$term%' OR field2 LIKE '$term%' OR field3 LIKE '%$term%' UNION ";
} 
$query = substr($query, 0, -6)
$searchquery = mysql_query($query,$db);

[Outpour]

Originally Posted by ratbastid

--snip--
Then run a post-query ranking process where you test each record returned from your query for the presence of each of your search terms, and scores them. Then sort your responses by score, and bob's yer uncle.

Like this:

--snip--

Note that I didn't actually run this code--this is strictly off-the-top-of-my-head. YMMV greatly.

I am not sure exactly how to implement your example.. what is the purpose of returning 1, 0 or -1 on the sorting function.

Here is your code with some modifications for my situation:

$search = explode(" ",$_GET["q"]);
$query = 'SELECT * FROM posts WHERE 0=0 OR ';
foreach ($search as $term) {
	$query .= "'Body' LIKE '%$term%' OR 'intro' LIKE '%$term%' OR ";
} 
$query = substr($query, 0, -3);
$searchquery = mysql_query($query,$db);
while ($results = mysql_fetch_array($searchquery)) {
	foreach ($results as $i => $record) {
			 $result[$i++][record] = $record;
			   foreach ($search as $word) {
				  if (ereg($word, $record)) {
					  ++$result[$i][score];
				  }
			}
		}		
}

//The user_sort function here

$sorted = usort($result, 'user_sort');
print_r($sorted);

How do I go about outputting results in a sorted manner?