PHP & JpGraph issues

[JonteRules1]

Hello guys!
I have a PHP script that is intended to plot power over time.

I have a database with 10 000+ data points in the form of voltage, current and date stamps.
The purpose of the PHP script is to calculate power by multiplying voltage and current and then plotting it with time on the x-axis.

 

Right now i have put voltage in an array and just tried to plot it with a few letters on the x axis. When i try to plot the information i get no error message telling me what is wrong, i only get a small  Icon with no information with in it. Is this caused because i have to few data points on the x-axis compared to the y-axis.

By right now i have no idea of how i should continue to add the functionality which i require, what would a good way to continue be? 

I'm open to any suggestions and start over with the code if that is necessary. 

 

The code so far: 
 

<?php // content="text/plain; charset=utf-8"
require_once ('/var/www/html/jpgraph/src/jpgraph.php');
require_once ('/var/www/html/jpgraph/src/jpgraph_line.php');

   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('/home/pi/Documents/Projects/ArduinoSerialImport/solartracker.db');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

$sql =<<<EOF
SELECT voltage from stuffToPlot;
EOF;

$voltage = array();

$ret = $db->query($sql);
while ($row['voltage'] = $ret->fetchArray(SQLITE3_ASSOC)){
        $voltage[] = $row['voltage'];


//print_r ($voltage);
echo '<pre>';
//var_dump($row);
//$isScalar = is_scalar($voltage);
//var_dump(isScalar);

}

$graph = new Graph(500,500);
$graph->SetScale('textlin',-10,10,0,0);

$theme_class=new UniversalTheme;
$graph->SetTheme($theme_class);
$graph->img->SetAntiAliasing(false);
$graph->title->Set('Filled Y-grid');
$graph->SetBox(false);

$graph->img->SetAntiAliasing();

$graph->yaxis->HideZeroLabel();
$graph->yaxis->HideLine(false);
$graph->yaxis->HideTicks(false,false);

$graph->xgrid->Show();
$graph->xgrid->SetLineStyle("solid");
$graph->xaxis->SetTickLabels(array('A','B','C','D'));
$graph->xgrid->SetColor('#E3E3E3');

$p1 = new LinePlot($voltage);
$graph->Add($p1);
$p1->SetColor("#6495ED");
$p1->SetLegend('Voltage');

$graph->legend->SetFrameWeight(1);

// Output line
$graph->Stroke();

?>

Thanks a lot in advance!

/J

[vorticalbox]

 

edit: Ignore this post im tired and didn't full read the code.

[Mr_KoKa]

I see you haven't got rid of this contraption yet:

