MySQL multi table bug

[Rink Web Site Developer]

Hello,

I am currently having a bug with a MYSQL query for an online calendar I am writing. I have been looking at this bug for a couple days now and am still not able to figure out why it continues to do it.

I have 2 tables called calendar_users and comments

calendar_users has these fields in it: user_id, dispname, username, password, color, login

comments has these fields in it: comment_id, orig_user_id, month_posted, day_posted, year_posted, comment, upd_user_id


Here is the query im looking at right now:

$query = "
SELECT DISTINCT a.color, o.comment 
FROM calendar_users a, comments o 
WHERE o.month_posted='" . $month . "' 
AND o.day_posted='" . $date . "' 
AND a.user_id=o.upd_user_id";
 

k.. the issue I am having with this query is there are 2 comments posted on the same date by 2 different users. However on the page itself is shows 4 comments: the first one, the second one, the first one again, the second one again. The comments are at least matching up with the color chosen for them so I know that the query is halfway working, but am unable to fix the repeating issues. Any ideas you can give?

[Atli]

Hi.

Try be more specific with your joins. The way you use them, all columns in table A are simply joined with all columns from table B.

Like, for example:

SELECT *
FROM `calendar_user` AS u
INNER JOIN `comments` AS c
    ON u.user_id = c.upd_user_id
 

This will filter the data *before* the where clause is applied, removing possible JOIN duplications.

If this doesn't work, please show us the exact table structure of your tables (the CREATE statements, preferably), and an example of the data that you are working with. Without that we are just guessing, really.

P.S
Please use [code] tags when posting your code examples.

[code] ...Code goes here... [/code]

Thank you.

And I hope you don't mind that I added a few new-lines to your query as well, just so it would be easier to read. Having it all in one line makes it very hard to read.

Moderator

[Rink Web Site Developer]

Thank you for the help with the query issue. While the suggestion you gave did fix the bug in the query when checked with the phpMyAdmin query window, the problem did persist on the page itself. After tracking down the issue in my php code, I did finally find the problem on the page. Thank you again for your help. :)