ster ster - 3 months ago 11
MySQL Question

PHP - Concatenate rows from MySQL

My table is, I know that the table terrible but is old:
http://sqlfiddle.com/#!9/f2680/1



+----+-----------------------+----------+------+
| ID | COUNTRY | QUANTITY | EACH |
+----+-----------------------+----------+------+
| 1 | U.S.A | 1 | 12 |
| 2 | U.K. | 2 | 3 |
| 3 | GERMANY | NULL | 3 |
| 4 | FRANCE;GERMANY; U.S.A | 0 | 7 |
| 5 | U.S.A;GERMANY | 3 | 8 |
| 6 | GERMANY;FRANCE;U.S.A | 1 | 10 |
+----+-----------------------+----------+------+







<?php
include 'bd_cnx.php';
$ret =[['Test','Valori livrare']];
$sql = "SELECT SUBSTRING_INDEX(COUNTRY, ';', 1) AS COUNTRY,
SUM(COALESCE(IF(QUANTITY = NULL OR QUANTITY = 0,1,QUANTITY),1) * EACHCOL) AS PRICE
FROM table1
GROUP BY COUNTRY";

$result = $conn->query($sql);
if ($result->num_rows > 0){
while($row = $result->fetch_assoc()){
$ret[] =[$row['COUNTRY'], floatval($row['PRICE'])];
}
}
echo json_encode($ret);
?>




My SQL code returns rows: FRANCE=3, GERMANY=6, GERMANY=12, U.S.A=12, U.S.A=7

I want to group the resulted values from the COUNTRY column (to make them unique) and then make SUM and finaly to result the next form: FRANCE=3; GERMANY=18; U.S.A=19. I will introduce these rows in google chart in the next form: ['country','value'].
Thank you!

Answer

Use this query with additional select ... group by on the first query:

select COUNTRY, sum(PRICE) from(
SELECT
  SUBSTRING_INDEX(COUNTRY, ';', 1) AS COUNTRY,
  SUM(COALESCE(IF(QUANTITY = NULL OR QUANTITY = 0,1,QUANTITY),1) * EACHCOL) AS PRICE
FROM table1
GROUP BY COUNTRY) as t
group by COUNTRY;
Comments