CODEWITHSUNDEEP
phpmysql
Diogo Alpha
Diogo Alpha

Reputation: 23

Select Distinct Values from different columns MySQL and PHP

I am having trouble with this. I have a mysql table with the following columns:

MySQL columns

This Table has more than the shown on the picture, however i just need to fetch data from the ones that are shown in the picture.

The columns contain tag words and they are repeated often. I need to grab a single array containing the Tags without getting the duplicate entries but getting all the unique ones.

How can i do this? What would be the best query for this purpose?

Also, if possible i would like to count the number of times that this tag appears on the table.

I intend to display the data on a PHP while() loop like this:

TagA (#videos)
TagB (#videos)
[...]

Upvotes: 0

Views: 44

Answers (2)

Rumbleweed
Rumbleweed

Reputation: 370

If I understand your question correctly, all tag words from any column are treated equally, so a union of all counts by each tag field should work.

select tag, sum(tagCount) as tagCount from
(select tag1 as tag, count(*) as tagCount from yourTable GROUP BY tag1
UNION
select tag2 as tag, count(*) as tagCount from yourTable GROUP BY tag2
UNION
select tag3 as tag, count(*) as tagCount from yourTable GROUP BY tag3
UNION
select tag4 as tag, count(*) as tagCount from yourTable GROUP BY tag4
UNION
select tag5 as tag, count(*) as tagCount from yourTable GROUP BY tag5)
GROUP BY tag

Caveat: I don't have a DB connection in front of me to try this out immediately.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You shouldn't be storing the tags in separate columns like that. Instead, they should be in a table with one row per video and on per tag.

Given this structure, you can do what you want with union all:

select tag, count(*)
from (select tag1 as tag from t union all
      select tag2 as tag from t union all
      select tag3 as tag from t union all
      select tag4 as tag from t union all
      select tag5 as tag from t
     ) t
group by tag;

Upvotes: 1

Related Questions