0

New to PHP/MySQL and building a CMS. I need to know how to associated an Article with multiple Categories eg "My Article 1" has to be in 3 categories and "My Article 2" has to be in only 2 etc.

It's basically just the table set up to begin with. Here's what I have:

Table = articles
Fields = id, publicationDate, title, summary, content

Table = categories
Fields = id, categoryTitle

I know a little about joins and relationships etc but I want to get this right from the start.

Raidri
  • 17,258
  • 9
  • 62
  • 65
Crashdesk
  • 665
  • 8
  • 27

2 Answers2

2

You need a many-to-many table that will link article ids to category ids

CREATE TABLE articles_categories (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    articles_id INT,
    category_id INT
) TYPE=myisam;

So for each category that you associate with an article you will need to insert a row into this table.

Perhaps something like:

$currentArticle = array('id' => 99, 'name' => 'Test Article');
$currentCategory = array('id' => 1, 'name' => 'First Category');
mysql_query(sprintf('INSERT INTO articles_categories (articles_id, category_id) VALUES (%d, %d)', $currentArticle['id'], $currentCategory['id']));
Yes Barry
  • 9,514
  • 5
  • 50
  • 69
1

You need a third table, called articles_to_categories or whatever, with two fields:

  • article_id
  • category_id

Add an entry for each article and category association. Set the PK to be both columns.

Brad
  • 159,648
  • 54
  • 349
  • 530
  • I didn't know you could have two primary keys in one table – Crashdesk Nov 02 '11 at 19:44
  • Not two primary keys... one key that uses both columns. This is called a composite key. Alternatively, you can add another column, but that is just a waste of space, since this table won't have anything else but these associations. – Brad Nov 02 '11 at 19:44
  • Okay I will try that now. As an example how would I return information in a SQL query eg. My Article 1 is in category 1, category 2 and category 3? – Crashdesk Nov 02 '11 at 19:49
  • `SELECT categories.* FROM categories, articles_to_categories WHERE categories.id=articles_to_categories.category_id AND articles_to_categories.article_id=12345;` – Brad Nov 02 '11 at 19:51