Jump to content

Count how many times a tag appears


dachshund
Go to solution Solved by vinny42,

Recommended Posts

Three tag columns smells of a design error, there is usally a table with tags and a table linking the tags to the rest of the data, so you don't have to store each tag more than once and you are not limited to three tags.

 

But hwat do you want exactly, just a list of all the tags and how many times they occur? Or does the order of tag1, tag2 and tag3 matter for the count?

Link to comment
Share on other sites

As vinny42 stated, this is best handled by normalizing the data such that the tags are stored in a related table with a foreign key reference to the parent record. This makes do any type of operations on the Tag values much easier. But, it can be done with three tag columns in the current records. It's just that the query will be more complicated and it will not scale - if you ever added/removed tag fields from that table the query would have to be updated.

 

Something such as

 

SELECT *,
    ((tag1 = 'word') + (tag2 = 'word') + (tag3 = 'word')) as total
FROM table_name
ORDER BY total DESC

YOu don't say if the word will be an exact match or a partial match. If you need partial matches then change the tag1 = 'word' to tag1 LIKE '%word%'

Link to comment
Share on other sites

apologies, i don't think i explained myself too well to begin with.

 

for each article, 'tag1' can be defined as any word you wish, such as "amazon" or anything else. this is the same as tag2 and tag3, you can enter any tag you like. I realise this would normally be done with one comma delimited column, but it was set up like this a while ago and works fine for the time being as we generally don't need more than 3 tags per article.

 

basically what I want to do is find out what words have been used most as a tag. so the query would simply list popular tag words in order of how many times they have been used.

 

i would like the end echo to be something like the below

 

amazon - 26 times

antarctica - 22 times

london - 4 times

new york - 2 times

 

the problem is "amazon" could have been entered as tag1 on one article, and tag3 on another article, so I need to search and could all three columns as one.

 

hope that makes a little more sense and thanks for your help so far!

Link to comment
Share on other sites

 


I realise this would normally be done with one comma delimited column, 

 

It should *NEVER EVER* be done with a comma delimited string. :-)

 

If you just want to put all values from tag1, tag2 and tag3 into one big list and count them then that is exactly what you should do:

 

SELECT tag, COUNT(*) AS number_occurences

FROM

(

SELECT tag1 AS tag FROM table 

UNION ALL

SELECT tag2 FROM table

UNION ALL 

SELECT tag3 FROM table

) AS merged_tags

GROUP BY tag

ORDER BY COUNT(*) DESC;

Link to comment
Share on other sites

thanks.

 

i'm assuming this is the wrong way to echo out the results?

 

 

 
$sql = "SELECT tag, COUNT(*) AS number_occurences FROM (SELECT tag_one AS tag FROM content UNION ALL SELECT tag_two FROM content UNION ALL SELECT tag_three FROM content) AS merged_tags GROUP BY tag ORDER BY COUNT(*) DESC";
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result)){
 
}
 
Link to comment
Share on other sites

Hi Vinny,

 

Sorry if I was initially unclear about what I was trying to achieve, the rough code below is what I'm trying to make work

 

 

$sql = mysql_query("SELECT DISTINCT tag_one + tag_two + tag_three FROM table ORDER BY (number of occurrences) DESC");
while($rows=mysql_fetch_array($sql)){
 
echo $rows['tag_one + tag_two + tag_three];
 
}
Link to comment
Share on other sites

 

Hi Vinny,

 

Sorry if I was initially unclear about what I was trying to achieve, the rough code below is what I'm trying to make work

$sql = mysql_query("SELECT DISTINCT tag_one + tag_two + tag_three FROM table ORDER BY (number of occurrences) DESC");
while($rows=mysql_fetch_array($sql)){
 
echo $rows['tag_one + tag_two + tag_three];
 
}

 

EDIT: If this is your table data

 

id | tag1   | tag2   | tag3
-------------------------------
 1 | apple  | pear   | banana
 2 | pear   | orange | apple
 3 | orange | kiwi   | banana
 4 | kiwi   | apple  | orange
 5 | apple  | apple  | tomato
 6 | apple  | apple  | apple
 5 | tomato | apple  | apple

 

Then you can get the sum of how many times a tag is used in each record using the query I provided previously

 

SELECT *,
    ((tag1 = 'apple') + (tag2 = 'apple') + (tag3 = 'apple')) as total
FROM table_name
ORDER BY total DESC

 

The results would be

 

id | tag1  | tag2   | tag3   | total
6 | apple  | apple  | apple  | 3
5 | apple  | apple  | tomato | 2
7 | tomato | apple  | apple  | 2
1 | apple  | pear   | banana | 1
2 | pear   | orange | apple  | 1
4 | kiwi   | apple  | orange | 1
3 | orange | kiwi   | banana | 0

What you posted makes no sense. You stated that the tag fields contained "text" values - you can't "add" text values. You stated previously that you wanted the count of those text values that matched a search term. I already provided that. I'm guessing you didn't even try the code I provided because you didn't understand it.

Edited by Psycho
Link to comment
Share on other sites

Psycho, that is not what I am trying to achieve. Let me try to better word this.

 

I'm trying to echo the DISTINCT values from three different columns

 

Like this - SELECT DISTINCT tag1 FROM table - but instead of just tag1 I want it to be tag1, tag2, and tag3.

 

How is that anything like your initial request?

 

I have three tag columns in my mysql database - tag1, tag2, and tag3. Is it possible to count how many times tag words appear in tag1, tag2, and tag3 combined and then list in order of how many times they appear?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.