Discussion on: Best way to store array type data inside database?

[Florian Rand]

CREATE TABLE post (
`post_id` INT NOT NULL PRIMARY KEY,
`title` VARCHAR(50)
);

CREATE TABLE tags (
`tag_id` INT NOT NULL PRIMARY KEY
`tag_name` VARCHAR(20),
);

CREATE TABLE post_tags (
`post_id` INT NOT NULL,
`tag_id` INT NOT NULL,
PRIMARY KEY(`person_id`, `tag_id`)
);

The post_tags table contains the foreign keys of tags and posts.
Something like this (:

post_id	tag_id
1	1
1	5
1	3
2	2
2	1
2	3

To retrieve a post and all its tags, you need to JOIN the tables in your queries:

SELECT p.*, t.*
FROM post p
INNER JOIN post_tags pt
ON pt.post_id = p.post_id
INNER JOIN tags t
ON t.tag_id = pt.tag_id

This answer is adapted from How to store arrays in MySQL from StackOverflow.

[Yashu Mittal]

Thank you for sharing the code sample.

But, I want to share multiple tags for single post.

[Florian Rand]

To retrieve a post and all its tags, you need to JOIN the tables in your queries

[Yashu Mittal]

On using the INNER JOIN clause it doesn't get the data if specific post have multiple tag_id

SELECT
    *
FROM
    post
INNER JOIN tag
ON post.post_id = tag.post_id;

it only return the data for 3rd row according to the above image.

[Florian Rand]

INNER JOIN tag

Can you see the mistake here?

You need to do the JOIN with the post_tags table (with both references, posts and tags) to work.

[Yashu Mittal]

Yeah I know that,

I tried with the both with and without post_tag table, it was return 0 rows.

[Hassan Farid]

This is the best solution on the topic. +1