How to push custom key and value into an array from an associative array in PHP-CodePudding

Take a look at this loop

$dataPoints1 = array();
$dataPoints2 = array();

$sql = "SELECT DATE_FORMAT(date,'%M') as 'date', income, expense FROM `balance` group by DATE_FORMAT(date,'%m');";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
          array_push($dataPoints1, $row);  **desire one - to push into $dataPoints1 array from $row**
          array_push($dataPoints2, $row);  **desire two - to push into $dataPoints2 array from $row**
    }
}

My table,

id	date	income	expense
1	2022-05-01 00:00:00	20	10
2	2022-06-01 00:00:00	40	30
3	2022-07-01 00:00:00	60	50

In desire one, I want to push custom key and value with specific columns from an associative array ($row) like this,

$row
↓
{"label":"date","y":"income"}

In also desire two,

$row
↓
{"label":"date","y":"expense"}

Final output is like below,

dataPoints1
↓
[{"label":"May","y":"20"},{"label":"June","y":"40"},{"label":"July","y":"60"}]

dataPoints2
↓
[{"label":"May","y":"10"},{"label":"June","y":"30"},{"label":"July","y":"50"}]

I will echo with json_encode,

echo json_encode($dataPoints1);

echo json_encode($dataPoints2);

CodePudding user response：

Better variable naming would help to make your code more clear.

Mysqli resultset objects can be iterated with foreach() as if they are arrays of associative arrays.

$income = [];
$expenses = [];
foreach ($conn->query($sql) as $row) {
    $income[] = ['label' => $row['date'], 'y' => $row['income']];
    $expenses[] = ['label' => $row['date'], 'y' => $row['expense']];
}