loading...

re: Best way to store array type data inside database? VIEW POST

FULL DISCUSSION
 
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.

 

Thank you for sharing the code sample.

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

 

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

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

image

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.

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.

Yeah I know that,

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

 

This is the best solution on the topic. +1

code of conduct - report abuse