如何使用MySQL PHP筛选记录

Question

我正在使用mysql和Php，我得到的数据格式如下：

Array
(
    [country] => India
    [total] => 23
)
Array
(
    [country] => USA
    [total] => 1
)
Array
(
    [country] => Japan
    [total] => 1
)

我希望如果有任何国家的完全匹配，它们应该以以下格式在同一个数组中。

Array
(
    [country] => India
    [total] => 23
)
Array
(
    [country] => USA,Japan
    [total] => 1
)

这是我的MySQL查询

SELECT country,COUNT(*) as total FROM doctors GROUP BY country ORDER BY COUNT(*) DESC

这是我的代码

<?php foreach ($data as $total) { 
echo "<pre>";print_R($total);
?>
<p><?php echo $total['country']; ?><?php echo $total['total']; ?></p>
 <?php } ?>

- amit

---

遍历数组并收集相同“total”的国家有什么问题？ - u_mulder

Answer 1

$countriesByTotal = [];

foreach ($data as $row) {
    $countriesByTotal[$row['total']][] = $row['country'];
}

foreach ($countriesByTotal as $total => $countries) {
    $countriesByTotal[$total] = [
        'country' => implode($countries, ','),
        'total'   => $total
    ];
}

$countriesByTotal = array_values($countriesByTotal); // optional

print_r($countriesByTotal);

结果：

Array
(
    [0] => Array
        (
            [country] => India
            [total] => 23
        )
    [1] => Array
        (
            [country] => USA,Japan
            [total] => 1
        )
)

rextester演示

另一种方法就是使用GROUP_CONCAT()修改您的SQL查询：

SELECT total, GROUP_CONCAT(country) as country
FROM (SELECT country,COUNT(*) as total FROM doctors GROUP BY country) sub
GROUP BY total
ORDER BY total DESC

- Paul Spiegel

Answer 2

array_walk() 可以用于连接相同总数的国家。

$data = [];
array_walk($array, function ($value) use (&$data) {
    $data[$value['total']] = isset($data[$value['total']])
        ? ['country' => "{$data[$value['total']]['country']},{$value['country']}", 'total' => $value['total']]
        : $value;
});

print '<pre>';
print_r($data);

- Janie