Shopies
asked on
related topics/articles...etc
Hi experts,
When viewing an article in most of the websites, we can see a list of "related" topics to the one we read. My question is: how can I make an effective "real" related topics list as they do? What do I need? How do I search the DB?
For you to know, I have made a field in the articles table called "key_words". The admin should add key words with / between each one. Later, I explode that field and do a seach using LIKE method. But the result was horrible.
Best Regards,
When viewing an article in most of the websites, we can see a list of "related" topics to the one we read. My question is: how can I make an effective "real" related topics list as they do? What do I need? How do I search the DB?
For you to know, I have made a field in the articles table called "key_words". The admin should add key words with / between each one. Later, I explode that field and do a seach using LIKE method. But the result was horrible.
Best Regards,
ASKER
Assuming that two words occured in two different articles, Both articles will appear twice as they match the same words.!! Unless you add one word for each article and this is not practical. And example to my point would be adding two articles, say art1 and art2 and both articles have the key words "plane" and "moon" then those articles will occure twice since they have both key words in the key_words table.
Is it possible to bring an article just once even if there were multi matches?
Is it possible to bring an article just once even if there were multi matches?
"Assuming that two words occured in two different articles, Both articles will appear twice as they match the same words.!!"
No, we would not suggest doing that.
"Is it possible to bring an article just once even if there were multi matches?"
Yes, of course. You can use MySQL to do a lot of things and in this case it might be a good idea to GROUP the results or to SELECT DISTINCT. Probably the GROUP would offer better performance, but with a reasonable LIMIT you might not see much difference. It is a good idea to EXPLAIN every query if performance is an issue.
The logic might be something like this... Select all articles that have both "plane" and "moon" and are very recent - assign these articles first priority. Then select all articles that have both "plane" and "moon" but are older - assign these articles second priority. Then select all articles that have "plane" or "moon" and assign these articles third priority. Then select DISTINCT ordered by priority. You'll probably get a fairly good results set.
Best, ~Ray
No, we would not suggest doing that.
"Is it possible to bring an article just once even if there were multi matches?"
Yes, of course. You can use MySQL to do a lot of things and in this case it might be a good idea to GROUP the results or to SELECT DISTINCT. Probably the GROUP would offer better performance, but with a reasonable LIMIT you might not see much difference. It is a good idea to EXPLAIN every query if performance is an issue.
The logic might be something like this... Select all articles that have both "plane" and "moon" and are very recent - assign these articles first priority. Then select all articles that have both "plane" and "moon" but are older - assign these articles second priority. Then select all articles that have "plane" or "moon" and assign these articles third priority. Then select DISTINCT ordered by priority. You'll probably get a fairly good results set.
Best, ~Ray
ASKER
Great explaination now it's more clear to me. Am I asking too much to request a MySQL example for that to learn from it? I just want to see how to looks then I'll fet it to my needs..
Regards,
Regards,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Here is what I would consider. Instead of a field called key_words, create a table called keywords. Each row in the table has ONE keyword and an article number with its publication date. To find the related articles, you would select from the keywords table, choosing the articles that appeared the most frequently (or that had the most recent publication date).
HTH, ~Ray