链接表和SQL SELECT查询
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 ;)
我认为你需要在网络上阅读一些关于数据库的文章，然后才能进入你的项目;）