将一个表的类别附加到另一个MySQL的条目 [英] Attaching categories from one table to entries in another MySQL

问题描述

我有一个数据库，该数据库接收用户提交的数据，我想将这些条目归类到大约10个类别中的一个或几个.

I have a database which takes user submitted data, the entries from which I want to group under one or several of about 10 categories.

例如，您将条目添加到我的网站，说出所有关于您的业务的信息(汽车代客服务)，并且我为您提供了将条目分类为10个固定类别(汽车，移动服务)中任何数量的机会等等)，因此，如果用户搜索汽车"或移动服务"类别下的企业，则会从查询中返回您的企业.

So for example, you add your entry to my site, say its all about your business (a car valeting service), and I offer you the opportunity to categorize your entry in any number of 10 fixed categories (automotive, mobile service, etc), so if a user searches for businesses under the 'automotive' or 'mobile service' category, your business is returned from the query.

因此，正如我从此处的大部分答案中获取的那样，要实现此目的，我的数据库中包含三个表(以下结构)，一个用于您的业务条目，一个列出了设置类别的表以及一个与之相关的表已从前两个表中添加了唯一键.

So as I have taken from most of the answers on here, to achieve this I have my database with three tables (structure below), one for your business entry, one listing the set categories, and one relational table to which I've added the unique key from the prior two tables.

CREATE TABLE `business` (
`bus_id` INT NOT NULL AUTO_INCREMENT, 
`bus_name` VARCHAR(50) NOT NULL, 
`bus_dscpn` TEXT NOT NULL, 
`bus_url` VARCHAR(255) NOT NULL,
PRIMARY KEY (`bus_id`)
)

CREATE TABLE `categories` (
`category_id` INT NOT NULL AUTO_INCREMENT, 
`category_name` VARCHAR(20) NOT NULL, 
PRIMARY KEY (`category_id`)
)

CREATE TABLE `tbl_works_categories` (
`bus_id` INT NOT NULL, 
`category_id` INT NOT NULL
)

我终生无法发现的是，当您从用PHP处理的表单中选择与您的业务相关的类别时，如何将它们实际关联到数据库中！

What I cannot figure out for the life of me is, when you select which categories you'd like your business associated with from my form which is processed with PHP, how to actually associate them in the database!

推荐答案

我假设您的表单返回所选每个类别的类别ID，并且这些ID与您的类别表中的category_id相对应.

I assume your form returns the category IDs for each category selected and that those IDs correspond with category_id in your categories table.

首先，您将业务记录插入到业务表中，这将为您提供自动递增编号(PHP中的mysql_insert_id函数或所使用的库的任何函数).因此，您在某个位置存储了$idfromfunction.

First you insert your business record into your business table, this will give you the auto increment number (mysql_insert_id function in PHP or whatever function for the library you are using). So, you have an $idfromfunction somewhere stored.

使用该公司ID，然后遍历您的类别ID

With that business ID, then loop through your category IDs

(也许已经使用过):

SELECT category_id, category_name
FROM categories

(...，并通过复选框或下拉列表或其他方式将此列表显示给您的用户)

(... and showed this list to your user through checkboxes or drop list or whatever)

用户选择一个$categoryidfromform后，您可以将其插入到tbl_works_categories表中

After the user chooses one $categoryidfromform, you can then insert into your tbl_works_categories table

INSERT INTO tbl_works_categories
    (bus_id, category_id)
VALUES
    ( $idfromfunction, $categoryidfromform) ;

这篇关于将一个表的类别附加到另一个MySQL的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！