Incorrect result in Google column charts

Question

What I am trying to do with the code below is plot a column chart, showing 2 types of amount(Income and Reversal) in any dates, however the result of it is incorrect and I can't find a better way to correct it.

https://i.sstatic.net/o0Kpx.jpg <- what I need.

https://i.sstatic.net/84wEV.jpg <- what I have.

Anybody who can help me acheive this?

<?Php
require "config.php";// Database connection
$stmt = $connection->query("SELECT date,trans_type,Sum(trans_amount) FROM prefix_transactions GROUP by date,trans_type"))

$php_data_array = Array();
 
while ($row = $stmt->fetch_row()) {
   $php_data_array[] = $row; 
   } 
   
echo json_encode($php_data_array); 

echo "<script>
        var my_2d = ".json_encode($php_data_array)."
</script>";
?>

<div id="chart_div"></div>

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
    google.charts.load('current', {packages: ['corechart', 'bar']});
    google.charts.setOnLoadCallback(drawChart);
      
    function drawChart() {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Date');
        data.addColumn('number', 'Type');
        data.addColumn('number', 'Amount');
        for (i = 0; i < my_2d.length; i++)
            data.addRow([my_2d[i][0], parseInt(my_2d[i][2]),parseInt(my_2d[i][2])]);
        var options = {
            hAxis: {title: 'Month',  titleTextStyle: {color: '#333'}},
            vAxis: {minValue: 0},
            width:500,
            height:400
        };

        var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
        chart.draw(data, options);
    }   
</script>

Answer 1

Starting from your sql, you actually need to perform conditional summing. (DB Fiddle Demo)

SELECT 
    DATE(trans_time) AS Date,
    SUM(IF(trans_type = 'Income', trans_amount, 0)) AS Income,
    SUM(IF(trans_type = 'Reversal', trans_amount, 0)) AS Reversal
FROM prefix_transactions
WHERE ref_number = 'CompanyXYZ'
GROUP BY Date
ORDER BY Date;

Result Set (from screenshot of sample data):

| Date       | Income | Reversal |
| ---------- | ------ | -------- |
| 2021-07-24 | 350.00 | 50.00    |
| 2021-07-25 | 200.00 | 50.00    |

---

PHP Code:

$ref_number = 'CompanyXYZ';
$sql = "SELECT DATE(trans_time) AS Date,
               SUM(IF(trans_type = 'Income', trans_amount, 0)) AS Income,
               SUM(IF(trans_type = 'Reversal', trans_amount, 0)) AS Reversal
        FROM prefix_transactions
        WHERE ref_number = ?
        GROUP BY Date
        ORDER BY Date"
$stmt = $conn->prepare($sql);
$stmt->bind_param('s', $ref_number);
$stmt->execute();

$result[] = ['Date', 'Income', 'Reversal'];
foreach ($stmt->get_result() as $row) {
    $result[] = [$row['Date'], $row['Income'], $row['Reversal']];
}

// then in your javascript...

new google.visualization.arrayToDataTable(<?php echo json_encode($result); ?>),

Here's a runnable javascript snippet to prove effectiveness:

google.load('visualization', '1', {
    packages: ['corechart', 'bar']
});
google.setOnLoadCallback(draw);

function draw() {
    var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
    chart.draw(
        new google.visualization.arrayToDataTable(
            [
              ['Date', 'Income', 'Reversal'],
              ['2021-07-24', 350.00, 50.00],
              ['2021-07-25', 200.00, 50.00]
            ]
        ),
        {
            hAxis: {title: 'Day',  titleTextStyle: {color: '#333'}},
            vAxis: {minValue: 0},
            width:500,
            height:400
        }
    );
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>

p.s. Having date and datetime in each row of your db table seems entirely redundant. I'd remove the date column from the schema if this was my application.