Is it possible to optimize my code for a single query?

Question

I have the following problem: I’m assembling a Highcharts chart with queries php and I end up having a while inside another because the result of the date I put in the next query, has to return in a single query?

<?php 
include 'config.php';
$query="SELECT distinct data FROM lista";
$output = sqlsrv($query, $conn);
while($row = sql_srv_fetch_array($output)){
   $query2="SELECT MATERIAL FROM lista WHERE data='".$row['data']."'";
   $output2 = sqlsrv($query2, $conn);
   while($row2 = sql_srv_fetch_array($output2)){
      echo $row['MATERIAL'];  
 }

}
?>

I want to return a query only that returns the date column of the form [data1,data2,data3,....,dataN] and material column of the shape [material1,material2,material3,...,materialN]. How to ride that one select?

Answer 1

Test the following:

At the end the two arrays will be with the values in the format you need.

<?php 
    include 'config.php';
    $query = "SELECT data, material FROM lista GROUP BY data, material ORDER BY data ASC";
    $output = sqlsrv($query, $conn);
    while($row = sql_srv_fetch_array($output)){
        $datas[] = $row['data'];
        $materiais[] = $row['material'];
        // o seguinte terá um array com todos materiais referentes a xx/xx/xxxx datas
        $array[$row['data']][] = $row['material'];
        // Assim terás 
        //$array['dataX'] = ['material1', 'material2'];
        //$array['dataY'] = ['material3', 'material4'];
    }
?>