[SOLVED] Order by Relevance

[jxrd]

Hi all,

 

I was just wondering how to order by relevance in a mysql search. Much like this forum does on thread searches.

 

Atm I just have something like this:

SELECT * FROM `table` WHERE `$type` LIKE '%$search%' ORDER BY `ID` DESC
/*
$type being the field to search in
and $search being the search
*/

 

I have no idea where to start about ordering by relevancy though. I imagine it'd be something like ORDER BY IF(`$type` LIKE '$search', 1, 0), but it obviously is like $search otherwise it wuldn't be selected, which is where I'm stuck.

 

Any help would be awesome.

 

Thanks,

[kickstart]

Hi

 

You need to define relevance.

 

For example, you could be searching for "mysql update select", and might define the full phrase in that order as being most important, all three words in any order next, 2 out of 3 words next, etc.

 

Once you can decide that kind or importance then we can try and find a way to code it.

 

All the best

 

Keith

[jxrd]

Hmm, I guess the record with the most occurences of any word in the search would be a good place to start. So yeah, I guess either order by how many occurences there are, so for example:

 

The search "play computer":

 

"I like my computer; I play lots of computer." would come before

 

"I play my computer."

 

Thanks for the reply.

[jxrd]

Bump...

[Ken2k7]

SELECT *, COUNT(*) AS count FROM `table` WHERE `$type` LIKE '%$search%' GROUP BY `$type` ORDER BY count DESC

?

[kickstart]

Hi

 

Something like this would order by the importance.

 

SELECT *, 
(CASE WHEN type LIKE '%play computer%'  THEN 50 
WHEN type LIKE '%play%computer%'  THEN 40 
WHEN type LIKE '%play% AND type LIKE %computer%'  THEN 30 
WHEN type LIKE '%play%'  THEN 20 
WHEN type LIKE '%computer%'  
THEN 10 ELSE 0) Importance
FROM `table` 
WHERE `type` LIKE '%play%' 
OR `type` LIKE '%computer%' 
ORDER BY Importance DESC

 

However it will rapidly get messy with longer phrases.

 

All the best

 

Keith

[Ken2k7]

kickstart - how long does it take MySQL to process that?

[kickstart]

Hi

 

Very quickly against a single row .

 

More seriously it isn't that nice but shouldn't be too bad as it is only having to do the hideous case statement for the small subset of rows that are returned.

 

If the phrase was "a the is and not" and the search against a large forum where it would bring back most records then I wouldn't like the hazard a guess.

 

All the best

 

Keith

[jxrd]

Awesome, thanks for the reply.

 

I currently have this:

 

<?php
include 'config/config.php';
$mysql = new connection;
$mysql->connect();

function order($i)
{
	/*
	 *return an integer inversely proportional to the order
	 *so that with each search case, the relevance order will decrease...
	 *hope you're with me so far.
	 */
	return round(100 / ($i + 1));
}

//just a test. will be post data in reality.
$search = 'something';

//extract each word/phrase
$search_array = preg_split('/(\s|\b)/', $search);

/*
 *init the search case with some general ordering...
 *ordered by an exact match, a partial match, partial match with a wildcard, and then any word/phrase with a wildcard
 */
$search_case = array(
"WHEN `Subject`='$search' THEN ".order(0),
"WHEN `Subject` LIKE '$search' THEN ".order(1),
"WHEN `Subject` LIKE '%$search%' THEN ".order(2),
"WHEN `Subject` LIKE '%".preg_replace('/(\s|\b)/', '%', $search)."%' THEN ".order(3)
);

//then, add each word, with the word order defining it's importance to the order
foreach($search_array as $key => $value)
{
	$key = order(count($search_case) + 1);
	array_push($search_case, "WHEN `Subject` LIKE '%$value%' THEN $key");
}

//create the SQL to do the initial select, so that all results with any match are returned
$search_case_init = null;
foreach($search_array as $key => $value)
{
	$search_case_init .= "OR `Subject` LIKE '%$value%' ";
}

