3

I have something like this 2 tables: videos members

In the members table I have the name of each member:

1 Tom
2 Bob
3 Zack
4 Dan
5 Casey

In the videos table I have a column named members and I have the names in there seperated by commas

1. Tom,Dan
2. Casey,Zack,Bob
3. Tom,Casey,Dan,Zack
4. Zack,Bob,Dan

I'm trying to display how many times each member appears to get these results:

1 Tom = 2
2 Bob = 2
3 Zack = 3
4 Dan = 2
5 Casey = 2

Do I need to do something like SELECT SUM(members) WHERE and use LIKE?

Improve this question
2
  • 3
    what you need to do is normalize your data Commented Sep 27, 2016 at 6:32
  • Double check your expected result. Commented Sep 27, 2016 at 6:51

2 Answers 2

Reset to default
3

I would strongly suggest to normalize your data as others suggested.

Based on your current design you can use FIND_IN_SET to accomplish the result you want.

SELECT 
 M.id,
 M.name,
 COUNT(*) total
FROM members M 
INNER JOIN videos V ON FIND_IN_SET(M.name,V.members) > 0
GROUP BY M.name
ORDER BY M.id

See Demo

Running this query on your given data set you will get output like below:

| id |  name | total |
|----|-------|-------|
|  1 |   Tom |     2 |
|  2 |   Bob |     2 |
|  3 |  Zack |     3 |
|  4 |   Dan |     3 |
|  5 | Casey |     2 |

A must read

Is storing a delimited list in a database column really that bad?

More

This is how your vidoes table would look like if you normalize your data:

videos 

id   member_id
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I started to normalize my data. So now I have 3 tables videos, members, video_member. On video member I have 2 columns: video_id, member_id using the names above my columns would be filled like so: under video_id 1 1 and next to those under member_id 1 4 these two would be foreign keys and signify that video_id 1 has members Tom and Dan. In my PHP project, I query the videos table data and, by using Twig Template Engine, I use a for loop to go through each row and output the videos table data. Creating a thumbnail video gallery.However I also want to display the members
Do you want to show the same output as above from your normalized database design?
Eventually, but removing my setup of having name,name,name with the commas broke the way I displayed data. I want to be able to display The members that belong to each video. Before I just got the string 'name,name,name' and split it into an array then each name was displayed under the video thumbnail with their own link
Would you mind posting this as a separate question? It's really tough answering through comments
0

One way to go is to join the two tables, based on a like expression:

SELECT members.name, count (*) as counter from members inner join videos
ON videos.members like CONCAT('%,',members.name,',%')
GROUP BY members.name;

But I think the better solution will be like @e4c5 said in the comment - you need to normalize the data. the videos table should look like:

+---+-------+  
|ID | MEMBER|  
+---+-------+  
| 1 | Tom   |
| 1 | Dan   |
| 2 | Casey |
| 2 | Zack  |
| 2 | Bob   |
| 3 | Tom   |
| 3 | Casey |
| 3 | Dan   |
| 3 | Zack  |
| 4 | Zack  |
| 4 | Bob   |
| 4 | Dan   |
+---+-------+

That way, you can simply count on this table

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.