PHP SQL Search question (order by relevance)

imc

Ok... I'm searching a table via the following:

$dispset="SHOW";

$sql2=mysql_query("SELECT * FROM plants WHERE (lname LIKE '%$hgsq1%' OR cname LIKE '%$hgsq1%' OR pdesc LIKE '%$hgsq1%') AND display = '$dispset' ORDER by lname,cname ASC LIMIT $limit,$rpp ");

I would like to know if there is a way to sort the results by their relevance to the search query. I have tried a couple other code snippets I found which involved "MATCH" and "AGAINST" statements, but I keep getting invalid query errors. I am using a simple text input from a form field to get the $hgsq1 variable.

Thanks for any help you're able to provide! 🙂

imc

bogu

Here you can find all the documentations+examples: Full-Text Search Functions

imc

Ok, so here's what I came up with... It gives me an error though; see below...

$sql2=mysql_query("SELECT * (MATCH (lname,cname,pdesc) AGAINST ('$hgsq1') AS score FROM plants WHERE MATCH (lname,cname,pdesc) AGAINST ('$hgsq1') ) AND display = '$dispset' ORDER BY score DESC LIMIT $limit,$rpp ");

The error I get is:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/hilltop/public_html/search.php on line 154

I probably have everything in the wrong order! lol... Please help!!

Thanks!

imc

cbcjobsDOTcom

here's what you wanna do:

take the search string and work it so it has pluses instead of spaces and commas:

$search_modded=str_replace(",","",$_GET[search]);
$search_modded=str_replace(" ","+",$search_modded);
$search_modded="+".$search_modded;

then....

SELECT , ( (1.3 (MATCH(title) AGAINST
('$search_modded' IN BOOLEAN MODE))) +
(0.6 * (MATCH(description) AGAINST ('$search_modded' IN BOOLEAN MODE)))
) AS relevance FROM jobs
WHERE ( MATCH(title,description) AGAINST
('$search_modded' IN BOOLEAN MODE) ) ORDER BY relevance DESC

in this example you can actually adjust the weight of the 2 columns that are searched...(title and description)...and it sorts it by relevance pretty good...

i got this off of http://dev.mysql.com/doc ... after searching for hours and hours of course...

if you want to see a working example go to

http://www.cbcjobs.com

florianFwd

First I want to thank you all for this code which help me a lot to do my search engine stuff.

Secondly I want to apologise to dig out this topic but I'm quite sure it could be relevant for someone else.. one day or another maybe 😛

So here we are, could you help me to find out how to display the result that contain only the searched words...

For example if I search with the technique above for 2 keywords, the top result will be the title that contain both of the keyword and then one of the keywords. What I want is to display only the result with the 2 keywords.

Do you think that will be possible to achieve with a fulltext research?

Thanks ahead of time, Flo