while ($row['voltage'] = $ret->fetchArray(SQLITE3_ASSOC)){
        $voltage[] = $row['voltage'];

please run this for me as it is:

<?php // content="text/plain; charset=utf-8"
require_once ('/var/www/html/jpgraph/src/jpgraph.php');
require_once ('/var/www/html/jpgraph/src/jpgraph_line.php');

   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('/home/pi/Documents/Projects/ArduinoSerialImport/solartracker.db');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

$sql =<<<EOF
SELECT voltage from stuffToPlot;
EOF;

$voltage = array();

$ret = $db->query($sql);
var_dump($ret->fetchArray(SQLITE3_ASSOC));

and paste output here. I want to have sure what data structure you get there.

 

 

Also, you probably forgot to comment this line

echo '<pre>';

Stroke() function generates image, and any other outputs are forbidden as they will break it.

[SSL]

Surely it should be:

 

while ($row = get_db_shit()) {
  $voltage[] = $row['voltage'];
}

 

[Mr_KoKa]

4 minutes ago, SSL said:

Surely it should be:

 

while ($row = get_db_shit()) {
  $voltage[] = $row['voltage'];
}

 

That's what I always thought, but giving this discussion:

 I'm not sure anymore

 

 

 

[JonteRules1]

10 minutes ago, Mr_KoKa said:

I see you haven't got rid of this contraption yet:

while ($row['voltage'] = $ret->fetchArray(SQLITE3_ASSOC)){
        $voltage[] = $row['voltage'];

please run this for me as it is:

<?php // content="text/plain; charset=utf-8"
require_once ('/var/www/html/jpgraph/src/jpgraph.php');
require_once ('/var/www/html/jpgraph/src/jpgraph_line.php');

   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('/home/pi/Documents/Projects/ArduinoSerialImport/solartracker.db');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

$sql =<<<EOF
SELECT voltage from stuffToPlot;
EOF;

$voltage = array();

$ret = $db->query($sql);
var_dump($ret->fetchArray(SQLITE3_ASSOC));

and paste output here. I want to have sure what data structure you get there.

 

 

Also, you probably forgot to comment this line

echo '<pre>';

Stroke() function generates image, and any other outputs are forbidden as they will break it.

Maybe i miss interpreted what you meant in the other thread

 

This was the result i got when i ran your code:  Opened database successfully array(1) { ["voltage"]=> float(4.02) }

 

I can upload the database with fewer datapoints if you would like. I can remove some data to make it smaller, would that help? The database doesn't contain any sensitive information. I can post the Python script to if you would like to see how i input the data into the database.

 

Thanks for your reply!

[Mr_KoKa]

Yeah, it should be as SSL said

8 minutes ago, SSL said:

Surely it should be:

 

while ($row = get_db_shit()) {
  $voltage[] = $row['voltage'];
}

 

I thought we already figured this out in your previous thread. 

[SSL]

Please read: http://php.net/manual/en/sqlite3result.fetcharray.php

 

SQLite3Result::fetchArray returns a SINGLE ROW indexed numerically OR associative OR both depending on how it is configured.

 

Note that the associative indexes are DATABASE COLUMN NAMES not whatever random variable you want it to be.

[JonteRules1]

Just now, Mr_KoKa said:

Yeah, it should be as SSL said

I thought we already figured this out in your previous thread. 

I have corrected this now but the result is the same (small icon). I got confused in the last thread and i will have to blame this upon my own stupidity, when i read the thread again i can see that i got it wrong the last time. Sorry about that.

 

Any ideas of how to proceed?

 

/J

[SSL]

2 minutes ago, JonteRules1 said:

I have corrected this now but the result is the same (small icon). I got confused in the last thread and i will have to blame this upon my own stupidity, when i read the thread again i can see that i got it wrong the last time. Sorry about that.

 

Any ideas of how to proceed?

 

/J

 

I would read the JpGraph documentation to make sure you are using it correctly.

[Mr_KoKa]

do you link your script to <img> tag as it's src? Run it directly and it shoudl yell errors. It displays that icon because it cannot load image I guess, it maybe also that those errors occurs after image header has been sent and they're at the end of image data as text, which corrupts image data and does't display errors.

[JonteRules1]

19 minutes ago, SSL said:

Please read: http://php.net/manual/en/sqlite3result.fetcharray.php

 

SQLite3Result::fetchArray returns a SINGLE ROW indexed numerically OR associative OR both depending on how it is configured.

 

Note that the associative indexes are DATABASE COLUMN NAMES not whatever random variable you want it to be.

So i  shouldn't fetch the array as an associative? The variable i'm using (voltage) has the same name as the column in the database. Is this still considered as random?

14 minutes ago, SSL said:

 

I would read the JpGraph documentation to make sure you are using it correctly.

Will do!

12 minutes ago, Mr_KoKa said:

do you link your script to <img> tag as it's src? Run it directly and it shoudl yell errors. It displays that icon because it cannot load image I guess, it maybe also that those errors occurs after image header has been sent and they're at the end of image data as text, which corrupts image data and does't display errors.

I can always try to put it with in html and see if that brings a different result. Right now i'm running the script directly in Chrome. I have tried their demo found at: http://jpgraph.net/features/src/show-example.php?target=new_line1.php and that works flawlessly in another test script browsed directly.

[SSL]

1 minute ago, JonteRules1 said:

So i  shouldn't fetch the array as an associative? The variable i'm using (voltage) has the same name as the column in the database. Is this still considered as random?

 

If the column name is literally 'voltage' then it's okay as is.

 

Does the line plot data need to be numerical and is that what you are giving it?

[JonteRules1]

15 minutes ago, SSL said:

 

If the column name is literally 'voltage' then it's okay as is.

 

Does the line plot data need to be numerical and is that what you are giving it?

I'm giving it float values in the format of (x.xx) perhaps the punctuation messes up how jpgraph interprets the data? I haven't read the documentation yet so i can't tell whether it has to be a comma or a punctuation.

 

I have set the scaling in jpgraph to be textlin which is  text on x-axis and linear scale on y-axis.

 

This is an example of what i am trying to create:

What i intend to do

[Mr_KoKa]

$row['voltage'] is float so you should be ok with your data.

[JonteRules1]

On 2016-10-12 at 10:43 PM, Mr_KoKa said:

$row['voltage'] is float so you should be ok with your data.

Alright, thanks for your reply!

I've ran into some new issues "ofc". I'm trying to multiply the voltage array and the current array to receive a power array. But somehow php is complaining about a semicolon (;) and i can't figure out what semicolon i should remove.

The new part of the code look like this: 

$sql =<<<EOF
SELECT voltage, current, datestamp FROM stuffToPlot;
EOF;

$voltage = array();
$current = array();
$datestamp = array();

$ret = $db->query($sql);
while ($row = $ret->fetchArray(SQLITE3_ASSOC)){
        $voltage[] = $row['voltage'];
        $current[] = $row['current'];
        $datestamp[] = $row['datestamp'];
}

$power = array();
foreach($voltage as $key => $door {
        $power[] = $door * $current[$key];
}

print_r ($power);
echo '<pre>';

 

/J

 

Edit: The error is found on this line "$power[] = $door * $current[$key];" 

Edited October 13, 2016 by JonteRules1
Forgot to post information

[Mr_KoKa]

foreach($voltage as $key => $door {
        $power[] = $door * $current[$key];
}

You miss bracket of foreach, I'm a little bit late with this answer, and you probably found this out already.

[JonteRules1]

11 hours ago, Mr_KoKa said:

foreach($voltage as $key => $door {
        $power[] = $door * $current[$key];
}

You miss bracket of foreach, I'm a little bit late with this answer, and you probably found this out already.

Thank you! That was the problem!

[JonteRules1]

It seems like i'm closing in on my target. One additional hurdle though, when i try to draw the graph jpGrapg tells me that "Either X or Y data arrays contains non-numeric values."

 

The problem is probably related to the x-axis (time), is there some way that i can convert a string into a float? . I guess a float value is what is required to do this?

 

The $datestamp variable is formated like this: 

[98] => 2016-10-14 14:58:01

The $power variable is formated like this:

[98] => 3.3781

I think both of the formats looks alright but somehow this is causing a problem.

 

This is the jpgraph documentation about date/time scale. Compared to the first example in the documentation i have only changed the variables and added seconds to the mix too.

http://jpgraph.net/download/manuals/chunkhtml/ch14s10.html

The datestamp column is formated as REAL in the sqlite database could this be a source of the problem?

 

Any clues of how to continue?

 

Thanks a lot for your guys help so far.

 

The current code:

<?php // content="text/plain; charset=utf-8"
require_once ('/var/www/html/jpgraph/src/jpgraph.php');
require_once ('/var/www/html/jpgraph/src/jpgraph_line.php');
require_once ('/var/www/html/jpgraph/src/jpgraph_date.php');
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('/home/pi/Documents/Projects/ArduinoSerialImport/solartracker.db');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }

$sql =<<<EOF
SELECT voltage, current, datestamp FROM stuffToPlot;
EOF;

$voltage = array();
$current = array();
$datestamp = array();

$ret = $db->query($sql);
while ($row = $ret->fetchArray(SQLITE3_ASSOC)){
        $voltage[] = $row['voltage'];
        $current[] = $row['current'];
        $datestamp[] = $row['datestamp'];
print_r($datestamp);
echo '<pre>';
}
//var_dump($row);
//$isScalar = is_scalar($voltage);
//var_dump(isScalar);

$power = array();
foreach ($voltage as $key => $door) {
        $power[] = $door * $current[$key];
print_r ($power);
echo '<pre>';
}

$graph = new Graph(500,500);

//$graph-SetMargin(40,40,30,130);
$graph->SetScale('datlin',0,1500);
$graph->title->Set("Power over time");
$graph->xaxis->SetLabelAngle(90);
$line = new LinePlot($power,$datestamp);
$line->SetLegend('Year 2016');
$graph->Add($line);
// Output line
$graph->Stroke();

?>

 

[Mr_KoKa]

Can you paste one row of how data looks when you browse your database (not by PHP, but by whatever you browse your database) and maybe structure of the table.

 

I don't think that real is best type to store datetime, I would use either datetime type or integer type. It seems you're storing your data as datetime which is ok. You would need to convert it to timestamp which can be done by strtotime function or creating new DateTime and passing datestamp as its constructor argument, and then use getTimestamp() member function.

 

The example then shows how to format time values by binding a callback.

[JonteRules1]

1 hour ago, Mr_KoKa said:

Can you paste one row of how data looks when you browse your database (not by PHP, but by whatever you browse your database) and maybe structure of the table.

 

I don't think that real is best type to store datetime, I would use either datetime type or integer type. It seems you're storing your data as datetime which is ok. You would need to convert it to timestamp which can be done by strtotime function or creating new DateTime and passing datestamp as its constructor argument, and then use getTimestamp() member function.

 

The example then shows how to format time values by binding a callback.

Okay!

 

This is the structure of my database, it's really simple containing just a few columns.

I do not have an option in the db browser to set it to datetime, should i change this to integer instead.
The formats which i can choose are (Text, blob, real, numeric, integer).

 

I´ll do some reading on that function and see if i can figure it out

 

/J

[Mr_KoKa]

I don't know, I always thought real is floating point value, which date is not, but your database seems to store date as string, I don't know how it works,a nd why it is real.

 

anyway, use functions I told you in my last post to convert string date to integer timestamp as you do $datestamp[] = $row['datestamp'];

[JonteRules1]

On 2016-10-14 at 9:12 PM, Mr_KoKa said:

I don't know, I always thought real is floating point value, which date is not, but your database seems to store date as string, I don't know how it works,a nd why it is real.

 

anyway, use functions I told you in my last post to convert string date to integer timestamp as you do $datestamp[] = $row['datestamp'];

Not quite sure how to use the strtotime when i use an array. Because i always get an error telling me that the function expected a string and not an array.

Would it be easier if i stored time as unix timestamps in the database and later converted it within php? The datapoints i have today are erasable so it wouldn't make a ton of difference to me either way. 

 

Thnx

/J

[Mr_KoKa]

You need to use that function on each element of array. You could do that as you pushing those elements to $datestamp array here:

$datestamp[] = $row['datestamp'];

 

[JonteRules1]

On 2016-10-16 at 5:07 PM, Mr_KoKa said:

You need to use that function on each element of array. You could do that as you pushing those elements to $datestamp array here:

$datestamp[] = $row['datestamp'];

 

I must completely utterly stupid but i didn't get that one at all. Do you mean that i should create a foreach function which does something like this?

 

$unixtimestamp = array();
foreach ($datestamp as $sun => $nebulosa){
        $unixtimestamp[] = strtotime($nebulosa[$sun]);
}

The code is messed up i'm aware of that but do you understand where i am going with it? Right now i receive uninitialized string offset.