SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: Requesting data

  1. #1
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,159
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Requesting data

    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.

    Code:
     $.ajax({
        type: 'post',
        url : 'countemployee.php',
        success: function(data){
          jan = data.january;
          feb = data.february;
         ect...
       }
    });
    countemployee.php
    PHP Code:
    $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); 

    HTML Code:
    <!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. #2
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by jemz View Post
    my question on this do i need to create 12 php script for counting the registered employe in each month ?countemployee.php
    PHP Code:
    $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); 
    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.
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  3. #3
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,446
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Hi Jemz,

    Rather than doing separate queries for each month, you can return all the data with one query like this:
    Code MySQL:
    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:
    PHP Code:
    $rows $sql->fetchAll();

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

    echo 
    json_encode($results); 
    Last edited by fretburner; Aug 13, 2013 at 04:47. Reason: Simplified SQL slightly

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,235
    Mentioned
    154 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    Hi Jemz,

    Rather than doing separate queries for each month, you can return all the data with one query like this:
    Code MySQL:
    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:
    PHP Code:
    $rows $sql->fetchAll();

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

    echo 
    json_encode($results); 
    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. #5
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,159
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    Hi Jemz,

    Rather than doing separate queries for each month, you can return all the data with one query like this:
    Code MySQL:
    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:
    PHP Code:
    $rows $sql->fetchAll();

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

    echo 
    json_encode($results); 

    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. #6
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,446
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Quote Originally Posted by jemz View Post
    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.?
    Yes, MONTHNAME is a MySQL function - you can find out more in the manual: https://dev.mysql.com/doc/refman/5.6...tion_monthname

    To limit the query to a specific year, you would do something like this:
    Code MySQL:
    WHERE YEAR(date_hired) = '2013'

  7. #7
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,159
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by fretburner View Post
    Yes, MONTHNAME is a MySQL function - you can find out more in the manual: https://dev.mysql.com/doc/refman/5.6...tion_monthname

    To limit the query to a specific year, you would do something like this:
    Code MySQL:
    WHERE YEAR(date_hired) = '2013'


    Hi fretburner, Thank you so much it's working now,thank you for helping me

  8. #8
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,159
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi fretburner, Is it possible to encode in json with 2 arrays ?
    PHP Code:
     json_encode($array1,$array2); 

  9. #9
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,235
    Mentioned
    154 Post(s)
    Tagged
    0 Thread(s)
    you'd want to put them in another array
    PHP Code:
    json_encode(array('firstArray' => $array1'secondArray' => $array2)); 

  10. #10
    SitePoint Wizard
    Join Date
    May 2012
    Posts
    1,159
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    you'd want to put them in another array
    PHP Code:
    json_encode(array('firstArray' => $array1'secondArray' => $array2)); 
    Hi cpradio,

    Thank you for this


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •