PHP/MySQL Searching Database, with Keyword Scores

[ryan_b83]

Hello I am trying to do a simple database search. I want to search 2 feilds. Title, and Description, and sort them based on relevence. So for example:

Search: gold ring

So in priority search the database for "gold ring"
Then search the database for "gold" && not "gold ring" (so i dont get duplicates)
Then search the database for "ring" && not "gold ring" (so i dont get duplicates again)

finally get the results as one mysql result array. Is there any way to do this as a single mysql statment? Could you do this with a temporary table?

Thanks!
Ryan

[supermanjnk]

You could always explode the the search term, then build the query from the results using something like

SELECT DISTINCT * FROM table WHERE field like '%term1%' OR field like '%term2%'

[ryan_b83]

Thanks, I figured it out to use the temporary table method.

Yes you are correct however I dont think that you would get the values in proper priority that way... for example: gold rings

I would want to show all instances of "gold rings" before any instances of just "gold" or "rings" because "gold rings" is a better match...

I'm pretty sure thats correct.

Thanks,
Ryan

[ryan_b83]

Actually, this way is still not even that good... Is there a way to pass in a string for mysql to search for, and display the results with the most relevent matches first?

[justageek]

You may want to just create a full text index. That will allow you to do what you want.

JAG

[superpower]

This may not be exactly what you are looking for but may help give you an idea of the type of query...I wrote this a while ago for MySQL 3.23:

$query = "SELECT MATCH(KWTitle) AGAINST('" . $keywords_commas . "') AS relevance_title,MATCH(KWDef) AGAINST('" . $keywords_commas . "') AS relevance_Def,KWTitle,KWDef,KWNumber,KWDate FROM db_mydb WHERE (MATCH(KWTitle) AGAINST ('" . $keywords_commas . "'))+(MATCH(KWDef) AGAINST('" . $keywords_commas . "')) ORDER BY relevance_title DESC,relevance_Def DESC LIMIT 0,100;";

I'd look at the MySQL manual for MATCH and relevance.

MySQL 4.0 has some new features that allows better searching, ranking so it is better if you use that.

[superpower]

Oh yeah, as justageek mentions, you need a full text index, at least for that type of query I listed.

[superpower]

The MATCH() function performs a natural language search for a string against a text collection. A collection is a set of one or more columns included in a FULLTEXT index. The search string is given as the argument to AGAINST(). For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

[dev.mysql.com...]

[ryan_b83]

thats EXACTLY what i was looking for!

Thank a lot! I will do some reading.

[ryan_b83]

Umm... i dont know why but i keep getting this error for this query.

create FULLTEXT INDEX name_index ON product (name,8);

Error Code : 1064
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 '8)' at line 2

Any ideas? Thanks

[coopster]

Yeah, just what it said, you have a syntax error ;)
[dev.mysql.com...]
Can you see what it is from this page? If not, let us know and we'll point it out more specifically. I really want you to discover it though so have another look.