PHP MySQL Group By 使用给定的数组

Question

************************************************
* id  *  location                   *  status  *
************************************************
* 1   *  Shah Alam, Selangor        *  1       *
* 2   *  Seri Kembangan, Selangor   *  1       *
************************************************

大家好，我的数据库中有这样一张表。在位置字段中，有状态。如您所见，当前字段中是 Selangor。

如何按雪兰莪分组并计算？我不确定在 mysql 查询中使用 substr 是否更好。我确实有一个数组，我认为它也可以用于分组和计数。

$negeri = array('Selangor','Kedah','Johor','Negeri Sembilan','Perlis','Perak','Sarawak','Sabah','Kuala Lumpur','Kelantan','Terengganu','Melaka','Pahang','Pulau Pinang');

如果我要使用这个数组解决方案，我该怎么做？谢谢

Answer 1

试试这个sql查询，

select count(location) as locCount from state where location LIKE '%Selangor%' group by location;

在 PHP 中

$sql = "select count(location) as locCount from state where location LIKE '%".$negeri[0]."%' group by location;";

替换$negeri[0]用你在 php 中的变量名

编辑:在循环中创建 SQL 查询试试这个代码

根据@massquote 的代码，您可以像下面这样在循环中创建一个 sql 查询，

$negeri = array('Selangor','Kedah','Johor','Negeri Sembilan','Perlis','Perak','Sarawak','Sabah','Kuala Lumpur','Kelantan','Terengganu','Melaka','Pahang','Pulau Pinang');
$sql = "SELECT ";
$i=1;
$totCount = count($negeri);
foreach($negeri as $place){
  $sql .= "SUM(CASE WHEN location LIKE '%".$place."%' THEN 1 ELSE 0 END) as place".$i;
  if($i != $totCount) {
    $sql .=", ";
  }
  $i++;
}
$sql .= " FROM state;";
echo $sql;

echo $sql将打印以下 sql query , 你可以使用最终 $sql变量为 sql query .

SELECT 
    SUM(CASE WHEN location LIKE '%Selangor%' THEN 1 ELSE 0 END) as place1, 
    SUM(CASE WHEN location LIKE '%Kedah%' THEN 1 ELSE 0 END) as place2, 
    SUM(CASE WHEN location LIKE '%Johor%' THEN 1 ELSE 0 END) as place3, 
    SUM(CASE WHEN location LIKE '%Negeri Sembilan%' THEN 1 ELSE 0 END) as place4, 
    SUM(CASE WHEN location LIKE '%Perlis%' THEN 1 ELSE 0 END) as place5, 
    SUM(CASE WHEN location LIKE '%Perak%' THEN 1 ELSE 0 END) as place6, 
    SUM(CASE WHEN location LIKE '%Sarawak%' THEN 1 ELSE 0 END) as place7, 
    SUM(CASE WHEN location LIKE '%Sabah%' THEN 1 ELSE 0 END) as place8, 
    SUM(CASE WHEN location LIKE '%Kuala Lumpur%' THEN 1 ELSE 0 END) as place9, 
    SUM(CASE WHEN location LIKE '%Kelantan%' THEN 1 ELSE 0 END) as place10, 
    SUM(CASE WHEN location LIKE '%Terengganu%' THEN 1 ELSE 0 END) as place11, 
    SUM(CASE WHEN location LIKE '%Melaka%' THEN 1 ELSE 0 END) as place12, 
    SUM(CASE WHEN location LIKE '%Pahang%' THEN 1 ELSE 0 END) as place13, 
    SUM(CASE WHEN location LIKE '%Pulau Pinang%' THEN 1 ELSE 0 END) as place14 
FROM state;