从2个单独的mysql表中提取数据，在同一个表行上回显匹配记录

Question

I have two MySQL tables, one that stores box numbers and one that saves the contents of each box. Whereas before I used to save this all on one table, for example column 1 = box number, column 2 = box contents in comma separated format, I now have table 'move_items' column 1 = box number, table 'move_contents' column 1 = box number, table 'move_contents' column 2 = box contents.

The problem I'm now finding is that although I can echo the results in a <table>, I'm getting separate rows for the same box that contains multiple contents:

What I want instead is if box number 2 contains 2 items, I want this to show on only one row with both items under the 'contents' column:

My currently query is:

$sql = "SELECT move_items.*, move_contents.* FROM move_items LEFT JOIN move_contents ON move_items.boxnumber = move_contents.box ORDER BY move_items.date DESC";  
$result = mysqli_query($con, $sql);
while($row = mysqli_fetch_array($result)) {
    echo '<tr>
        <td>'.$row["boxnumber"].'</td>
        <td>'.$row["boxcontents"].'</td>
    </tr>';
}

I am at a complete loss now. Before I go back to the old inefficient way of storing all the box contents in one comma separated cell, can anyone help me out? I've tried many things such as GROUP BY but my inexperience is showing.

Answer 1

You are looking for group by and group_concat:

SELECT mi.*, group_concat(mc.contents)
FROM move_items mi LEFT JOIN
     move_contents mc
     ON mi.boxnumber = mc.box
GROPU BY mi.boxnumber
ORDER BY mi.date DESC;