Using Google Chart with PHP MYSQL

Question

I have to show how many accounts and balance 1 user has. I have to show the data on a table and a pie chart. I have created a Pie Chart from Google Chart. However, I got stuck on inserting the data for the pie chart using PHP. Please help! :(

 <tr>
                        <th>Account Type</th>
                        <th>Account Number</th>
                        <th>Account Balance</th>
                    </tr>
                </thead>
                    <?php
                    $query = "SELECT * FROM account WHERE user_id='$user_id'";
                    $result = mysqli_query($link, $query) or die(mysqli_error($link));
                    while ($row = mysqli_fetch_array($result)) {
                        $acc_type = $row ['account_type'];
                        $acc_num = $row ['account_number'];  
                        $acc_bal = $row ['account_balance']; 

                        ?>
                    <tbody>
                        <tr>
                            <td><?php echo $acc_type ?></td>
                            <td><?php echo $acc_num ?></td>
                            <td>S$ <?php echo $acc_bal ?></td>
                        <?php
                    }


                    ?> 
                        </tr>
                </tbody>  
                <tr class="alt">
                    <?php 
                    $query = "SELECT SUM(account_balance) AS account_total FROM account WHERE user_id='$user_id'";
                    $result = mysqli_query($link, $query) or die(mysqli_error($link));
                    while ($row = mysqli_fetch_array($result)){
                        $acc_total = $row ['account_total'];
                    ?>
                    <td colspan="2" align="right"><b>Total: </b></td>
                    <td align="right"><b>S$ <?php echo $acc_total ?> </b></td>
                    <?php
                    }
                    ?>

                </tr>  
            </table>

                </center>

    // creating pie Chart
                    <!--Load the AJAX API-->
        <script type="text/javascript" src="https://www.google.com/jsapi"></script>
        <script type="text/javascript">

          // Load the Visualization API and the piechart package.
          google.load('visualization', '1.0', {'packages':['corechart']});

          // Set a callback to run when the Google Visualization API is loaded.
          google.setOnLoadCallback(drawChart);

          // Callback that creates and populates a data table,
          // instantiates the pie chart, passes in the data and
          // draws it.
          function drawChart() {

            // Create the data table. (and I think this is where I go wrong)
            var data = new google.visualization.DataTable();
            data.addColumn('string', 'Accounts');
            data.addColumn('number', 'Balance');
            data.addRows([

               ['<?php echo $acc_type; ?>', <?php echo $acc_bal; ?>],
               ['<?php echo $acc_type; ?>', <?php echo $acc_bal; ?>],   
            ]);

            // Set chart options
            var options = {'title':'Account Balance',
                           'width':400,
                           'height':300};

            // Instantiate and draw our chart, passing in some options.
            var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
            chart.draw(data, options);
          }
        </script>

        <!--Div that will hold the pie chart-->
        <div id="chart_div"></div>

A little more information would be helpful. Does your data render correctly in the table view? If you check the console are you getting any sort of js error message? In short: What results are you seeing now? — Edward
How to insert the same data that is shown on the table on the pie chart? @JensonMJohn — user3210617
Yes my data is correct on the table view. When I run this code, only one row of the data is shown on the pie chart, when actually I have 2 rows of data on my table view. @Edward — user3210617
Your PHP is outputting the same values for both rows in javascript. Are you looking to get a PieChart of all account types and balances? — asgallant

asgallant asgallant · Accepted Answer · 2014-01-21T23:21:18

To get a PieChart of account types and balances, you need to add a bit of code to your first while loop:

$pieData = array();
while ($row = mysqli_fetch_array($result)) {
    $acc_type = $row ['account_type'];
    $acc_num = $row ['account_number'];  
    $acc_bal = $row ['account_balance'];
    $pieData[] = array($row['account_type'], $row['account_balance']);
    //...
}

then in the javascript, output the $pieData array:

data.addRows(<?php echo json_encode($pieData, JSON_NUMERIC_CHECK); ?>);

Using Google Chart with PHP MYSQL

1 Answers