This content has been marked as final. Show 2 replies
>In this example search query, how would I order the submissions by the number
I don't think this can be done with pure SQL. The usual way is to use a
search index, which contains all found terms, the documents they appear
in and some statistical values. This then allows to rank the terms and
documents by frequency and relevance (for example a term found in every
document would have no relevance at all).
You could also have a look at MySQL's built-in fulltext search
capabilities. I've never used it, but it might be worth a look.
This website explains an interesting way of finding how many times a keyword occurs using just MySQL:
This site explains how to get a relevence factor in several different ways, but the only one I am interested in right now is the one labeled "occ":
- length(replace(Description,'games','')) )
/ length('games') as occ
The problem is that the site only explains how to find the occurennces of a single keyword, rather than multiple keywords.
I am not sure if an array in the replace function would be possible, but otherwise I was thinking I could just use PHP to repeat that code in the SELECT clause for each keyword, similar to how the keywords are repeated in the WHERE clause.
Let me know if you have a more efficient idea.