显示标准化数据 [英] Displaying normalized data

问题描述

关注以下问题: 从2个不同的表中求和

我创建了3个表: members videos video_member

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.

我以前在videos表中只有一个members列，我会像这样将成员放在那里:

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

在我的PHP项目中，我将从videos表中查询数据，并使用Twig模板引擎，使用for循环遍历每一行并创建一个类似于页面的画廊.每个框"都有一个缩略图，并在其下方是来自视频表的数据.

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.

我需要单独显示成员，以便每个成员都有自己的链接.所以我将用逗号分隔字符串. (例如:Casey，Zack，Bob)排列成一个数组，然后循环遍历它.

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?

我所做的: 这就是我在video.php中所拥有的

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));

然后我使用第一个结果从我的videos.html中的视频表中输出数据

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

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

因为我有一个for循环遍历视频的每一行，所以我将其添加到了循环中

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

所以这个想法是:每次它循环遍历videos中的一行时，它都会检查用我的$ sql2创建的新表中的video_id和v_id(fk)，如果有匹配项，它将显示那些名称.

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:

现在它正以这种方式工作，我现在只需要实现逗号，但是...

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

video.html

推荐答案

在大多数情况下，您在单个查询中返回所有想要的数据，并将其放在页面上.

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

例如，您可以运行一个查询，如下所示:$sql2 = 'select * from videos v, video_member b, members m where b.video_id = v.video_id and b.member_id = m.member_id';，然后在videoID更改以绘制下一个视频框时，在前端代码中包含逻辑.

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

这篇关于显示标准化数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！