how to select from database based on a match in ca

Question

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?

Answer 1

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'