CODEWITHSUNDEEP
phpmysqljoinrows
reformed
reformed

Reputation: 4780

MySQL JOIN multiple rows of one table to one row of other table

I have two tables:

Table 1: Articles (id, name, title, content)
Table 2: Comments (id, comment, a_id)

Table 2 contains comments with a_id corresponding with the id field in Table 1, and has several rows for the same article

I used the following select statement:

$result = mysql_query("SELECT * FROM articles a JOIN comments c ON a.id = c.a_id WHERE a.name='$a'");

$row = mysql_fetch_array($result);
echo $row["title"]."<br/>".$row["content"]."<hr/>".$row["comments"]

Security issues aside, this shows the contents for the given article name $a, but gives only one comment when there should be multiple ones. What must I do to get all comments for the given article to show up? And shouldn't $row["comments"] be an array?

The end result I'm going for is displaying one article at a time, and all the comments assigned to the given article. Currently all I'm getting is one article and one (the first) comment, even though there are multiple rows in Table 2 with the same value for the a_id field

Upvotes: 1

Views: 1582

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

You have to put your fetch in a while loop:

$i=0;
while($row = mysql_fetch_assoc($result))
{
    if(!$i)
    {
        echo $row["title"]."<br/>".$row["content"]."<hr/>";
        $i++;
    }
    echo $row["comment"] . "<hr />";
}

Upvotes: 2

Related Questions