如何从数据库中选择基于类别中的匹配？ [英] how to select from database based on a match in category?

问题描述

当条目中有多个类别时，是否可以根据与其匹配的类别选择某些行？很难解释，所以我会告诉你。我在数据库中的行看起来像这样：

  ** article_title ** | ** article_content ** | ** category ** 
 
 Article-1 |一些内容在这里|所以我的查询看起来像这样：
  $ sql = mysqli_query（$ mysqli_connect，SELECT * FROM table WHERE category ='
 preg_match（例如word three）' 
  
我这样做的原因是一些文章将在多个页面上可用， 
解决方案
这是一种方法来匹配我通过数据库行中的条目查找的方法。您应该使用更灵活的数据库设计。创建一个单独的表，其中包含（一）文章和（许多）类别之间的一对多关系：
  CREATE TABLE IF NOT EXISTS`article`（
`article_id` int（11）NOT NULL AUTO_INCREMENT，
`article_name`varchar（255）NOT NULL，
 PRIMARY KEY（`article_id`）
）ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREMENT = 2; 
 
 INSERT INTO`articles`（`article_id`，`article_name`）VALUES 
（1，'Research Normalized Database Design'）; 
 
如果不存在，则CREATE TABLE如果不存在`article_category`（
`article_id` int（11）NOT NULL，
`category_id` int（11）NOT NULL 
） = InnoDB DEFAULT CHARSET = latin1; 
 
 INSERT INTO`article_category`（`article_id`，`category_id`）VALUES 
（1,1），
（1，2） 
 
如果不存在`categories`，则创建表（
`category_id` int（11）NOT NULL AUTO_INCREMENT，
`category_name`varchar（255）NOT NULL，
 PRIMARY KEY（`category_id`）
）ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREMENT = 3; 
 
 INSERT INTO`categories`（`category_id`，`category_name`）VALUES 
（1，'Databases'），
（2，'Normalization'）; 
  
查询随后变得简单：
  SELECT 
 * 
 FROM 
文章AS a 
 JOIN 
 article_category AS pivot ON a.article_id = pivot.article_id 
 WHERE 
 pivot.category_id = 2 
  
或执行类似操作：
  SELECT 
 * 
 FROM 
文章AS a 
 JOIN 
 article_category AS枢轴ON a.article_id = pivot.article_id 
 JOIN 
类别AS c ON pivot.category_id = c.category_id 
 WHERE 
 c.category_name ='Normalization'
  
 
Is it possible to select certain rows based on a category which matches it when there are multiple categories in the entry?  It's hard to explain so I'll show you.  The row I have in the database looks like this:
**article_title**   |   **article_content**    |    **category**

Article-1           |   some content here      |    one,two,three,four
So my query looks like this:
$sql = mysqli_query($mysqli_connect, "SELECT * FROM table WHERE category='
preg_match(for example the word three)'");
Reason why I'm doing that is some articles will be available on multiple pages like page one and page three...so is there a way to match what I'm looking for through the entry in the database row?
解决方案
You should use a more flexible database design.  Create a separate table that holds the one-to-many relationships between (one) article and (many) categories:
CREATE TABLE IF NOT EXISTS `articles` (
  `article_id` int(11) NOT NULL AUTO_INCREMENT,
  `article_name` varchar(255) NOT NULL,
  PRIMARY KEY (`article_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `articles` (`article_id`, `article_name`) VALUES
(1, 'Research Normalized Database Design');

CREATE TABLE IF NOT EXISTS `article_category` (
  `article_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `article_category` (`article_id`, `category_id`) VALUES
(1, 1),
(1, 2);

CREATE TABLE IF NOT EXISTS `categories` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_name` varchar(255) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `categories` (`category_id`, `category_name`) VALUES
(1, 'Databases'),
(2, 'Normalization');
Querying then becomes as simple as:
SELECT 
    *
FROM 
    articles AS a
JOIN 
    article_category AS pivot ON a.article_id = pivot.article_id 
WHERE 
    pivot.category_id = 2
Or do something like:
SELECT 
    *
FROM 
    articles AS a
JOIN 
    article_category AS pivot ON a.article_id = pivot.article_id 
JOIN 
    categories AS c ON pivot.category_id = c.category_id
WHERE 
    c.category_name = 'Normalization'


                        
这篇关于如何从数据库中选择基于类别中的匹配？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！