How to display multiple records with same id in html table in single row(in )?

Question

I have sql data result set having records as follows

 id |   name    |   hobbies
------------------------------------
1   |   RAVI    |   PLAYING CRICKET
------------------------------------
1   |   RAVI    |   LISTENING MUSIC
------------------------------------
2   |   REENA   |   BADMINTON
------------------------------------

I am displaying this data in view by using html table. Whereas my requirement is, I want to display as follows

id  |   name    |   hobbies
------------------------------------
1   |   RAVI    |   PLAYING CRICKET
    |           |   LISTENING MUSIC
------------------------------------
2   |   REENA   |   BADMINTON
------------------------------------

meaning I want to display records with id 1 into one <td> I am using php foreach loop to display result. How can I achieve this?

My current code is as follows and is results into same as my first table whereas I want my view as in the second table.

<table class="table table-striped">
    <tr >
        <th>ID</th>
        <th>Name</th>
        <th>Hobbies</th>
    </tr>
foreach($result as $row)
{
    echo "<tr>
         <td>".$row->id."</td>
         <td>". $row->name."</td>
         <td>". $row->hobbies."</td> 
         </tr>";
}
</table>

Show what you have so far - if the result is order by ID, then just test that the ID changes before making a new row/cell — mplungjan, Jan 15 '14 at 17:11

newfurniturey · Accepted Answer · 2014-01-16T13:25:51.027

A quick way to approach this would be to modify your MySQL query to use GROUP_CONCAT(hobbies) to group all of a user's hobbies together. The query would look something like:

SELECT
    id, name, GROUP_CONCAT(hobbies) AS hobbies
FROM
    your_table
GROUP BY
    id

This will group all of a user's hobbies in a comma-delimited list. To display it, you can use PHP's explode() and iterate over that:

foreach ($results as $row) {
    echo '<tr>';
        echo '<td>' . $row->id . '</td>';
        echo '<td>' . $row->name . '</td>';

        echo '<td>';

        $hobbies = explode(',', $row->hobbies);
        foreach ($hobbies as $hobby) {
            // output each hobby and decorate/separate them however you'd like
            echo $hobby . '<br />';
        }

        echo '</td>';

    echo '</tr>';
}

If you don't want the inner loop (or the ending <br /> that will pad the hobbies), you can use str_replace() instead:

echo str_replace(',', '<br />', $result['hobbies']);

**But using this display all hobbies list Need to display user wise Hobbies** — Reena Shirale, Jan 16 '14 at 12:45
@Reena My mistake; I wrote `ORDER BY` instead of `GROUP BY` in the MySQL query. Try it again now and if you're still having issues, please let me know and I will be glad to help out more =] — newfurniturey, Jan 16 '14 at 13:26

score 0 · Answer 2 · edited May 23 '17 at 11:44

0

This post may help you: Concatenate many rows into a single text string?

Just replace the , with </br>.

edited May 23 '17 at 11:44

Community

1
1

answered Jan 15 '14 at 17:19

MaxLascombe

96
3

It might be better to explain that to him.. perhaps you could write up a quick example of how you would use it? – msturdy Jan 15 '14 at 17:37

score 0 · Answer 3 · answered Jan 15 '14 at 17:29

in each iteration, you must save the last ID, in the next iteration you must check it whether its value has been changed or not. Something like this:

$res = $this->db->query('.....');
$last_ID = '';
foreach ($res->result() as $row) {
   if ($row->id != $last_ID) {
      if (strlen($last_ID)>0) echo '</td></tr>';
      echo '<tr>';
      echo '<td>$row->id</td>';
      echo '<td>$row->name</td>';
      echo '<td>$row->hobbies';
   } else {
      echo '<br />'.$row->hobbies;
   }
   $last_ID = $row->id;
}
if ($res->num_rows() > 0) echo '</td></tr>';

Using This, it display in diffrent `` i want to display in same ``. — Reena Shirale, Jan 17 '14 at 06:16

Gil Sousa · Answer 4 · 2014-01-15T17:47:44.633

-1

Supposing you have your row in $row var and the ID is $row["ID"]

?>

<td id="<php echo $row["id"]; ?>"> 
   <?php echo $row["id"]; ?>
</td>

<?php

edited Jan 15 '14 at 17:47

answered Jan 15 '14 at 17:13

Gil Sousa

769
5
12

How does that answer the question? He wants to show all records with the same ID in the same cell and you have syntax errors – mplungjan Jan 15 '14 at 17:14
You have just read the title of the question if you read the rest you will see the question "I want to display records with id 1 into one I am using php foreach loop to display result. How can I achieve this?" Thanks for telling me about the syntax errors they have been corrected – Gil Sousa Jan 15 '14 at 17:50
In a SINGLE TD - you are only showing the cell with the ID and is not looping over the hobbies belonging to the same person - see the other two solutions which do that – mplungjan Jan 15 '14 at 17:55
If you take the time to look at the current code that he as he already knows how to to the loop and doesn't need me to tell him how to do what he already knows. He only needs an example on how to display the id of a row as a id of a td element. – Gil Sousa Jan 15 '14 at 18:08
As you can see on his example he did not know how to fill a cell with a list of hobbies. Your example does not answer the question – mplungjan Jan 15 '14 at 19:42

How to display multiple records with same id in html table in single row(in )?

4 Answers4