如何从mysql计数多个值作为html和php中的一列

Question

$query="SELECT data_home.name_home, flower.flower FROM
data_home INNER JOIN (flower INNER JOIN home_flower ON flower.id_flower=home_flower.id_flower)
ON data_home.id_home = home_flower.id_home";

$data=mysql_query($query);
            $no=0;

            //output
            while ($row=mysql_fetch_array($data))
            {
                $no++;
                if (empty($row['foto_home']))
                {
                    $gambar = "default2.jpg";
                }
                else
                {
                    $gambar = $row['foto_home'];
                }
                echo "<tr class='odd gradeA'>
                        <td width='5%'>".$no."</td>
                        <td width='20%'>".$row['name_home']."</td>
                        <td width='25%'>".$row['home_adress']."</td>
                        <td width='15%'>".$row['flower']."</td>
                        <td width='15%'><img src='uploads/".$gambar."' width='125px' height='125px'></img></td>
                        <td width='20'>
                            <center> 
                                <a href='?restore=".$row['id_home']."' onclick='return confirmRestore()'>
                                    <button type='button' class='btn btn-warning btn-circle' title='Restore'><i class='icon-refresh'></i><font size='1px'> Restore</font></button>
                                </a> 
                                <a href='?delete=".$row['id_home']."' onclick='return confirmSubmit()'>
                                    <button type='button' class='btn btn-danger btn-circle' title='Hapus'><i class='icon-trash'></i><font size='1px'> Delete</button></font>
                                </a>
                            </center>
                        </td>
                    </tr>
                ";
            }
            ?>

i have code like this for my program and The output I want is as follows:

no             home            flower
1             type 41         tulip, rose, lily

but i dont knnow why when i running the program the out come be like this:

no            home            flower
1             type 41         tulip
2             type 41         rose
3             type 41         lily 

i am really new to php and html so i dont have idea how to fix this.my question how to arrange it so "flower" with same "home" just count 1 and just seperate flower in one column with "," ?.thnx and sory for my bad english and for mysql code iknow ppl today using mysqli but this is the old program.

Answer 1

Your expected output in SQL could be achieved by this query, which uses GROUP_CONCAT function of MYSQL and gives you comma separated flowers, aggregated by home

But if you current query you haven't mentioned about column no.

SELECT dh.name_home, group_concat(f.flower) as flower
     FROM
data_home dh 
       INNER JOIN 
     home_flower hf
ON dh.id_home = hf.id_home
INNER JOIN 
       flower f
  ON f.id_flower=hf.id_flower
group by dh.name_home