Not able to select UNIQUE rows in query

Sonic_Rage

I have two tables. One contains the articles, the other contains the comments made for that particular article. I want to display only the article title that actully has comments to it so the writer can see all of the comments made to his article. But it makes duplicate lines. One article has 5 comments, so there is 5 lines of it, the other is 2, and then there is one.

Here is my code which I thought was doing right:

        $query = "SELECT DISTINCT rpg_article_comments.articleid,rpg_article_comments.date,rpg_articles.writer,rpg_articles.title 
                         FROM rpg_article_comments,rpg_articles 
                         WHERE rpg_articles.articleid = rpg_article_comments.articleid";
        $result = @mysql_query($query);
        $num_results = mysql_num_rows($result);
         if($num_results =='')
         {       

          echo"<TR><TD COLSPAN=4><P>No Article Comments Available</TD></TR>";
         }
         else
         {
            while ($row = mysql_fetch_array($result))
             { 
              $articleid = $row["articleid"]; 
              $title = stripslashes($row["title"]); 
              $newcomment = ''; 
               if ($row['date'] >= strtotime('5 days ago')) 
                { 
                   $newcomment = "<font color=\"red\">New!</font>"; 
                }
               $writer = $row["writer"];
               print "<TR>\n";
               echo "<TD><P class=small><A class=link HREF=\"articles.php?mode=comments&articleid=$articleid&title=$title\">$title</A></TD>\n"; 
               echo "<TD><P class=small>$writer</TD>\n"; 

           get_comment_count($articleid);

           echo "<TD><P class=small>$newcomment</TD>\n"; 
           echo "</TR>\n"; 
          } 
       }

Here is the output on screen:

Scooping The Cream From The Top Of The Crop 5

Lock And Load - Barrett Is Back 2

Scooping The Cream From The Top Of The Crop 5

Lock And Load - Barrett Is Back 2

Scooping The Cream From The Top Of The Crop 5 New!

Quick! I got a Ghost in my Shell! 1 New!

What am I missing?

The_Chancer

try mysql_fetch_assoc instead of array.....

Sonic_Rage

Nope, didn't work. But thanks.

mtimdog

Use two queries.

One to select all the article ids and name/title.
The other while loop to loop through all comments.

The_Chancer

How about using a group by clause at the end ??

Sonic_Rage

how do you do that? sorry I don't know.

The_Chancer

At the end of the first query, use GROUP BY rpg_articles.articleid

This will group all the articles with the same id together.

What does the $numresult give you ? as this will give you an idea of if it is your query and the way it is structured that is wrong, or the following code...

Sonic_Rage

That worked. Thank you so much, was driving me nuts figuring it out.