I have a table with this data

   ID | name|    num1
    --------------------------
    1  | Marco  | 700
    2  | Alessio| 750
    3  | Luca   | 900
    4  | Mario  | 1200
    5  | Manuel | 700
    6  | Edo    | 1500

Aim is to get an json results that looks like this:

{"result":"1", "data":[{"name":"Marco","num1":"700"},{"name":"Alessio","num1":"750"},{"name":"Luca","num1":"900"},{"name":"Mario","num1":"1200"},{"name":"Manuel","num1":"700"},{"name":"Edo","num1":"1500"}],"total":"5.750"}

but the result is this

{"result":"1", "data":[{"name":"Marco","num1":"700"},{"name":"Alessio","num1":"750"},{"name":"Luca","num1":"900"},{"name":"Mario","num1":"1200"},{"name":"Manuel","num1":"700"},{"name":"Edo","num1":"1500"}],"total":"700"}

the values ​​are not summed and returns only the first result.

does anyone have the solution to my problem? thank you in advance for your help

php:

$stmt = $connection->prepare("SELECT GROUP_CONCAT(concat.name) as name, GROUP_CONCAT(concat.num1) as num1 FROM (SELECT name, num1 FROM giocatori WHERE categoria=? ORDER BY RAND() LIMIT 6 )concat");

$stmt->bind_param("s",$categoria);
$stmt->execute();
$risultato = $stmt->get_result();

$result=array("result"=>"1", "data"=>"" ,"total"=>"");

while($rispostacarte=$risultato->fetch_assoc()){
$valoretotale=array($rispostacarte['num1']);
$result['total'] = array_sum($valoretotale);
$result['data']=$rispostacarte;
$ris = $result["data"];

$tempRis = [];

foreach ($ris as $key => $value) {
$explodedArray = explode(",", $value);
$length = count($explodedArray);
for ($i=0; $i < $length ; $i  ) { 
$tempRis[$i][$key] = $explodedArray[$i];
}
}
$result["data"] = $tempRis;
echo json_encode($result);  
}

CodePudding user response：

When you do your query, your resulting query will look something like this:

[
  'name'=>'Marco,Luca,Mario,Alessio,Manuel,Edo', 
  'num1'=>'700,900,1200,750,700,1500'
]

These are strings. In your code you're doing:

$valoretotale=array($rispostacarte['num1']);
$result['total'] = array_sum($valoretotale);

essentially you're taking a comma separate array and just putting it into an array then using array_sum where it's the only value, so it would turn the string into an int and stop on the comma....

what you should be doing though is turning the comma separated string into an array like this:

$valoretotale = explode(',',$rispostacarte['num1']);

Then you'll get the desired total.

Additionally if you want to format the output, using a separator for the thousands place, you can use number_format:

result['total'] = number_format(array_sum($valoretotale),0,',','.');

If it were up to me though, I'd rewrite the query to avoid concats and rewrite the code to something much cleaner like this:

$stmt = $connection->prepare("SELECT a.name as name,
a.num1 as num1
FROM (
SELECT name, num1 FROM giocatori WHERE categoria=? ORDER BY RAND() LIMIT 6
) a");

$stmt->bind_param("s",$categoria);
$stmt->execute();
$risultato = $stmt->get_result();

$result=array('result'=>'1', 'data'=>[] ,'total'=>0);

while($rispostacarte = $risultato->fetch_assoc()) {
    $result['data'][] = $rispostacarte;
    $result['total']  = $rispostacarte['num1'];
}
$result['total'] = number_format($result['total'],0,',','.');
echo json_encode($result);

CodePudding user response：

Sorry, but your approach is wrong. You should not use GROUP_CONCAT in this case. You just need simple select and loop

<?php

$query = "SELECT *  FROM giocatori";

// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute([]);

$risultato = [
    'risultato' => 1,
    'data'=>[],
    'total' => 0
];

// loop fetched rows and calculate total
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $risultato['data'][] = $row;
    $risultato['total']  = $row['num1'];
}

print_r(json_encode($risultato, JSON_PRETTY_PRINT));

Test PHP SQL online

Here you can found example using mysqli