Count how many times a tag appears

[dachshund]

Hi,

 

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?

 

Thanks

 

 

[vinny42]

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?

[Psycho]

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%'

[dachshund]

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!

[vinny42]

 

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;

[dachshund]

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)){
 
}
 

[dachshund]

basically Im trying to work out how to make something like this work

 

$sql = mysql_query("SELECT DISTINCT tag_one + tag_two + tag_three FROM table ORDER BY (number of occurrences) DESC");

[dachshund]

does anyone know if the above is possible, and how you would group together the columns to echo out as one?

[vinny42]

 

 

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

 

No, it's correct.

 

 

basically Im trying to work out how to make something like this work

 

That's not what you said before.

[dachshund]

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];
 
}

[Psycho]

 

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 September 13, 2013 by Psycho

[dachshund]

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.

[vinny42]

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

 

That is that my query does.

[dachshund]

thanks vinny, I was echoing out tag_one instead of just tag. thanks for your help.

[Psycho]

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?

[dachshund]

Like you say "The quality of the responses received is directly proportional to the quality of the question asked."

 

I was always trying to achieve what vinny has helped me do. Sorry if I worded my question badly at first.

[fenway]

FYI, if you're truly using comma-seperated values, FIND_IN_SET() can help too -- not that I recommend this approach.