PHP / SQL Query issue with Google visualization chart

I am having an issue with an SQL Query where no chart shows at all.

For example if I use this query:

This one works:

SELECT thedate, cars, bikes, planes from stats GROUP BY thedate

This one also works:

SELECT thedate, count(cars), bikes, planes from stats GROUP BY thedate

But as soon as I add a second count or more, the graphs will only show planes which is the last one:

SELECT thedate, count(cars) AS t1, count(bikes) AS t2, count(planes) AS t3 from stats GROUP BY thedate

The I would change this: echo "['Date: {$thedate}', {$cars}, {$bikes}, {$planes}],"; to echo "['Date: {$thedate}', {$t1}, {$t2}, {$t3}],";

Below is the full code:

<?php

    $query = "SELECT DATE(thedate), count(cars) AS t1, count(bikes) AS t2, count(planes) AS t3 FROM stats GROUP BY DATE(thedate)";
    $result = $mysqli->query( $query );
    $num_results = $result->num_rows; //get number of rows returned

    if( $num_results > 0){ ?>

    <script type="text/javascript">
       google.load('visualization', '1', {packages: ['linechart']});  //load package
    </script>

    <!-- Hourly Chart -->
    <script type="text/javascript">
        function drawVisualizationdailyChart() {

            var data = google.visualization.arrayToDataTable([ // Create and populate the data table.
                ['Date', 'Cars', 'Bikes', 'Planes'],
                <?php
                while( $row = $result->fetch_assoc() ){
                    extract($row);
                    echo "['Date: {$thedate}', {$cars}, {$bikes}, {$planes}],";
                }
                ?>
            ]);

                new google.visualization.LineChart(document.getElementById('daily_chart')).   // Create and draw the visualization.
                draw(data, {title:"Daily Chart"});
        }

        google.setOnLoadCallback(drawVisualizationdailyChart);
    </script>
<?php

}else{
    echo "0";
}
?>

Open in new window


How can I fix this issue?
LVL 1
error2013Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Just so I understand, what data are you expecting with this query?
SELECT thedate, count(cars) AS t1, count(bikes) AS t2, count(planes) AS t3 from stats GROUP BY thedate 

Open in new window

0
error2013Author Commented:
Basically Cars, Bikes and Planes all have numbers for example:

The table looks like this:

id | theDate       |  cars | bikes | planes
---------------------------------------------------------
1   10-25-2015        20        10         15
2   10-25-2015        10         15         12

and so on....

I need to plot them in the chart per day so, I want to get the total for each --- cars, bikes and planes and then plot them
into the chart.

My problem with the code is that I can only have 1 count.

For example if I do:

SELECT thedate, count(cars) AS t1, from stats GROUP BY thedate

Open in new window

... this will plot into the chart without any problems
BUT, if I add any other counts only 1 is plotted.

So, if I add: count(cars) AS t1 this works fine.
If I add count(cars) AS t1, count(bikes) AS t2  ... then cars will dissapear and bikes only will show up

Hope this helps
0
error2013Author Commented:
Also, the query actually works if I run it on phpmyadmin but for some reason google chart does not like more that one count :o/
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Julian HansenCommented:
Surely you want to SUM the values and not COUNT,

COUNT will return how many records there are for a particular day - but not the total of the quantities in each field?
0
Julian HansenCommented:
Is this not the query you are looking for?
SELECT thedate, SUM(cars) AS t1, SUM(bikes) AS t2, SUM(planes) AS t3 FROM stats GROUP BY thedate;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
error2013Author Commented:
YES!!!! That worked like a charm!

Thanks very much for your help :)
0
Julian HansenCommented:
You are welcome - thanks for the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.