Select Distinct Values from different columns MySQL and PHP

Question

I am having trouble with this. I have a mysql table with the following 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)
[...]

Gordon Linoff · Accepted Answer · 2016-01-17 18:12:28Z

1

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;

answered Jan 17, 2016 at 18:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Diogo Alpha Over a year ago

I know i shouldn't be doing it like this, but its functional, its enough and it will be processed more quickly because its one less query to deal with

Rumbleweed · Accepted Answer · 2016-01-18 20:24:46Z

0

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.

edited Jan 18, 2016 at 20:24

answered Jan 17, 2016 at 18:27

Rumbleweed

3701 silver badge9 bronze badges

4 Comments

Diogo Alpha Over a year ago

Haven't tested yet, i'll do in about 15minutes. Sounds very logic your soulution, I will be able to fetch it as $var['tag'] and $varTotal['tagCount'] if i got your idea correctly, right?

Diogo Alpha Over a year ago

<?php include("includes/dbconnect.php"); $getTagsQuery = mysql_query(" select tag, sum(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 "); while ($row = mysql_fetch_array($getTagsQuery)){ 	echo " 		".$row['tag']." 		"; } ?>

Not Working

Rumbleweed Over a year ago

You might have to change the first line "sum(tagCount)" aggregate field to "sum(tagCount) as tagCount" in the first line, but yes I believe so.

Diogo Alpha Over a year ago

include("includes/dbconnect.php"); $getTagsQuery = mysql_query(" 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 "); while ($row = mysql_fetch_array($getTagsQuery)){ 	echo " 		".$row['tag']." 		"; }

Collectives™ on Stack Overflow

Select Distinct Values from different columns MySQL and PHP

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related