Page 1 of 1

converting date time and using this calculate various values

Posted: Fri Oct 06, 2006 5:27 am
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

Posted: Fri Oct 06, 2006 7:05 am
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?

Posted: Fri Oct 06, 2006 7:23 am
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();
      }

Posted: Fri Oct 06, 2006 8:05 am
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.

Posted: Fri Oct 06, 2006 8:25 am
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

Posted: Fri Oct 06, 2006 9:02 am
by volka
can you provide sample data (as valid sql statements or dump) for two or three printers for two or three months?

Posted: Fri Oct 06, 2006 9:12 am
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

Posted: Mon Oct 09, 2006 4:22 am
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