Ranjan Tanwar Ranjan Tanwar - 3 years ago 77
CSS Question

how to get data in single row in <td>, multiple entry in database with same id

table structure is like as:

medicine table component table
id | name id | medicine_id | component_name
---------------- -----------------------------------
1 | a 1 | 1 | aaaa
2 | b 2 | 1 | bbbb
3 | b 3 | 1 | cccc
4 | 2 | abab
5 | 2 | baba
6 | 3 | zzzz


My query is

$query = "SELECT med_name, component_name FROM medicine left JOIN component ON medicine.id=component.medicine_id order by medicine.med_name";


PHP Code

$result = $mysqli -> query($query);

while ($row = $result -> fetch_array()) {

echo '<tr><td>' . $row['med_name'] . '</td><td>' . $row['component_name'] . '</td><td>' . $row['component_name'] . '</td><td>' . $row['component_name'] . '</td><td>' . $row['component_name'] . '</td><td><a href="edit.php?id=' . $row['id'] . '"><i class="fa fa-edit text-success text-active"></i> Edit</a></td></tr>';

}


with this code my html output is like:

medicine | component1 | component2 | component3 | component4
-------------------------------------------------------------
a | aaaa | aaaa | aaaa | aaaa
a | bbbb | bbbb | bbbb | bbbb
a | cccc | cccc | cccc | cccc
b | abab | abab | abab | abab
b | baba | baba | baba | baba
c | zzzz | zzzz | zzzz | zzzz


but I want html output as:

medicine | component1 | component2 | component3 | component4
--------------------------------------------------------------
a | aaaa | bbbb | cccc |
b | abab | baba | |
c | zzzz | | |

Answer Source

What you're trying to do is just a bit more complicated that what you're doing. Your query seems fine. But after you fetch the results there's a little more work to do to determine how the table should be produced. I would start by fetching the rows into a multidimensional array, where multiple component rows are stored under each medicine name, like this:

while ($row = $result->fetch_array()) {
    $medicines[$row['med_name']][] = $row;
}

Then you'll need to find the maximum number of components for any of the medicines you fetched, so you can know how many columns your table should have.

$component_columns = max(array_map('count', $medicines));

Then you can iterate your array of medicines and output each one as a row with its components as additional columns.

foreach ($medicines as $medicine => $components) {

    // start the row and echo the medicine name
    echo "<tr><td>$medicine</td>";

    // echo the components, keeping track of how many you've done so far
    $i = 0;
    foreach ($components as $component) {
        echo "<td>$component[component_name]</td>";
        $i++;
    }

    // then echo empty cells to fill the rest of the row
    for (; $i < $component_columns; $i++) {
        echo "<td>&nbsp;</td>";
    }
    echo '<tr>';

}

By the way, it looks like you're using the medicine id in a link in your HTML, but if you want to add that, you'll need to be sure to include that in the selected columns in your query as well.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download