PHP - Concatenate rows from MySQL

Question

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!

So, what's the question?

M. Eriksson
– M. Eriksson

2016-08-29 19:54:15 +00:00
Commented Aug 29, 2016 at 19:54 — M. Eriksson
– M. Eriksson, Commented Aug 29, 2016 at 19:54
stackoverflow.com/a/32620163

Drew
– Drew

2016-08-29 19:58:10 +00:00
Commented Aug 29, 2016 at 19:58 — Drew
– Drew, Commented Aug 29, 2016 at 19:58

Andrej · Accepted Answer · 2016-08-29 19:54:28Z

1

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;

answered Aug 29, 2016 at 19:54

Andrej

7,5041 gold badge22 silver badges22 bronze badges

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

1 Comment

ster Over a year ago

@ Andrej Ludinovsckov For those who will have my problem, and public response to this post stackoverflow.com/questions/39212444/… Thank you !

Collectives™ on Stack Overflow

PHP - Concatenate rows from MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related