SQL Join in CodeIgniter

I have one file, then for every file there are multiple comments about that file. I was able to successfully join the two tables, comments and files, however when I try to view the page where it displays the file and its comments, the page displays the name of file n times (n corresponds to the number of records of comments of that certain file).

How will I be able to display just once the file name as well as displaying the comments?

    <table width="40%" align="center">
        <?php foreach($rows as $file):?>
        <tr>
            <td width="70%" id="title"><?php echo $file->name; ?></td>

        </tr>
        <?php endforeach;?>
    </table>

    <table width="40%" align="center" frame="above" style="margin-top:10px; border-top-width:3px; border-top-color:#666666">
        <?php foreach($rows as $file):?>
        <tr>
            <td width="15%"></td>
            <td width="45%" id="name">FILESIZE:</td>
            <td width="40%" id="txt3"&开发者_JAVA技巧gt;<?php echo $file->size; ?></td>
        </tr>
        <tr>
            <td></td>
            <td id="name">DATE UPLOADED:</td>
            <td id="txt3"><?php echo $file->dateUploaded;?></td>
      </tr>
        <?php endforeach;?>
    </table>
    <!--COMMENTS -->

    <table align="center" frame="above" style="margin-top:10px; border-top-width:3px; border-top-color:#666666" width="40%">
        <tr>
            <td><h3>Comments</h3></td>
        </tr>
        <tr><?php foreach($rows as $comment):?>
            <td><?php echo $comment->comm;?></td>
        </tr><?php endforeach;?>


    </table>

You can use

 GROUP_CONCAT() 

in your join.

SELECT person.id AS id, name, GROUP_CONCAT(role.role SEPARATOR ',') AS roles
  FROM person
  LEFT OUTER JOIN person_role ON person.id=person_role.person_id
  LEFT OUTER JOIN role ON person_role.role_id=role.id
  GROUP BY id
  ORDER BY id;

is an example from a tutorial at http://dev-loki.blogspot.com/2008/01/aggregate-join-results-with-mysql-using.html

can't give much better without seeing your schema.

EDIT: trying anyway.

SELECT files.*, GROUP_CONCAT(comments.comment SEPARATOR ',') as comments_list
FROM files
LEFT JOIN comments on files.id = comments.id
GROUP BY id
ORDER BY id

Adding onto orbit, in codeigniter if your using activerecord it would be: (Sorry, I couldn't format code in a comment)

$this->db->select('person.id AS id, name, GROUP_CONCAT(role.role SEPARATOR ',') AS roles',false);

$this->db->from('person');

$this->db->join('person_role', 'person.id = person_role.person_id', 'left outer');

$this->db->join('role', 'person_role.role_id = role.id', 'left outer');

$this->db->group_by('id');

$this->db->order_by('id'):

$this->db->get();

well it looks like you're echoing the filename for each row that is returned

your rows are getting returned like

name | size | comment1
name | size | comment2

so just don't do foreach ($rows as $file) that will print out the filename each time, just change

<?php foreach($rows as $file):?>
        <tr>
            <td width="70%" id="title"><?php echo $file->name; ?></td>

        </tr>
        <?php endforeach;?>

to

<tr> 
            <td width="70%" id="title"><?php echo $rows[0]->name; ?></td>

        </tr>