Requesting data

PHP
1

Hi,I am trying to create a graph using chartjs,I am still new on this creating graph and i think i am in big trouble,what i want to achieve is that i am going to get all the number registered employee in each month from jan-dec. and to draw it to a graph with the use of jquery.Ajax,…my question on this do i need to create 12 php script for counting the registered employe in each month ?and how do i set them to the graph ?

This is my code please correct me if i am wrong.



 $.ajax({
    type: 'post',
    url : 'countemployee.php',
    success: function(data){
      jan = data.january;
      feb = data.february;
     ect...
   }
});

countemployee.php


$jan='';
 try{
       //query for the mont of january   
       $connectionString = $db->prepare("select (*) as count from employee   
                                     where date_hired > = '2013-01-01' AND date_hired <= '2013-01-31'");

         $jan = $sql->fetchColumn();

 }
catch(){
  //log error message here.
}

 try{
        //another count query for month of february
 }
catch(){
  //log error message here.
}


 try{
        //another count query for month of march
 }
catch(){
  //log error message here.
}

etc...

encode to json

$month = array("$january"=>'$jan',
                      "$february"=>'$feb',
                     etc..);

json_encode($month);




<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="initial-scale = 1, user-scalable = no">
    <script src="Chart.js-master/Chart.js"></script>
</head>
<body>
<canvas id="myChart" height="450" width="600"></canvas>

<script type="text/javascript">


    var data = {
        labels: ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"],
        datasets: [
            {
                fillColor: "rgba(220,220,220,0.5)",
                strokeColor: "rgba(220,220,220,1)",
                data: jan,feb,etc..
            }

        ]
    }


    var ctx = document.getElementById("myChart").getContext("2d");

    new Chart(ctx).Bar(data);

</script>

</body>

</html>
2

I would think that someone from the PHP forum will be better placed to answer this part of things. Moving the thread over there now.

3

Hi Jemz,

Rather than doing separate queries for each month, you can return all the data with one query like this:

SELECT COUNT(*) as hired, MONTHNAME(date_hired) as month 
FROM employees 
GROUP BY MONTH(date_hired)

Then use PDO’s fetchAll method to return all the rows as an array, which you can loop over and put into an associative array to be encoded as JSON and sent back to the browser:


$rows = $sql->fetchAll();

$results = array();
foreach ($rows as $row) {
    $results[$row['month']] = $row['hired'];
}

echo json_encode($results);
4

You may need to add a WHERE clause to make sure you only get data for the current year instead of ALL years that may be stored in the database. Or add Year to your SELECT and GROUP BY so you can show all months over each year.

5

Hi fretburner, Thank you for this… is this a function “MONTHNAME”,okay how do i put the where clause of the year just like cpradio says.?

6

Yes, MONTHNAME is a MySQL function - you can find out more in the manual: https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_monthname

To limit the query to a specific year, you would do something like this:

WHERE YEAR(date_hired) = '2013'
7

Hi fretburner, Thank you so much it’s working now,thank you for helping me :slight_smile:

8

Hi fretburner, Is it possible to encode in json with 2 arrays ?


 json_encode($array1,$array2);
9

you’d want to put them in another array

json_encode(array('firstArray' => $array1, 'secondArray' => $array2));
10

Hi cpradio,

Thank you for this :slight_smile: