MySQL query and PHP problem.

rp7

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!

http://oi47.tinypic.com/2dratmq.jpg

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.

traq

Without knowing your DB schema, I might suggest grouping the results to filter out duplicate combinations:

$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' )
    GROUP BY post_id,tag_id";

if( ! mysqli_query( $query ) ){
    # die(); # WHY WONT IT
    print mysqli_error();
}

Note that, in your original example, you were trying to use [man]mysqli_query/man in concert with [man]mysql_error/man, which I corrected here. Also, die() is a horrible approach to error handling: I hope you aren't doing this in your live scripts.

rp7

Thanks for your reply! And I will definitely change my mysql debugging methods.

However, this did not do the trick! And so I have described the two relevant tables:

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`)
)

traq

rp7;11024775 wrote:
this did not do the trick!

How so? what were your results?

rp7;11024775 wrote:
And so I have described the two relevant tables

what about the [font=monospace]posts[/font] table?

. . .

You could probably use your current statement if you made the post_id + tag_id combination a primary key, and used INSERT IGNORE:

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

$query = "INSERT IGNORE 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' )";

...not saying that that's the best solution, but it should solve your immediate problem.