MySQL query problem

[rp91]

Hi guys,

I have been racking my brain for a while now trying to sort out a problem I have been having with my mysql query and thought this would be the best place to get some help and learn how to edit my query!

So, users are able to post items which have associated tags (that are pre-defined). If you look at the link below I am trying to enter into this table the post id, with the tag that the user had selected. The correct inserts are highlighted in blue but the rest are duplicates where the query adds the same tag_id for every single existing post!

Click Here for the snapshot of the table.

The mysql query is as follows:

mysqli_query("INSERT INTO post_tag(post_id, tag_id) SELECT post_id, tag_id FROM posts INNER JOIN tag WHERE username = '$userName' AND tag IN ('$music','$sport','$tech')") or die ('Query is invalid: ' . mysql_error());;

Any help will be so appreciated! Thanks.

[AleMonteiro]

Please post a picture from your database diagram, so we can know how your database is structured.

For what I deduced, your select will return for each post 3 records, one for each tag. So, if you have 10 posts and 3 tags, it will return 30 records.

[rp91]

A table to store the post_id and the tag/s (pre-defined tags only, a user cannot create their own) associated with the post which is the snapshot of I had shown:

CREATE TABLE `post_tag` (
  `post_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
)

Another table 'tag' to store the tag_id's, with the names of the tags associated with each id. These are limtited to three tags at the moment; music, sport and tech :

CREATE TABLE `tag` (
  `tag_id` int(10) NOT NULL AUTO_INCREMENT,
  `tag` varchar(30) NOT NULL,
  PRIMARY KEY (`tag_id`)
)

[AleMonteiro]

Post your posts table also

[rp91]

CREATE TABLE `posts` (
  `post_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `item` varchar(255) NOT NULL,
  `time_posted` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`post_id`)
) 

[AleMonteiro]

Ok, now I got the picture.

Now let me ask, what do you want to do? For each post you want to insert each tag into the post_tag table? I mean, if you have 10 posts and 3 tags, you want to have 30 records in the post_tag table?

[rp91]

Well, for each post, a user can select up to three of the specified tags. So, the tag_id's are as follows: music = 1, sport = 2, and tech = 3. If a user submits a post with sport and tech for example as their tags, the post_tag table will be as such:

post_id | tag_id
  1     |   2
  1     |   3

[AleMonteiro]

Ok, so you can't use an select like from the posts tables. Because if you do, it'll insert for all posts, not only the one that the user selected the tags.

You need to insert the tags for each post, like this:

INSERT INTO post_tag(post_id, tag_id) 
SELECT
    $post_id, tag_id 
FROM
    tag
WHERE
    tag IN ('$music','$sport','$tech')

[rp91]

That worked! Thank you!