CODEWITHSUNDEEP
javascriptphpjquerymysqlcharts
Rupesh Arora
Rupesh Arora

Reputation: 577

Display data according to month

I want to show employee data in pie chart. I'm able to make pie chart using jQuery and php. But demand is that in a page there are 12 button. Name Jan to Dec.

When user click on feb then pie chart of feb is shown to user. When click on dec the pie chart of dec. is shown to user.

In my database there is a date column where date is stored like 2015-06-23

I'm not able to get logic of SQL query here is my code but it need improvements.

<html>
  <head>
    <!--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.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Topping');
        data.addColumn('number', 'Slices');
        data.addRows
        ([
          <?php

            $servername = "localhost";
            $username   = "root";
            $dbname     = "dat_database";
            $password   = "";
            $conn       = new mysqli($servername, $username, $password, $dbname);
            $EMP_ID  = 'MMUM254';
            $sql        = "SELECT  typeof_task,hourspend from emp_dat_info where EMP_ID='" . $EMP_ID. "'  ORDER BY date DESC LIMIT 10 " ;
            $result     = $conn->query($sql);
            if ($result->num_rows > 0) {
                            while ($row = $result->fetch_assoc()) {
                                $a[] = "['" . $row["typeof_task"] . "', " . $row["hourspend"] . "]";
                }
                echo implode(', ', $a);

}

?>

        ]);

        // Set chart options
        var options = {'title':'How Much Hours You Spend',
                       'width':900,
                       'height':500};

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

  <body>
    <!--Div that will hold the pie chart-->
    <div id="chart_div"></div>
  </body>
</html>
<?php echo date("Y"); ?>

`

Upvotes: 1

Views: 2584

Answers (3)

sunny
sunny

Reputation: 1593

Try MONTH with date

$sql = "SELECT  typeof_task,hourspend 
            FROM emp_dat_info 
            WHERE EMP_ID='" . $EMP_ID. "'  
            ORDER BY MONTH(date) DESC LIMIT 10 " ;
            $result= $conn->query($sql);

Upvotes: 1

Riken Shah
Riken Shah

Reputation: 87

If you want to get the full name of the month then you can try MONTHNAME clause in the query. Pass the date in the MONTHNAME clause.

$sql = "SELECT  typeof_task,hourspend 
        FROM emp_dat_info 
        WHERE EMP_ID='" . $EMP_ID. "'  
        ORDER BY MONTHNAME(date) DESC LIMIT 10 " ;
        $result     = $conn->query($sql);

For more reference you can visit this link : https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_month

Upvotes: 1

Meenesh Jain
Meenesh Jain

Reputation: 2528

You can try month clause in your code to get only month data 

$sql = "SELECT  typeof_task,hourspend 
        FROM emp_dat_info 
        WHERE EMP_ID='" . $EMP_ID. "'  
        ORDER BY MONTH(date) DESC LIMIT 10 " ;
        $result     = $conn->query($sql);

Upvotes: 1

Related Questions