Low Rider Low Rider - 7 months ago 29
PHP Question

MYSQL - Select MIN and MAX year and fill the gaps

I want to use Highcharts (bar) with my database, but I am struggling how to bring the JSON using my select.

I have three levels: Gold, Silver, Platinum and I need to get the minimum year and maximum year and cover the gaps if that level is not present in some year

I have this select:

select B.year, A.level, COUNT(B.id_level) as n_level from level AS A
inner join company_level AS B ON A.id_level = B.id_level
GROUP BY B.id_level, B.year
ORDER BY B.year


My result:

year level n_level
2010 Siver 1
2010 Platinum 2
2010 Gold 2
2011 Gold 1
2013 Gold 1
2014 Platinum 1
2015 Silver 1


In my result I don't have Silver and Platinum for 2011 and I would like to put the Silver and Platinum in the list but with number_level (n_level) with 0.

My PHP:

$rows = mysqli_query($this->conn, $query);
$bln = array();
$bln['name'] = 'Year';
$row['name'] = 'Level';

$year ="";
while ($r = mysqli_fetch_array($rows)) {
if($year !== $r['year']){
$bln['data'][] = $r['year'];
}
$row['data'][] = $r['n_level'];
$year = $r['year'];

}
$rslt = array();

array_push($rslt, $bln);
array_push($rslt, $row);

print json_encode($rslt, JSON_NUMERIC_CHECK);


My PHP Result:

[{"name":"Year","data":[2010,2011,2013,2014,2015]},{"name":"Level","data":[2,2,1,1,1,1,1]}]


But my goal is:

[{"name":"Year","data":[2010,2011,2013,2014,2015]},{"name":"Gold","data":[2,2,1,1,1]}, {"name":"Silver","data":[1,0,0,0,1]}, {"name":"Platinum","data":[2,2,1,1,1]}]


Thank you for any help.

Result after @Tin Tran answer:

$rows = mysqli_query($this->conn, $query);
$bln = array();
$bln['name'] = 'Year';
$row_gold['name'] = 'Gold';
$row_platinum['name'] = 'Platinum';
$row_silver['name'] = 'Silver';

while ($r = mysqli_fetch_array($rows)) {
$bln['data'][] = $r['year'];
$row_gold['data'][] = $r['gold']; 'gold';
$row_platinum['data'][] = $r['platinum'];
$row_silver['data'][] = $r['silver']; '';

}
$rslt = array();

array_push($rslt, $bln);
array_push($rslt, $row_gold);
array_push($rslt, $row_platinum);
array_push($rslt, $row_silver);
print json_encode($rslt, JSON_NUMERIC_CHECK);


This code will works really well with Highcharts. Thank you again @Tin Tran.

Answer

I am not familiar php, but I think based on your goal of the JSON, this query might give you result you're after.

SELECT B.year,
       SUM(A.level = 'Gold') as Gold,
       SUM(A.level = 'Silver') as Silver,
       SUM(A.level = 'Platinum') as Platinum
FROM level AS A
INNER JOIN company_level AS B ON A.id_level = B.id_level
GROUP BY B.year
ORDER BY B.year;

result:

year    Gold    Silver  Platinum
2010    2       1       2
2011    1       0       0
2013    1       0       0
2014    0       0       1
2015    0       1       0

sqlfiddle -> http://sqlfiddle.com/#!9/59893/3

Comments