CODEWITHSUNDEEP
phpdatabasetags
Robert
Robert

Reputation: 149

Finding Related Tags

I'm making a website with specific posts that have tags created by the user. My database looks like this:

Qid              Tag
3                Happy
3                Sad
3                Funny
6                Happy
6                Delicious
8                Angry

What I want to do is to find related tags.

My plan is to enter a tag as the key (say Happy for example) and then come out Sad, Funny, and Delicious.

But I only want a max of say 5 or 6.

In the real database, there are tons more of these entries, but i don't know how to choose between the top 5 or 6.

My questions are:

  1. How would i do the query properly to just come up with all related tags?
  2. How would i narrow my best search results to 5 or 6?
  3. Is there an even better way to do related tags that i haven't thought of (e.g., dictionary)?

BTW, it's in PHP so I can do post processing there, too.

Upvotes: 2

Views: 364

Answers (1)

Saladin Akara
Saladin Akara

Reputation: 2548

To give a general answer to your question...

  1. You need to find the QID of the tag, then select all tags with that QID - this depends, of course, on how you store post metadata in your database

  2. If by 'best search results' you mean most-used tags, then you need to add another column to the table - something like frequency. Then, have that increase by one each time the tag is used. But, to find the top five based on that assumption, you would do something like:

    SELECT * FROM tag_table
WHERE qid = 3            /* Where 3 is just an example                     */
ORDER BY frequency DESC  /* so the most used are at the top of the results */
LIMIT 5;                 /* select only the first five results             */

  3. I'm not sure what would be a better way, to be honest. Without any further information, it's hard to know.

Upvotes: 1

Related Questions