how to fix this php json response?

Question

I get this result from my PHP:

{"risultato":"1","ris":[{"pda":"763,41","num1":"86,86","num2":"21,18","num3":"178603,201535"}]}

What I need to do if I want to return the result like this instead?

{"risultato":"1","ris":[{"pda":"763","num1":"86","num2":"21","num3":"178603"},{"pda":"41","num1":"86","num2":"18","num3":"201535"}]}

Here is my PHP code:

$stmtcarte = $connection->prepare("SELECT GROUP_CONCAT(concat.pda) as pda, GROUP_CONCAT(concat.num1) as num1,GROUP_CONCAT(concat.num2) as num2, GROUP_CONCAT(concat.num3) as num3  FROM (SELECT pda, num1, num2, num3,  FROM giocatori WHERE categoria=? ORDER BY RAND() LIMIT 2 ) concat");
    
    $categoria=$categoriaselezionata;
    $stmtcarte->bind_param("s",$categoria);
    $stmtcarte->execute();
    $risultatocarte = $stmtcarte->get_result();
    
    $result=array("risultato"=>"1", "ris"=>"");
    while($rispostacarte=$risultatocarte->fetch_assoc()){
    
    $result['ris']=array($rispostacarte);
    echo json_encode($result);
                  
    }
    $stmtcarte->close();

Answer 1

The group_concat is your problem. Remove it. All it's going to make you have to do is explode the delimited string to get it into arrays, and then have to reframe everything into a new array of a different structure.

Not sure what that subquery is for. I think a straight select is going to be all you need. You wouldn't need the subquery even if you did want to use group_concat, which I don't think you do.

This also doesn't take complicated array handling. All you have to do is push each row from your result set to the $ris array, because fetch_assoc() is already returning an (associative) array that has the keys and values.

$stmtcarte = $connection->prepare("
  SELECT pda, num1, num2, num3  
  FROM giocatori WHERE categoria=? 
  ORDER BY RAND() LIMIT 2
");
    
$categoria = $categoriaselezionata;
$stmtcarte->bind_param("s",$categoria);
$stmtcarte->execute();
$risultatocarte = $stmtcarte->get_result();
    
$result = array("risultato"=>"1", "ris"=>array());

while($rispostacarte = $risultatocarte->fetch_assoc()){    
  $result['ris'][] = $rispostacarte;                  
}

$stmtcarte->close();
echo json_encode($result);

If you are using php >= 5.4, you don't need to use array(). You can use [] instead. That would change this line:

$result = array("risultato"=>"1", "ris"=>array());

to this:

$result = ["risultato"=>"1", "ris"=>[]];