Displaying data using AJAX PHP

Question

Flow of the program: 1. Upon load of the page, the chart will display the total sales of all the branches. 2. When you choose a branch from the dropdown button, the data shown by the chart is the total sales of that specific branch.

My problem is when I click an specific branch the data wont show at all.

Please refer to the screenshot and Code for more information.

Screenshot: Screenshot of the page when it loads Screenshot of the page when I choose a branch

PHP Code for data:

<div class="report-header">
    <h5 class="report-title">Total Yearly Sales</h5>
        <div class="branch-report">
            <select class="form-control" id="t-yearly">
                <option value="">Branch</option>
                <?php
                    require_once "connect.php";

                    $sql = "SELECT id,branch FROM tblLocation";
                    $result = mysqli_query($conn,$sql);

                    while ($row = mysqli_fetch_array($result)) {
                     echo "<option value='".$row['id']."'>".$row['branch']."</option>";
                     }
                    ?>
                </select>
            </div>
        </div>

        <div id="t-yearly-sales" style="height: 80%;"></div>
            <?php
                include "connect.php";

                $sql = "SELECT year, SUM(sales) AS sales FROM tblSales GROUP BY year";
                $result = mysqli_query($conn, $sql);

                $chart = '';
                while ($row = mysqli_fetch_array($result)){
                    $chart .= "{year:'".$row["year"]."', sales:".$row["sales"]."},";
                }                       
            ?>
            <script>
                new Morris.Bar({
                  element: 't-yearly-sales',
                  data: [<?php echo $chart; ?>],
                  xkey: 'year',
                  ykeys: ['sales'],
                  labels: ['Total Sales'],
                  hideHover: 'auto'
                });
            </script>

AJAX Code:

//Total Yearly Sales
$("#t-yearly").change(function(){
    var branch = $(this).val();
    $.ajax ({
        url:"fetch_yearly_sales.php",
        method: "POST",
        data: {branch:branch},
        success: function(branch_data){
             new Morris.Bar({
                element: 't-yearly-sales',
                data: [branch_data],
                xkey: 'year',
                ykeys: ['sales'],
                labels: ['Total Sales'],
                hideHover: 'auto'
             });
             console.log(branch);
        }
    });
});

Fetch_Yearly_Sales.php Code:

   <?php 
require "connect.php";


$data = mysqli_real_escape_string($conn,$_POST['branch']);


if($data == ""){
    $output = "";
    $sql = "SELECT branch_id, year, SUM(sales) AS sales FROM tblSales GROUP BY year";
    $result = mysqli_query($conn, $sql);

    while ($row = mysqli_fetch_array($result)) {
        $chart .= "{year:'".$row["year"]."', sales:".$row["sales"]."},";
    }
    ob_clean();
    echo $output;
}
else{
    $output = "";
    $sql = "SELECT branch_id, year, SUM(sales) AS sales FROM tblSales WHERE branch_id='".$data."' GROUP BY year";
    $result = mysqli_query($conn, $sql);

    while ($row = mysqli_fetch_array($result)) {
        $chart .= "{year:'".$row["year"]."', sales:".$row["sales"]."},";
    }
    ob_clean();
    echo $output;
}
?>

`$("#t-yearly").change(function(){ alert('hello'); } );` do you the the alert box with this? — Niclas Larsson, Feb 20 '18 at 06:28
In ajax code use `alert(branch_data);` to check what error you are getting — Nikita Agrawal, Feb 20 '18 at 06:29
@NiclasLarsson yes my ajax script is going to the success part but the data is not reflecting to the chart please see screenshots thank you — , Feb 20 '18 at 06:30
@NikitaAgrawal when I alert the branch_data the alertbox is empty why is it empty? — , Feb 20 '18 at 06:32
@NiclasLarsson when I alert the branch data it is empty why? can you check my code for errors — , Feb 20 '18 at 06:32
Both of your mysql queries are incorrect and would result in an error message if your server were properly configured... — Shadow, Feb 20 '18 at 06:34
@Shadow can you help me fix my code im a total newbie please help me — , Feb 20 '18 at 06:35
@NikitaAgrawal I will use a prepare statement in the future but I need to fix this problem first please help — , Feb 20 '18 at 06:38
See https://stackoverflow.com/questions/1023347/mysql-selecting-a-column-not-in-group-by — Shadow, Feb 20 '18 at 06:44

