链接表和SQL SELECT查询

时间：2022-08-06 23:40:58

I have a few linked tables in my custom forum: categories, sub_categories and posts

我的自定义论坛中有一些链接表：categories，sub_categories和posts

basically, I have people able to add up to three categories and five sub-categories when they make a new post.

基本上，我有人能够在他们发布新帖子时添加最多三个类别和五个子类别。

I also enable people to 'listen' to certain categories and sub-categories and have them in an easy to access bar at the side of the page.

我还让人们“听”某些类别和子类别，并将它们放在页面侧面易于访问的栏中。

My tables are set up thus (only showing relavent fields for ease):

我的表格就这样设置了（只显示了相关字段）：

posts:

帖子：

id              INT
category_id     VARCHAR(12)
sub_category_id VARCHAR(35)

categories:

类别：

id      INT
name    VARCHAR(20)

sub_categories:

- 行业标准：

id      INT
name    VARCHAR(20)

in my posts table, I store the set categories and sub-categories by their ID in the following format:

在我的帖子表中，我按照以下格式按ID存储设置的类别和子类别：

category_id     [2][4][8]
sub_category_id [1][2][3][4][5]

thus enabling me to execute the following query in PHP and get the post based on category and sub-category:

从而使我能够在PHP中执行以下查询并根据类别和子类别获取帖子：

SELECT * FROM posts WHERE category_id LIKE '%[{$id}]%' AND sub_category_id LIKE '%[{$id2}]%'

the problem I have is selecting the sub_categories for the access bar that people 'listen' to...

我遇到的问题是为人们'听'的访问栏选择sub_categories ...

$sql = "SELECT title, id FROM categories";
$query = mysql_query($sql);
$list = array();
while ($row = mysql_fetch_assoc($query)){
    $list[$row['title']] = array();
    $sql = "SELECT sub_categories.title FROM sub_categories, posts WHERE (category_id LIKE '%[{$row['id']}]%') AND (????) LIMIT 0,100";
    $query = mysql_query($sql);
    while($result = mysql_fetch_assoc($query)){
        $list[$row['title']][] = $result['title'];
    }
}
print_r($list);

Obviously you can see where I am stuck (????), but before I explain what I am trying to do, I'll explain what the output I am looking for is.

显然你可以看到我被困在哪里（????），但在我解释我想要做什么之前，我会解释我正在寻找的输出是什么。

when I print the $list array, I want it to print a multi-dimensional array featuring the categories as the first key, with their values being an array of sub-categories that have been tagged in the main category.

当我打印$ list数组时，我希望它打印一个以类别为第一个键的多维数组，其值是一个已在主类别中标记的子类别数组。

The problem I have is that in my sub_category_id field on the post table, remember the values are stored in the format [1][2][3] and I need to check the value against the subcategory field id.

我遇到的问题是在post表的sub_category_id字段中，记住值以[1] [2] [3]的格式存储，我需要根据子类别字段id检查值。

I have tried the following:

我尝试过以下方法：

"SELECT sub_categories.title FROM sub_categories, posts WHERE (category_id LIKE '%[{$row['id']}]%') AND (sub_category_id LIKE '%[sub_categories.id]%') LIMIT 0,100"

But it didn't work. I don't know whether there is an error in my query or whether it even SHOULD work, but I would be grateful if anyone could tell me how to do it or where I am going wrong in my code!

但它没有用。我不知道我的查询中是否有错误，或者它是否应该有效，但如果有人能告诉我如何做或我的代码出错，我将不胜感激！

NB. I am trying to find which sub_categories appear in which categories based on people tagging them in a post together.

NB。我试图找到哪些sub_categories出现在哪些类别基于人们在帖子中一起标记它们。

1 个解决方案

#1

3  

You're facing problems because you're not aware about few concepts in database...

你遇到了问题，因为你不知道数据库中的几个概念......

In your case, you want to create associations called "many-to-many". It means that a category can be used in many post and a post can be represented by many category.

在您的情况下，您想要创建称为“多对多”的关联。这意味着一个类别可以在很多帖子中使用，一个帖子可以用很多类别来表示。

To translate that into a "SQL" world, you have to create an intermediate table.

要将其转换为“SQL”世界，您必须创建一个中间表。

this table will store both identifier of the two tables.

该表将存储两个表的标识符。

for exemple:

举个例子：

-------------------
|  categorisation |
-------------------
|    post_id      |  => is the link to posts
|   category_id   |  => is the link to categories
-------------------

When you create a new post, you create a new object in the table post. But you also create N records in the table categorisation.

创建新帖子时，您将在表格帖子中创建一个新对象。但您也可以在表分类中创建N条记录。

When you want to retrieve which categories applied to this post, you can do a query like that:

如果要检索应用于此帖子的类别，可以执行以下查询：

SELECT post.id, post.name 
FROM post 
INNER JOIN categorisation on (post.id = categorisation.post_id)
INNER JOIN category ON (categorisation.category_id = category.id)

I think you need to read some articles on the web about database before progressing in you project ;)

我认为你需要在网络上阅读一些关于数据库的文章，然后才能进入你的项目;）

#1

3  

You're facing problems because you're not aware about few concepts in database...

你遇到了问题，因为你不知道数据库中的几个概念......

In your case, you want to create associations called "many-to-many". It means that a category can be used in many post and a post can be represented by many category.

在您的情况下，您想要创建称为“多对多”的关联。这意味着一个类别可以在很多帖子中使用，一个帖子可以用很多类别来表示。

To translate that into a "SQL" world, you have to create an intermediate table.

要将其转换为“SQL”世界，您必须创建一个中间表。

this table will store both identifier of the two tables.

该表将存储两个表的标识符。

for exemple:

举个例子：

-------------------
|  categorisation |
-------------------
|    post_id      |  => is the link to posts
|   category_id   |  => is the link to categories
-------------------

When you create a new post, you create a new object in the table post. But you also create N records in the table categorisation.

创建新帖子时，您将在表格帖子中创建一个新对象。但您也可以在表分类中创建N条记录。

When you want to retrieve which categories applied to this post, you can do a query like that:

如果要检索应用于此帖子的类别，可以执行以下查询：

SELECT post.id, post.name 
FROM post 
INNER JOIN categorisation on (post.id = categorisation.post_id)
INNER JOIN category ON (categorisation.category_id = category.id)

I think you need to read some articles on the web about database before progressing in you project ;)

我认为你需要在网络上阅读一些关于数据库的文章，然后才能进入你的项目;）

标签：

相关文章