MySQL keyword frequency

[jaoudestudios]

Hi,

 

I hope someone can help.

 

I am doing a mysql search on 3 columns within a table, and I am ranking the returned result and ordering by the total relevance. However, I would like to add another count to the relevance if the keyword appears in the field more than once.

 

Below is a simplified version.

SELECT *, (

(CASE WHEN title LIKE '%the%' THEN 2 ELSE 0 END) +

(CASE WHEN content LIKE '%the%' THEN 1 ELSE 0 END)

) as relevance

FROM content WHERE (page LIKE '%the%' OR title LIKE '%the%' OR content LIKE '%the%')

ORDER BY relevance DESC

 

So if 'the' appears in the 'title' column and 'content' column the result will be 3. But if 'the' appears three times in the 'content' column it won't total 3 and then add it to the 2 from 'title' to equal 5. But I would like it to total 5.

 

Hope that makes sense.

Thanks,

Eddie

[jaoudestudios]

sorry meant to add... am using mysql 5

 

if you need any more info just let me know, thanks

[fenway]

LIKE is just returning true or false.

[jaoudestudios]

yep, is there another way so that it returns a value - of how many matches it finds in that row?

 

thanks

[fenway]

Not without a stored procedure.

[jaoudestudios]

How would it be done with a stored procedure?

 

Is that really hard? if so can you give a simplified example?

 

thanks

[fenway]

Not hard per se, you simply need to move through the field, and increment a counter every time you see an instance of your keyword.

[jaoudestudios]

I have created a simple stored procedure and I can get it to work on the command line, but I can not get it to work through php 5.

 

I have read many examples on how to call the stored procedure through php, but how do I set it? on the command line I use 'SOURCE procedure_file.sql' and it is ready to be called.

 

Please can you show me how to call the source file in php?

[fenway]

I'm not sure what you mean... just create it from phpmyadmin.

[jaoudestudios]

how would I do that? through the sql textarea?

 

I would need to load the external script (procedure) and then call it.

 

Can I send the script in a query instead of loading it from a *.sql file.

[fenway]

how would I do that? through the sql textarea?

 

I would need to load the external script (procedure) and then call it.

 

Can I send the script in a query instead of loading it from a *.sql file.

Well, you only need to "load" it once... and since the stuff in the .sql file is SQL statements, yes.

[jaoudestudios]

what about if the mysql has been restarted, will it keep the stored procedure? if not is there a way for the stored procedure to always auto load - maybe through the php?

 

 

[fenway]

what about if the mysql has been restarted, will it keep the stored procedure? if not is there a way for the stored procedure to always auto load - maybe through the php?

It will not "go away" -- it is stored in the proc table of the mysql database.