Niclas Larsson · Answer 1 · 2018-02-21T13:21:24.123

0

The last comma is invalid. You should really consider using json_encode.

while ($row = mysqli_fetch_assoc($result)) {
    $chart .= "{year:'".$row["year"]."', sales:".$row["sales"]."},";
}
echo $output;

Also you're not printing anything:

$output = "";
echo $output;

but you use $chart .= ""; to prepend data.

json_encode example:

$arr = array();
while ($row = mysqli_fetch_assoc($result)) {
    $arr[] = $row;
}

echo json_encode( $arr );

Whole ajax example:

require "connect.php";


$data = mysqli_real_escape_string($conn,$_POST['branch']);


if($data == ""){
    $sql = "SELECT year, SUM(sales) AS sales FROM tblSales GROUP BY year";
    $result = mysqli_query($conn, $sql);

    $arr = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $arr[] = $row;
    }

    echo json_encode( $arr );
}
else{
    $sql = "SELECT year, SUM(sales) AS sales FROM tblSales WHERE branch_id='".$data."' GROUP BY year";
    $result = mysqli_query($conn, $sql);

    $arr = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $arr[] = $row;
    }

    echo json_encode( $arr );
}

edited Feb 21 '18 at 13:21

answered Feb 20 '18 at 06:32

Niclas Larsson

1,317
8
13

I still don't understand – Feb 20 '18 at 06:36
Updated answer. – Niclas Larsson Feb 20 '18 at 06:40
What happens if you browse to `fetch_yearly_sales.php?` manually? – Niclas Larsson Feb 20 '18 at 06:42
When i alerted the branch_data here is the output [{"0":"2017","year":"2017","1":"82860","sales":"82860"},{"0":"2018","year":"2018","1":"80689","sales":"80689"}] – Feb 20 '18 at 06:42
I need the year and sales part only not the "0":"2018" part – Feb 20 '18 at 06:43
@lawrenceagulto pls read the manual on mysqli_fetch_array's parameters and check out what is used in its place in this answer. – Shadow Feb 20 '18 at 06:48
@Shadow the sql code is correct i just need to get rid of this part "0":"2018", – Feb 20 '18 at 06:56
@lawrenceagulto that's why you should check out the parameter of mysqli_fetch_array()... – Shadow Feb 20 '18 at 06:59
@Shadow I got it the problem now is the format should be "{year:'".$row["year"]."', sales:".$row["sales"]."}," in order for the data be transferred to the chart – Feb 20 '18 at 07:00
@Shadow I am using morris.js to display the sales – Feb 20 '18 at 08:07
Don't use _array then use _assoc as I wrote. – Niclas Larsson Feb 21 '18 at 13:19

score 0 · Answer 2 · answered Feb 20 '18 at 06:44

You can try this in PHP

if($data == ""){
$sql = "SELECT branch_id, year, SUM(sales) AS sales FROM tblSales GROUP BY year";
 $result = mysqli_query($conn, $sql);
 while ($row = mysqli_fetch_assoc($result)) {
   $chart = '{year:'.$row["year"].', sales:'.$row["sales"].'},';
 } 
 echo $chart; 
   } else {
  $sql = "SELECT branch_id, year, SUM(sales) AS sales FROM tblSales WHERE branch_id='".$data."' GROUP BY year";
$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_array($result)) {
    $chart = '{year:'.$row["year"].', sales:'.$row["sales"].'}';
}
echo $chart;
}

I am using morris.js to display the data – Feb 20 '18 at 07:31 — , Feb 20 '18 at 07:31

Displaying data using AJAX PHP

2 Answers2