2

Follow up question from: Adding sum from 2 different tables

I created 3 tables: members videos video_member

The third one being used to 'link' the first 2 tables, as one member can belong to many videos and one video may have many members.

enter image description here

I used to just have a members column in the videos table and I would put the members in there like so:

video_id | members
 1       | Tom,Dan
 2       | Casey,Zack,Bob
 3       | Tom,Casey,Dan,Zack
 4       | Zack,Bob,Dan

In my PHP project I would query the data from videos table, and by using Twig template engine, I would use a for loop to go through each row and create a gallery like page. Each 'box' with a thumbnail image and under it the data from videos table.

I needed to display the members individually so each would have their own link. So I would split the string by commas. (ex: Casey,Zack,Bob ) into an array then just looped through it.

With my new format in the image I posted, How would I go about displaying members?

What I've done: This is what I have in my video.php

$sql = 'SELECT * FROM videos';
$sql2 = 'select v.video_id "v_id", m.name "name" from videos v, video_member b, members m where b.video_id = v.video_id and b.member_id = m.member_id';

$result = $mysqli->query($sql);
$result2 = $mysqli->query($sql2);

echo $twig->render('video.html', array('videos'=> $result, 'members' => $result2));

I then use the first result to output the data from videos table in my videos.html

{% for v in videos %}
*html layout*
{% endfor %}

since I have a for loop going through each row from videos, I added this inside the loop

{% for m in members %}

{% if v.video_id == m.v_id %} <a href='{{memberurl}}/{{m.name}}/'>{{m.name}}</a>

{% endif %}
{% endfor %}

So the idea is: every time it loops through a row from videos, it checks the video_id and v_id(fk) in the new table created with my $sql2 and if there are any matches it'll display those 'name's.

$sql2: enter image description here

Right now it's working this way, I'm just need to implement commas right now but...

I'm mainly learning to create and use normalized data and wanted to know if I'm on the right path because I want to implement this with tags and future items. Is this the proper/fine way to display members?

I'm new to a lot of this and every time I learn something new it feels great, thanks for any help!

video.html enter image description here

Community
  • 1
  • 1
Dan
  • 185
  • 3
  • 11
  • Please be more specific on what you are trying to achieve. Because there are many ways to improve code and it can be by manageability, architecture, performance etc. Also a query cannot be said bad or good based on the amount of data, it can effect the performance which also can be tackled in many ways. – DC- Sep 28 '16 at 08:51

1 Answers1

0

In most cases, you return all the data you want in a single query, and put that on the page.

For instance, you can run a single query like so: $sql2 = 'select * from videos v, video_member b, members m where b.video_id = v.video_id and b.member_id = m.member_id'; and then in your front end code include logic when the videoID changes to draw your next video box.

What you're doing is joining the two datasets in your template; this is inefficient, especially if you have complex queries, or when you have a filter (e.g. "videoName like 'Ferris%').

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52