PHP MySQL Group By Using given array

Question

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

Hello guys, i have this kind of table in my database. Which in the location field, there is state. As you see, in the current field is Selangor.

How do i group it by Selangor and count it? I'm not sure whether using substr in mysql query is better or not. I do have an array which i think also possible to use to group and count.

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

If i am about to use this array solution, how do i do it? Thanks

You'd be better served to just have two columns for location: city and state. Then you can group by state. — Mark Miller
– Mark Miller, Commented Aug 11, 2014 at 4:23
@MarkM .. the previous programmer make the field like that. The data now contain more than 100 thousands.. i have to find a simple solution for this. — Muhamad Azam
– Muhamad Azam, Commented Aug 11, 2014 at 4:41

CJ Ramki · Accepted Answer · 2014-08-11 07:39:54Z

2

try this sql query,

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

in PHP

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

Replace $negeri[0] with your variable name in php

EDIT: TO CREATE SQL QUERY IN LOOP TRY THIS CODE

as per @massquote's code, you can create a sql query in loop like below,

$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 will print the below sql query, you can use final $sql variable as 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;

edited Aug 11, 2014 at 7:39

answered Aug 11, 2014 at 4:53

CJ Ramki

2,6323 gold badges27 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Muhamad Azam Over a year ago

@cj-ramkl How do i group the location and show the sum? Do i need to group by the array given?

CJ Ramki Over a year ago

it will return each location's sum in separate column like place1, place2,... placex. So, number of columns is depends upon your array length. In this example I used your $negeri. It contains 14 locations in array. So, It is returning place1 to place14 as a separate column.

massquote · Accepted Answer · 2014-08-11 04:54:03Z

1

You can do something like this.

SELECT 'Selangor' as state,
    SUM(CASE WHEN location like '%, Selangor%' then 1 Else 0 end) AS cnt from yourtable 
UNION ALL 
SELECT 'Kedah' as state,
    SUM(CASE WHEN location like '%, Kedah%' then 1 Else 0 end) AS cnt from yourtable;

Just generate the sql using loop in php then execute the script.

answered Aug 11, 2014 at 4:54

massquote

4,6271 gold badge16 silver badges16 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Group By Using given array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related