converting date time and using this calculate various values

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

converting date time and using this calculate various values

Post by ryuuka »

this has been breaking me up over the course of several days

story time:
i have gotten the mission to put the total prints made each month in a graph.
Now the graph is no problem but the way i get that data is. This is because
every day the total number of prints made is updated, meaning i get an extra
record per printer every day. The number of total prints made is not reset each month.

every date is recorded by means of this date format: 1-8-2006

i am searching for a way to get this data to show up right.
this cannot be done per server because i need the total of each server per month.

i need to select the month and then all the servers with total number of prints made so far need to show up.

picture of DB table:
Image

i've tried a lot but this is what's usefull:

select page:

Code: Select all

$rsa  = $db->Execute("SELECT datum FROM @@@@@@_printer_stats GROUP BY datum ORDER BY datum DESC");

  echo ('<form action="/index.php?pagename=printerstotaal" method="POST">
          
              <select name="datum">              
              <option value= "0" selected> Selecteer datum: 
              
       ');
                                while (!$rsa->EOF)
                                {
                                $s_datum            = $rsa->Fields['datum']->value;
                                //$timestamp = strtotime($s_datum);
                                $correctdate = date("F", strtotime($s_datum));
                                
                                  
                                  echo "<option value=$correctdate> $correctdate";
                                  $rsa->movenext();
                                }
           
    echo ('</select>
          <input type="image" name="submit" src="/gispen/submit.gif" width="75" height="17">
        </form>');
  
  if (isset($_POST['datum']))
  {
  $datum = $_POST['datum'];

     echo " <img src=http://www.@@@@@@.net/@@@@@@/jpgraph_printers_Totaal.php?datum=".$datum.">";

graph page:

Code: Select all

$rs = $objConn->Execute("SELECT datum, naam, totaal FROM @@@@@@_printer_stats 
                                                     WHERE (naam = 'pr0150')");
  
  while (!$rs->EOF) 
      {
      $s_datum             = $rs->Fields['datum']->value;
      $s_naam              = $rs->Fields['naam']->value; 
      $s_totaal[]          = $rs->Fields['totaal']->value;
      //settype($s_totaal, "string");
           
           $DBasetime = strtotime($s_datum);
           $correctdate = date("F", strtotime($DBasetime));

thanks in advance
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

The counter is always incremented?
I.e.
pr0150 1-aug-2006 22654
pr0150 27-aug-2006 30125
-> 7471
And that's the result you want, pr0150,august,7471?
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

essentially yes
but then for all the printers i have (bout 15)

so this needs to happen for every printer and prefferably with minimal amounts of code

EDIT: i just made what i want happen with just 1 bar the rest is on its way.

Code: Select all

$rs = $objConn->Execute("SELECT datum, naam, totaal FROM gispen_printer_stats WHERE (naam = 'pr0150')");
  
  while (!$rs->EOF) 
      {
      $s_datum             = $rs->Fields['datum']->value;
      $s_naam              = $rs->Fields['naam']->value; 
      $totaal[]          = $rs->Fields['totaal']->value;
        
      $totalmin = min($totaal);
      $totalmax = max($totaal);
      $totalround = ($totalmax - $totalmin);
      $awesome =array($totalround);
      $rs->MoveNext();
      }
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

You can group by naam and select max(totaal)-min(totaal). Then you have to limit each query to one month and you're done.
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

The date limit thing will be a little hard to figure out
but i will have some fun in doing so.

wil post complete code when i'm finished. till then status remains unsolved
thanks man
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

can you provide sample data (as valid sql statements or dump) for two or three printers for two or three months?
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

can't supply the input data because that is pulled from the
printer websites (monitor site) and it's hard to read out
but i can however supply you with the table makeup and a few bits
of data.

table make up:

Image

bits of data:
Image

hope this will be enough

will check in the weekend but actual work on the code will have to wait until monday
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

got it working

the datetime was requested wrongly
instead of 1-8-2006 it has to be 2006-8-1

the first is just for the show

thanks for all the help
Post Reply