PHP结果与MYSQL有很多对很多表和GROUP By

Question

I am putting together a list of service providers. I would like them to be listed like:

Plumber: Joe's Plumbing Mike's Plumbing

Electrician: Scott electric John electric

etc...

How can I pull and group this information from my query. Also when I try to call pt.name it says it is NULL any ideas why?

Thanks

$qry  = "SELECT pt.name, p.name, p.phone
FROM provider_type AS pt
LEFT JOIN providers_provider_type AS ppt ON pt.id = ppt.provider_type_id
LEFT JOIN providers AS p ON ppt.providers_id = p.id
WHERE installation_id = $installation_id
GROUP BY ppt.provider_type_id";
//$qry = "SELECT * FROM providers WHERE installation_id = $installation_id";    
$res = mysqli_query($mysqli, $qry) or die('-1'.mysqli_error($mysqli));


    $categories = array();
    while ($row_rsCategories = mysqli_fetch_assoc($res)) { 


/////////////////////////////////////////////// 

        $categories[] = array(
            'id' => $row_rsCategories['id'],
            'provider_type' => $row_rsCategories['pt.name'],
            'provider_name' => $row_rsCategories['name']
        );

    }


?>


<div class="container">
    <div class="content">
        <?php if ($msgBox) { echo $msgBox; } ?>
        <div class="row">  
<?php var_dump($categories); ?>

<?php if (empty($categories)) { ?>
    <div class = "big-icon">
        <i class="fa fa-meh-o fa-5x"></i>
    </div>
    <p><center>Sorry! It looks like we don't have a Scout in this area. <br />
        Please check back in as we continue to add new Scouts to the network all the time.</center></p>

<?php  } else {
?>

<table class="table table-striped">
<tr>
    <th>Type</th>
    <th>Name</th>
    <th>Phone</th>
    <th>More Info</th>
</tr>

<?php foreach ($categories as $category) { ?>
<tr>
    <td><?php echo $category['provider_type']; ?>  </td>
    <td><?php echo $category['provider_name']; ?> </td>
    <td> </td>
    <td> </td>
</tr>   

    <?php  } 
        }
        ?>  
</table>    

Answer 1

Your query retuns only one row per provider type, while you want to list all providers (more than one from each type).

One option is to use GROUP_CONCAT, which will concatenate all the providers for each type into one comma separated string:

SELECT pt.name AS type, GROUP_CONCAT(p.name) AS providers
FROM provider_type AS pt
LEFT JOIN providers_provider_type AS ppt ON pt.id = ppt.provider_type_id
LEFT JOIN providers AS p ON ppt.providers_id = p.id
WHERE installation_id = $installation_id
GROUP BY ppt.provider_type_id;

This will give you a results set that looks like this:

type        | providers                       |
------------+---------------------------------+
plumber     | Joe's Plumbing,Mike's Plumbing  |
electrician | Scott electric,John electric    |

In your case, I think there is a better option - since you are retrieving all the results into php anyway, you may want to get all providers, one by one, and then do the grouping in your code, and show it any way you like.

The query would be the same as yours, without the grouping:

SELECT pt.name AS type, p.name, p.phone
FROM provider_type AS pt
LEFT JOIN providers_provider_type AS ppt ON pt.id = ppt.provider_type_id
LEFT JOIN providers AS p ON ppt.providers_id = p.id
WHERE installation_id = $installation_id;

And the results set will simply be a list of providers, each one with his name and phone number.

type        | name            | phone         |
------------+-----------------+---------------+
plumber     | Joe's Plumbing  | 123...        |
plumber     | Mike's Plumbing | 123...        |
electrician | Scott electric  | 123...        |
electrician | John electric   | 123...        |

As for NULL in pt.name - In the result set from MySQL you don't get pt.name and p.nameas the column names. You get name and name1 (both columns have the same name, no matter which table they originally come from, so the DB just adds the 1 to have unique names).

You should give unique names to your colums in the query, e.g. pt.name AS type and then use those names to read the data, e.g. 'provider_type' => $row_rsCategories['type']