CODEWITHSUNDEEP
javascriptphpmysqlsql
hacker_0101
hacker_0101

Reputation: 11

SQL Query Within a While Loop Not Working Using PHP and JS

What I'm trying to do is simple. I have a MySQL database table for ice cream sales called "ice_cream_sales". The columns I have for the table is the "Flavor" of ice cream and a "DateTime" timestamp. I want to display the number of ice cream sales per flavor for each year. So I have a while loop that executes an SQL query that retrieves the number of rows for each year, then depending on the flavor of ice cream for the rows retrieved, it adds it to the appropriate PHP variable for that flavor of ice cream to count the sales for that ice cream in the current year. Then I want to use a Google line chart that uses a JS array to display the ice cream sales per year. Visit this link to see how it works: https://google-developers.appspot.com/chart/interactive/docs/gallery/linechart.

However, for now I just want to see that I can successfully transfer PHP variables to JS variables and then add those variables to a JS array. So I transfer the PHP variables to JS variables using echo statements, since the PHP code is embedded in a JS script tag. Then I create an array using these counter values for each flavored ice cream per year that will be appended to the array used for the Google line chart. When I try executing the code, I get a blank page with no error messages. Any suggestions and fixes would be much appreciated.

When I comment out the if statement block of code under the while statement to check for the flavor of ice cream, the code works well and I get an output array of [2010, 0, 0, 0], [2011, 0, 0, 0] and so on. 

  <script>
      var toAdd = new Array();
      <?php

      $year = intval(2010);

      while ($year <= 2019) {
        include 'includes/dbh_cred.php';
        $sql = "SELECT * FROM ice_cream_sales WHERE myDate LIKE '$year%'";
        $chocolate = intval(0);
        $vanilla = intval(0);
        $strawberry = intval(0);
        $result = mysqli_query($connect, $sql);
       if (mysqli_rows($result) > 0) {
          while ($row = mysqli_fetch_assoc($result)) {
            if ($row['Flavor'] == 'Chocolate')
              $chocolate = $chocolate + 1;
            else if ($row['Flavor'] == 'Vanilla')
                $vanilla = $vanilla + 1;
            else  if ($row['Flavor'] == 'Strawberry')
                $strawberry = $strawberry + 1; 
              }
            }
          //chocolate, vanilla, strawberry sales for that particular year
              echo "var chocolate = " . $chocolate . ";";
              echo "var vanilla = " . $vanilla . ";";
              echo "var strawberry = " . $strawberry . ";";
              echo "var year = " . $year . ";";
              //add these values to an array
              echo "toAdd.push([year, chocolate, vanilla, strawberry]);";
              $year = $year +1;
          }

           ?>



        var data = new Array(["Year", "Chocolate", "Vanilla", "Strawberry"]);
        console.log(data[0][0] + "\t" + data[0][1] + "\t" + data[0][2] + "\t" 
                     + data[0][3]);
        data.push(toAdd);
          for(var i = 1; i < data.length; i++) {
            for(var j = 0; j < data[i].length; j++) {
        console.log(data[i][j]);

Upvotes: 0

Views: 104

Answers (2)

miken32
miken32

Reputation: 42681

This could be a lot LOT simpler.

<?php
include 'includes/dbh_cred.php';
$sql = "SELECT
  YEAR(myDate) AS year,
  SUM(IF(Flavor = 'chocolate', 1, 0)) AS chocolate,
  SUM(IF(Flavor = 'strawberry', 1, 0)) AS strawberry,
  SUM(IF(Flavor = 'vanilla', 1, 0)) AS vanilla
FROM ice_cream_sales
GROUP BY YEAR(myDate)";
$result = $connect->query($sql);
if ($result->num_rows) {
    while ($row = mysqli_fetch_assoc($result)) {
        $data[] = $row;
    }
}
?>
<script>
var data = <?php echo json_encode($data);?>;
</script>

Here's a demo of the SQL: http://sqlfiddle.com/#!9/f7ca1b/3/0. Basically it's maintaining a counter for each flavour, and then grouping it by year, so all your data is compiled as part of the database query. Then all you need to do in PHP is put the records into an array and output them in JSON format.

I'd recommend using PDO for database access, as it's much easier to learn and would make this code even more concise.

Upvotes: 1

yang0111
yang0111

Reputation: 1

Try follow SQL, may it easy for you?

select Flavor,count(*) from ice_cream_sales WHERE myDate LIKE '$year%' group by Flavor

Upvotes: 0

Related Questions