CODEWITHSUNDEEP
phpmysql
Ronel Solano
Ronel Solano

Reputation: 63

Approach on how to assign multiple categories to a single post in PHP?

I am currently working on a custom cms where user can assign multiple categories into single post. My questions are:

  1. What is the correct mysql table schema for this?
  2. How do you store the multiple selected categories in the blog post table?

Please let me know if you have clarification.

Upvotes: 3

Views: 2712

Answers (5)

eggyal
eggyal

Reputation: 125865

  1. What is the correct mysql table schema for this?

    One way is to create a relationship table:

    CREATE TABLE cms.Posts (
  PostID       SERIAL,
  PostContent  TEXT,
  PRIMARY KEY (PostID)
) Engine=InnoDB;

CREATE TABLE cms.Categories (
  CategoryID   SERIAL,
  CategoryName VARCHAR(20),
  PRIMARY KEY (CategoryID)
) Engine=InnoDB;

CREATE TABLE cms.PostCategories (
  PostID BIGINT UNSIGNED NOT NULL,
  CategoryID BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (PostID, CategoryID),
  FOREIGN KEY (PostID)     REFERENCES cms.Posts      (PostID),
  FOREIGN KEY (CategoryID) REFERENCES cms.Categories (CategoryID)
) Engine=InnoDB;

  2. How do you store the multiple selected categories in the blog post table?

    You don't, you store them in the PostCategories table:

    $dbh = new PDO('mysql:charset=utf8', $username, $password);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$dbh->prepare('INSERT INTO cms.Posts (PostContent) VALUES (?)')
    ->execute([$_POST['content']]);

$qry = $dbh->prepare('
  INSERT INTO cms.PostCategories (PostID, CategoryID) VALUES (?, ?)
');

$qry->bindValue(1, $dbh->lastInsertId());
$qry->bindParam(2, $category);
foreach ($_POST['categories'] as $category) $qry->execute();

Upvotes: 2

Cups
Cups

Reputation: 6896

blog_posts
==========
id | 23
title | My title

categories
==========
id | 1
name | yogurt
// another row
id | 2
name | motor cycles

category_blog_post
==================
23 | 2
23 | 1

Showing an entry where the blog post My title is tagged as being about yogurt and motor cycles

Upvotes: 0

Tarun
Tarun

Reputation: 3165

For many to many relationship you can design your tables as

caegory table
    categoryId categorydescription

post table    
    postid     postText

a third table to link them    
    categoryId  postId

Upvotes: 0

merlinbeard
merlinbeard

Reputation: 218

The solution everyone (including myself) seems to be using is what @rwilliams described. These work well when query is limited to a single tag. For querying for 2 tags (tagged as personal and tagged as travel), you need to use a join. When querying becomes more complex, this starts to break down. MongoDB would be a better solution I think.

Upvotes: 0

rwilliams
rwilliams

Reputation: 21497

  1. What you have here is a many to many relationship. The standard method of storing the relationships would be to use a join table for categories and posts. That table will only have category ids and post ids.

  2. The post table will have no information regarding categories itself.

Upvotes: 4

Related Questions