CODEWITHSUNDEEP
javascriptphpmysqlgoogle-visualizationlinechart
Prateek
Prateek

Reputation: 1257

MySQL SELECT is not returning values in correct way and google line graph is not showing in desired way

I am quite new here and asking question for first time, So I don't know how to ask question, But I need help of your's.

Image of google line graph enter image description here

I have 2 problems in the above graph.

1. I am getting repeated dates for views from my database and is display in the graph as above marked as 1 and 2.

2. The dates are not aligned properly with dates and is marked as 3 in above image and also months are not shown some times as visible in the image.

Help me please. Thank's in Advance

php & MySQL Code

function AdminViews()
{
    if(!$this->DBLogin())
    {
        $this->HandleError("Database login failed!");
        return false;
    }
    $out_query = "SELECT count(ip_address) AS count, date(visit_date_time) as visit_date_time FROM db_views WHERE user_id = id GROUP BY visit_date_time ORDER BY visit_date_time LIMIT 30 ";
    $result = mysqli_query($this->connection,$out_query);
    while($row = mysqli_fetch_array($result))
    {
        $resultset[] = $row;
    }       
    if(!empty($resultset))
        return $resultset;
}

Google Graph Javascript Code

<script type="text/javascript">
    google.load("visualization", "1", {packages:["corechart"]});
    google.setOnLoadCallback(drawChart);
    function drawChart() {
        var data = google.visualization.arrayToDataTable([ ['Date', 'Views']
        <?php
        $results = $ClassName->AdminViews();
        if(!empty($results)){
            foreach($results as $row) {
                $vDate = str_replace("-",", ",$row['visit_date_time']);
                echo ",[new Date('".$vDate."'),".$row['count']."]";
            }
        } ?> ]);

        var options = {
            pointSize: 5,
            legend: { position: 'top', alignment: 'end' },
            hAxis: { format: 'MMM dd, yyyy', gridlines: {count: -1, color: '#fff'} },
            vAxis: { minValue: 0 }
        };
        var chart = new google.visualization.LineChart(document.getElementById("barChart"));
        chart.draw(data, options);
    } </script>

At Browser Js comes like this

<script type="text/javascript">
    google.load("visualization", "1", {packages:["corechart"]});
    google.setOnLoadCallback(drawChart);
    function drawChart() {
        var data = google.visualization.arrayToDataTable([ ['Date', 'Views']
        ,[new Date('2017, 01, 01'),6],[new Date('2017, 01, 02'),6],[new Date('2017, 01, 03'),6],[new Date('2017, 01, 04'),6],[new Date('2017, 01, 05'),7],[new Date('2017, 01, 06'),5],[new Date('2017, 01, 07'),5],[new Date('2017, 01, 07'),3],[new Date('2017, 01, 08'),3],[new Date('2017, 01, 08'),4],[new Date('2017, 01, 09'),2],[new Date('2017, 01, 10'),2],[new Date('2017, 01, 10'),6],[new Date('2017, 01, 11'),6],[new Date('2017, 01, 12'),6],[new Date('2017, 01, 13'),6],[new Date('2017, 01, 14'),6],[new Date('2017, 01, 15'),6],[new Date('2017, 01, 16'),6],[new Date('2017, 01, 17'),10],[new Date('2017, 01, 18'),30],[new Date('2017, 01, 19'),3],[new Date('2017, 01, 20'),3] ]);

        var options = {
            pointSize: 5,
            legend: { position: 'top', alignment: 'end' },
            hAxis: { format: 'MMM dd, yyyy', gridlines: {count: -1, color: '#fff'} },
            vAxis: { minValue: 0 }
        };
        var chart = new google.visualization.LineChart(document.getElementById("barChart"));
        chart.draw(data, options);
    }
</script>

Upvotes: 1

Views: 78

Answers (2)

gwnp
gwnp

Reputation: 1216

Your group by also needs to cast the datetime to a Date, otherwise you will see in your data that you have multiple entries for the same day but slightly different timestamps, this will give you the multiple nodes.

Change this:

  ... GROUP BY visit_date_time ORDER BY visit_date_time ...

To this:

 ...  GROUP BY DATE(visit_date_time) ORDER BY visit_date_time ...

That should do the trick.

EDIT

For your second issue, count: -1 --> means automatic alignment of grid lines which produces the undesirable result here.

Change this:

var options = {
        pointSize: 5,
        legend: { position: 'top', alignment: 'end' },
        hAxis: { format: 'MMM dd, yyyy', gridlines: {count: -1, color: '#fff'} },
        vAxis: { minValue: 0 }
    };

To this (as you figured out):

var options = {
            pointSize: 5,
            legend: { position: 'top', alignment: 'end' },
            hAxis: { slantedText: 'true', format: 'MMM dd', gridlines: {count: 10, color: 'none'} },
            vAxis: { minValue: 0 }
        };

Disclaimer: The second answer came from OP himself as I wasn't sure.

Upvotes: 2

Prateek
Prateek

Reputation: 1257

Ok here is the solution I found

1. Change

 ... GROUP BY visit_date_time ORDER BY visit_date_time ...

to this as suggested by @gwnp

...  GROUP BY DATE(visit_date_time) ORDER BY visit_date_time ...

And

2. Change

var options = {
        pointSize: 5,
        legend: { position: 'top', alignment: 'end' },
        hAxis: { format: 'MMM dd, yyyy', gridlines: {count: -1, color: '#fff'} },
        vAxis: { minValue: 0 }
    };

to this

var options = {
            pointSize: 5,
            legend: { position: 'top', alignment: 'end' },
            hAxis: { slantedText: 'true', format: 'MMM dd', gridlines: {count: 10, color: 'none'} },
            vAxis: { minValue: 0 }
        };

count: -1 --> means automatic alignment of grid lines which produces the undesirable result here.

Upvotes: 0

Related Questions