displaying php mysql query result with one to many relationship

Question

Heres A Sample Table:

info table
info_id     name  
1           john  
2           peter
------------------------  
details table  
details_id      log                               date  
1            test log for john                  2013-08-01  
1            another log for john               2013-08-02  
2            test log for peter                 2013-08-02

Here's my Sample Query:

SELECT info.info_id, info.name, details.details_no, details.log, details.date 
FROM info JOIN details ON details.details_id = info.info_id 
GROUP BY info.info_id  

And Here's display i want to achieve:

john  
1     test log for john            2013-08-01  
1     another test log for john    2013-08-02  

peter  
2     test log for peter           213-08-02  

I have tried using foreach loop and then execute another foreach loop inside the first loop.

Please Help Guys

Answer 1

Try making your results into a multidimensional array like below

Note: I am assuming that details.details_no is a primary key for the details table and you want results similar to

john  
1     test log for john            2013-08-01  
2     another test log for john    2013-08-02  

peter  
3     test log for peter           213-08-02

Which you can retrieve with the following

...
$qry = "
    SELECT
        info.info_id,
        info.name,
        details.details_no,
        details.log,
        details.date 
    FROM
        info
        JOIN details ON (details.details_id = info.info_id)
";
$result = mysqli_query($your_db_link,$qry)
$data = array();
while($row = mysqli_fetch_array($result)){
    $data[$row["info_id"]]["name"] = $row["name"];
    $data[$row["info_id"]]["logs"][$row["details_no"]] = array(
        "log"=>$row["log"],
        "date"=>$row["date"],
    );
}

Would result in an array like:

$data = array(
    "1" => array(
        "name" => "john",
        "logs" => array(
            "1" => array(
                "log" => "test log for john",
                "date" => "2013-08-01",
            ),
            "2" => array(
                "log" => "another test log for john",
                "date" => "2013-08-02",
            ),
        ),
    ),
    "2" => array(
        "name" => "peter",
        "logs" => array(
            "3" => array(
                "log" => "test log for peter",
                "date" => "2013-08-02",
            ),
        ),
    ),
);

Then you could display like:

echo "<table>";
foreach($data as $value){
    echo "<tr><td colspan='3'>".$value["name"]."</td></tr>";
    foreach($value["logs"] as $subkey => $subvalue){
        echo "<tr>";
        echo "<td>".$subkey."</td>";
        echo "<td>".$subvalue["log"]."</td>";
        echo "<td>".$subvalue["date"]."</td>";
        echo "</tr>";
    }
}
echo "</table>";

Which would result similar to:

<table>
    <tr>
        <td colspan='3'>john</td>
    </tr>
    <tr>
        <td>1</td>
        <td>test log for john</td>
        <td>2013-08-01</td>
    </tr>
    <tr>
        <td>2</td>
        <td>another test log for john</td>
        <td>2013-08-02</td>
    </tr>
    <tr>
        <td colspan='3'>peter</td>
    </tr>
    <tr>
        <td>3</td>
        <td>test log for peter</td>
        <td>2013-08-02</td>
    </tr>
</table>