SQL to implement n-n relationship for Multiple Categories in MySQL & PHP

Question

I wish to know what SQL is needed to be passed to implement multiple categories for an article.

I have created 3 tables.

ARTICLES:
id, title, content

CATEGORIES:
id, name

RELATION:
article_id, cat_id

I am successfully able to create the first two tables, and store data in them when user submits the form. But, I have no clue how to update the RELATION table. I searched on stackoverflow, and I learned that I need to use a many to many relationship. I have idea about it. But, I do not know how to do it practically i.e. the SQL.

The Categories are obviously added while the post is published, So i need to update this table only after the first two tables have been updated.

If someone can guide me to a tutorial or in the right direction I shall be greatful.

I believe it depends on the purpose of `RELATION` table. Should it include **every** pair of the two IDs (after they are added to `ARTICLES` and `CATEGORIES`) or only **unique** ones? — PM 77-1, Feb 28 '13 at 18:55
Every Pair of Ids. Then only, Mult Category can be implemented. — Rohitink, Feb 28 '13 at 19:00

score 0 · Answer 1 · answered Feb 28 '13 at 18:53

0

This should be taken care of by your SQL DBMS. For example, if you set your relationship's foreign keys to cascade delete or update, when something changes in the parent, the children will also be deleted/updated. Can you give me an example of an update that you would expect to make to the first two tables and the resulting update to the RELATION table?

answered Feb 28 '13 at 18:53

jason

3,821
10
63
120

I want the system to work just like it works in blogs. You may think of blogger or wordpress. – Rohitink Feb 28 '13 at 18:56
That response provides no help. What you are asking to do is manage a one to many relationship to prevent update and deletion anomalies. This doesn't appear to be a php, or even SQL, issue but a database normalization issue. You should look into how to normalize a database and managing one-to-many relationships, like this one: http://databases.about.com/od/specificproducts/a/normalization.htm. If you have a specific scenario you need help with, let me know. – jason Feb 28 '13 at 19:16

score 0 · Accepted Answer · edited May 23 '17 at 11:49

Assuming that post and article are synonyms, then each time a new post is published and its category is determined you need to 'INSERT' a record into 'RELATION' table.

When you originally create tables you will need to identify Primary and Foreign keys (CONSTRAINTS) and (if so desired) specify whether CASCADE should be enabled.

Apparently you already know how to CREATE tables and INSERT rows.

You may want to Google for PRIMARY KEY, FOREIGN KEY, CASCADE ON DELETE in conjunction with MYSQL and PHP.

Also see if the following helps any: How do I use on delete cascade in mysql?.

SQL to implement n-n relationship for Multiple Categories in MySQL & PHP

2 Answers2