//...
$sql = $mysql->query("SELECT *, (CASE ".implode("\n", $search_case)." ELSE 0) r FROM `$tb_Forum` WHERE `Subject` LIKE '%$search%' $search_case_init ORDER BY r;") or $mysql->trigger_error();
while($fetch = $mysql->fetch_array($sql))
{
	echo $fetch['Subject'].'<br />';
}
?>

Which...should order by matching exactly, matching the exact phrase somewhere, matching the exact phrase but with a wildcard, and then any words seperated with a wildcard. In theory anyway

 

However, I'm getting a mysql error: Fatal error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'r FROM `Forum` WHERE `Subject` LIKE '%something%' OR `Subject` LIKE '%something%' at line 5 in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\config\config.php on line 125

 

This is the SQL generated:

SELECT *, (CASE WHEN `Subject`='something' THEN 100

WHEN `Subject` LIKE 'something' THEN 50

WHEN `Subject` LIKE '%something%' THEN 33

WHEN `Subject` LIKE '%something%' THEN 25

WHEN `Subject` LIKE '%something%' THEN 17 ELSE 0) r FROM `Forum` WHERE `Subject` LIKE '%something%' OR `Subject` LIKE '%something%'  ORDER BY r;

 

Any pointers would be great. This is a bit out of my MySQL league tbh...I'm better with PHP

[kickstart]

Hi

 

I missed an END out. There needs to be an END after the ELSE part of the CASE statement before closing the brackets.

 

SELECT *, (CASE WHEN `Subject`='something' THEN 100

WHEN `Subject` LIKE 'something' THEN 50

WHEN `Subject` LIKE '%something%' THEN 33

WHEN `Subject` LIKE '%something%' THEN 25

WHEN `Subject` LIKE '%something%' THEN 17 ELSE 0 END) r FROM `Forum` WHERE `Subject` LIKE '%something%' OR `Subject` LIKE '%something%'  ORDER BY r;

 

Sorry.

 

All the best

 

Keith

[jxrd]

Aha, yeah, that works pretty well.

 

Cool; thanks. I appreciate you dedicating your time to helping me out.

 

Cheers

 

End product, with a few bits tidied up that didn't work so well...

<?php
include 'config/config.php';
$mysql = new connection;
$mysql->connect();

function order($i)
{
	//generate order
	return 100 * $i;
}

//just a test. will be post data in reality.
$search = 'what taking';

//extract each word/phrase
$search_array = preg_split('/\s/', $search);

/*
 *init the search case with some general ordering...
 *ordered by an exact match, a partial match, partial match with a wildcard, and then any word/phrase with a wildcard
 */
$search_case = array(
"WHEN `Subject`='$search' THEN ".order(0),
"WHEN `Subject` LIKE '$search' THEN ".order(1),
"WHEN `Subject` LIKE '%$search%' THEN ".order(2),
"WHEN `Subject` LIKE '%".preg_replace('/(\s|\b)/', '%', $search)."%' THEN ".order(3)
);

//then, add each word, with the word order defining it's importance to the order
foreach($search_array as $key => $value)
{
	$key = order(count($search_case) + 1);
	array_push($search_case, "WHEN `Subject` LIKE '%$value%' THEN $key");
}

//create the SQL to do the initial select, so that all results with any match are returned
$search_case_init = null;
foreach($search_array as $key => $value)
{
	$search_case_init .= "OR `Subject` LIKE '%$value%' ";
}

//...
$sql = $mysql->query("SELECT *, (CASE ".implode("\n", $search_case)." ELSE 0 END) r FROM `$tb_Forum` WHERE (`Subject` LIKE '%$search%' $search_case_init) AND `Type`='thread' ORDER BY r;") or $mysql->trigger_error();
while($fetch = $mysql->fetch_array($sql))
{
	echo $fetch['Subject'].'<br />';
}
?>