如果从另一个表加入时包含重复项，则在记录中显示一个名称

Question

I have two tables. One called 'markers', containing ID(pk), name, address and another table called 'markers_date' that contains 'markers_id, date, date2, time.

I have no issue at all echoing it out as json. However, I want to display the name and address only one time as well as displaying the corresponding dates and times for the name, which there are multiple dates and times.

PHP

---

$markers = array();
$qry = "SELECT markers.name, markers.address, markers_date.date, markers_date.date2, markers_date.time FROM markers, markers_date WHERE markers_date.markers_id = markers.id ORDER BY markers.name";

$result = mysqli_query($dbConnect, $qry);

while($row = mysqli_fetch_object($result)) {

    $markers[] = $row;

}

echo '{"markers":'.json_encode($markers).'}'; here

The output I receive is as follows:

          Atlantic Yacht Club     5 New St          3/31     5/30     6:30-9:00pm
          Atlantic Yacht Club     5 New St          4/30     6/16     4:30-7:00pm
          VFW                  145 Greenwich St     4/22              6:30-9:00pm
          VFW                  145 Greenwich St     4/27     4/30     6:30-9:00pm
          VFW                  145 Greenwich St     7/22              6:30-9:00pm

What I really want from it is:

          Atlantic Yacht Club     5 New St          3/31     5/30     6:30-9:00pm
                                                    4/30     6/16     4:30-7:00pm

          VFW                  145 Greenwich St     4/22              6:30-9:00pm
                                                    4/27     4/30     6:30-9:00pm
                                                    7/22              6:30-9:00pm

My question is, would this be controlled in the SQL statement or somehow controlled from the PHP array?

Thanks

Answer 1

One approach would be to iterate over your ordered result set in PHP, and then selectively blank out the marker name and address whenever it is not the first time we have seen that combination of name and address.

$curr_name = '';
$curr_address = '';
while ($row = mysqli_fetch_object($result)) {
    $name = $row->name;
    $address = $row->address;
    // if we have seen this name/address pair already, blank it out
    if ($name == $curr_name && $address == $curr_address) {
        $row->name = '';
        $row->address = '';
    }
    // otherwise, don't blank it out but remember this name/address pair
    else {
        $curr_name = $name;
        $curr_address = $address;
    }
    echo '{"markers":'.json_encode($row).'}';
}