Author Topic: Help needed with detail/summary/season script.  (Read 9456 times)

Alan Rowley

  • Strong Breeze
  • ***
  • Posts: 150
  • It is far better to wear out than to rust away.
    • D3696
    • INOTTING15
    • Selston Weather
  • Station Details: Weather station: Davis Vantage Pro2. Software: WeatherCat. Computer: Mac Mini, High Sierra OS
Help needed with detail/summary/season script.
« on: May 01, 2016, 11:16:35 AM »
Help! I am installing Wildwood Weather's detail/summary/season script and I'm stuck.

So far I have managed to get the old climatedataout and dailynoaareport files created by Weather Display to display correctly and, using Astrosteve's stmonthlytoWDfiles.php file as detailed on this thread, I've managed to get the data from March 1st to display.

I am stuck on data from when I started using WeatherCat in July 2015 until March 1st. I have created a database and uploaded all the data, but I can't seem to get Astrosteve's createclimate.php and createnoaa.php scripts to work. They seem to connect to the database and they produce the table outline, but I get the following errors ...

Code: [Select]
[01-May-2016 10:58:38 Europe/London] PHP Warning:  Division by zero in /home/ar310153/public_html/data/createclimate.php on line 114
[01-May-2016 10:58:38 Europe/London] PHP Warning:  Division by zero in /home/ar310153/public_html/data/createclimate.php on line 115
[01-May-2016 10:58:38 Europe/London] PHP Warning:  Division by zero in /home/ar310153/public_html/data/createclimate.php on line 116
[01-May-2016 10:58:38 Europe/London] PHP Warning:  Division by zero in /home/ar310153/public_html/data/createclimate.php on line 176
[01-May-2016 10:58:38 Europe/London] PHP Warning:  Division by zero in /home/ar310153/public_html/data/createclimate.php on line 177

I know that other people have got the scripts working, but I've tried everything I know, and I'm not getting anywhere. Can someone suggest what is wrong or suggest another way of achieving this?

Thanks - ALAN.

Edit: Link to page - http://selstonweather.info/wxtempdetail.php

Alan Rowley

  • Strong Breeze
  • ***
  • Posts: 150
  • It is far better to wear out than to rust away.
    • D3696
    • INOTTING15
    • Selston Weather
  • Station Details: Weather station: Davis Vantage Pro2. Software: WeatherCat. Computer: Mac Mini, High Sierra OS
Re: Help needed with detail/summary/season script.
« Reply #1 on: May 14, 2016, 10:49:32 AM »
I've been trying for a fortnight to get the createclimate.php and createnoaa.php scripts working, but without success. The scripts connect to the database ok, but they don't seem to be pulling in the data, which is why I'm getting division by zero errors. The following code should select each day and produce a chart displaying data for each day in the month ...

Code: [Select]
$datesearch = "SELECT * FROM WeatherData WHERE CONVERT_TZ(Time,'UTC','$tz') BETWEEN '$s' AND '$e' ORDER BY Time ASC";
$result = mysql_query($datesearch);
$c = 0;
$curr_day = "01";
$tot_temp = 0;
$tot_dew = 0;
$tot_press = 0;
$MaxHiT[1] = 0;
$MinHiT[1] = 1000;
$RainDay[$c_day] = 0;
while ($row = mysql_fetch_assoc($result)) {

$local_time = strtotime($row['Time'].'UTC');
$local_date_time = date('Y-m-d H:i', $local_time);
$local_date = date('Y-m-d', $local_time);
$local_month_name = date('M', $local_time);
$local_month_num = date('m', $local_time);
$local_year = date('Y', $local_time);
$local_day_2 = date('d', $local_time);
$local_day_1 = date('n', $local_time);
if ($local_day_2 == $curr_day) {
$curr_day = $local_day_2;
$c_day = sprintf("%1d",$curr_day);
if ($row['Temperature_C'] > $MaxHiT[$c_day]) $MaxHiT[$c_day] = $row['Temperature_C'];
if ($row['Temperature_C'] < $MinHiT[$c_day]) $MinHiT[$c_day] = $row['Temperature_C'];
if ($row['RawRain_mm'] > $RainDay[$c_day]) $RainDay[$c_day] = $row['RawRain_mm'];
$tot_temp = $tot_temp + $row['Temperature_C'];
$tot_dew = $tot_dew + $row['Dewpoint_C'];
$tot_press = $tot_press + $row['Pressure_mB'];
$c++;
} else {
$c_day = sprintf("%1d",$curr_day);
$MaxHiT[$c_day] = sprintf("%05.1f",9/5 * $MaxHiT[$c_day] + 32);
$MinHiT[$c_day] = sprintf("%04.1f",9/5 * $MinHiT[$c_day] + 32);
$RainDay[$c_day] = sprintf("%05.2f",$RainDay[$c_day]* 0.03937);
$Ave_Temp[$c_day] = sprintf("%04.1f",9/5 * ($tot_temp/$c) + 32);
$Ave_dew[$c_day] = sprintf("%04.1f",9/5 * ($tot_dew/$c) + 32);
$Ave_press[$c_day] = $tot_press/$c;
$Ave_press[$c_day] = sprintf("%05.2f",$Ave_press[$c_day]/33.8637526);
$deg_days = $Ave_Temp[$c_day]-65;
if ($deg_days < 0) $heat_days[$c_day] = sprintf("%04.1f",abs($deg_days)); else $heat_days[$c_day] = sprintf("%04.1f",0);
if ($deg_days > 0) $cool_days[$c_day] = sprintf("%04.1f",$deg_days); else $cool_days[$c_day] = sprintf("%04.1f",0);
$curr_day = $local_day_2;
$c_day = sprintf("%1d",$curr_day);
$c = 0;
$tot_temp = 0;
$tot_dew = 0;
$tot_press = 0;
$MaxHiT[$c_day] = 0;
$MinHiT[$c_day] = 1000;
$RainDay[$c_day] = 0;
if ($row['Temperature_C'] > $MaxHiT[$c_day]) $MaxHiT[$c_day] = $row['Temperature_C'];
if ($row['Temperature_C'] < $MinHiT[$c_day]) $MinHiT[$c_day] = $row['Temperature_C'];
if ($row['RawRain_mm'] > $RainDay[$c_day]) $RainDay[$c_day] = $row['RawRain_mm'];
$tot_temp = $tot_temp + $row['Temperature_C'];
$tot_dew = $tot_dew + $row['Dewpoint_C'];
$tot_press = $tot_press + $row['Pressure_mB'];
$c++;

}


}
$c_day = sprintf("%1d",$curr_day);
$MaxHiT[$c_day] = sprintf("%05.1f",9/5 * $MaxHiT[$c_day] + 32);
$MinHiT[$c_day] = sprintf("%04.1f",9/5 * $MinHiT[$c_day] + 32);
$RainDay[$c_day] = sprintf("%05.2f",$RainDay[$c_day]* 0.03937);
$Ave_Temp[$c_day] = sprintf("%04.1f",9/5 * ($tot_temp/$c) + 32);
$Ave_dew[$c_day] = sprintf("%04.1f",9/5 * ($tot_dew/$c) + 32);
$Ave_press[$c_day] = $tot_press/$c;
$Ave_press[$c_day] = sprintf("%05.2f",$Ave_press[$c_day]/33.8637526);
$deg_days = $Ave_Temp[$c_day]-65;
if ($deg_days < 0) $heat_days[$c_day] = sprintf("%04.1f",abs($deg_days)); else $heat_days[$c_day] = sprintf("%04.1f",0);
if ($deg_days > 0) $cool_days[$c_day] = sprintf("%04.1f",$deg_days); else $cool_days[$c_day] = sprintf("%04.1f",0);

Can anyone who has got this script working, or anyone with a greater SQL / PHP knowledge than me (which isn't a lot), suggest what might be wrong? I'm getting desperate.

Thanks - ALAN.

xairbusdriver

  • Storm
  • *****
  • Posts: 3126
    • EW7115 (E7115)
    • KTNGERMA20
    • Mid-South Weather
  • Station Details: Davis VP2 wireless + remote Anemometer/2014 Mac min - 10.15.7/WC 3.0.5
Re: Help needed with detail/summary/season script.
« Reply #2 on: May 14, 2016, 03:40:02 PM »
Your first post shows the line numbers (114, 115, 116 & 176, 177). However, I don't see a direct association in the code in your second post that has three consecutive line with division followed by two rather further down the code with that same math). This may just be due to changes as you trouble shoot. It can also be that PHP is reporting the problem line in its sometimes confusing manner; it often reports the error line as one just past then actual problem line. Even if that's the case, I would still expect to see three consecutive lines with the problem math.

However, since your error is the infamous 'divide by zero' problem, I do see several attempts (in the } else { section) to divide by the same variable that might cause the divisor to be zero. Not sure of their actual line numbers but the lines I'm referring to are these:
$c_day
  • $MaxHiT[$c_day] = sprintf("%05.1f",9/5 * $MaxHiT[$c_day] + 32);
  • $MinHiT[$c_day] = sprintf("%04.1f",9/5 * $MinHiT[$c_day] + 32);
$c
  • $Ave_Temp[$c_day] = sprintf("%04.1f",9/5 * ($tot_temp/$c) + 32);
  • $Ave_dew[$c_day] = sprintf("%04.1f",9/5 * ($tot_dew/$c) + 32);

If possible, it might help to add an "echo" statement just before those lines to display the values of $c_day and $c so you can see those values before continuing with the script execution.
Something like:
echo 'The value of $MaxHiT[$c_day] at line xyz is: ' .  $MaxHiT[$c_day] . '<br>';
echo '5 * $MaxHiT[$c_day] + 32 = ' . 5 * $MaxHiT[$c_day] + 32 . '<br>'; break;

In the first two lines, the only way I can see this equating to zero is if $c_day is -6.4 which ends up as -32 + 32 or zero as the divisor.

In the second two lines, if 5 * ($tot_dew/$c) end up as -32, the same problem occurs.
THERE ARE TWO TYPES OF COUNTRIES
Those that use metric = #1 Measurement system
And the United States = The Banana system


Alan Rowley

  • Strong Breeze
  • ***
  • Posts: 150
  • It is far better to wear out than to rust away.
    • D3696
    • INOTTING15
    • Selston Weather
  • Station Details: Weather station: Davis Vantage Pro2. Software: WeatherCat. Computer: Mac Mini, High Sierra OS
Re: Help needed with detail/summary/season script.
« Reply #3 on: May 14, 2016, 04:51:41 PM »
Hi xair,

Ignore the line numbers as I have added lines of code to try and trap what's going wrong. I have gone back to the original script, written by AstroSteve.

The lines in which the 'division by zero' errors occur all relate to lines of conversion (e.g. centigrade to fahrenheit, inches to millimetres etc.) I have removed all these conversions to see what happens and I still get the same problem.

Adding the echo lines you suggested produced the answer '37'.

I don't think the problem lies in the conversions, and I believe the 'division by zero' is a red herring. I think the problem lies in the fact that the script isn't getting the rows of data from the database, although I know the database is being opened as I have tested this.

Could there be a problem with the "SELECT * FROM WeatherData WHERE CONVERT_TZ(Time,'UTC','$tz') BETWEEN '$s' AND '$e' ORDER BY Time ASC" line?

$s and $e are times of the start and end of data for the month.

ALAN

Alan Rowley

  • Strong Breeze
  • ***
  • Posts: 150
  • It is far better to wear out than to rust away.
    • D3696
    • INOTTING15
    • Selston Weather
  • Station Details: Weather station: Davis Vantage Pro2. Software: WeatherCat. Computer: Mac Mini, High Sierra OS
Re: Help needed with detail/summary/season script.
« Reply #4 on: May 14, 2016, 06:28:51 PM »
Also, does the command ...

   while ($row = mysql_fetch_assoc($result))

... look correct?

I've had another look at the chart that this script produces and at the top, it should say 'Month: mm/yy', but it actually displays 'Month: /'. Again, this suggests that the script isn't getting the data from the database.

ALAN.

xairbusdriver

  • Storm
  • *****
  • Posts: 3126
    • EW7115 (E7115)
    • KTNGERMA20
    • Mid-South Weather
  • Station Details: Davis VP2 wireless + remote Anemometer/2014 Mac min - 10.15.7/WC 3.0.5
Re: Help needed with detail/summary/season script.
« Reply #5 on: May 14, 2016, 11:02:15 PM »
Can you look directly at the database with something like phpMyAdmin? Your ISP probably has that available in their cPanel (or whatever they provide for accessing your files directly). You can insert that "SELECT" line and see the output directly. You might want to limit the number of rows of data in the output. You can also see exactly the name of each row (or column, depending on how you display the table) of data.

Is it possible that one of the data points was defined as text instead of numeric in one of the tables? PHP should be able to handle that, but if the text has alpha characters, it may end up changing the expected numeric value to zero.

Hopefully, the author can help you. Especially if you can share some of your data with him.

Found this interesting statement at W3Schools.com:
Quote
Notice that the BETWEEN operator can produce different result in different databases!
In some databases, BETWEEN selects fields that are between and excluding the test values.
In other databases, BETWEEN selects fields that are between and including the test values.
And in other databases, BETWEEN selects fields between the test values, including the first test value and excluding the last test value.

Therefore: Check how your database treats the BETWEEN operator
My question might be what version of SQL was used to create the databases and tables. [banghead]

There was also an example on date formatting when used in BETWEEN. Are $s and $e dates? Are they formatted correctly? See <this W3Schools> page, very near the bottom.
THERE ARE TWO TYPES OF COUNTRIES
Those that use metric = #1 Measurement system
And the United States = The Banana system


Alan Rowley

  • Strong Breeze
  • ***
  • Posts: 150
  • It is far better to wear out than to rust away.
    • D3696
    • INOTTING15
    • Selston Weather
  • Station Details: Weather station: Davis Vantage Pro2. Software: WeatherCat. Computer: Mac Mini, High Sierra OS
Re: Help needed with detail/summary/season script.
« Reply #6 on: May 14, 2016, 11:44:13 PM »
Thanks for your reply again. It's almost midnight here in the UK, so I'll try your suggestions and report back tomorrow.

Thanks -ALAN

xairbusdriver

  • Storm
  • *****
  • Posts: 3126
    • EW7115 (E7115)
    • KTNGERMA20
    • Mid-South Weather
  • Station Details: Davis VP2 wireless + remote Anemometer/2014 Mac min - 10.15.7/WC 3.0.5
Re: Help needed with detail/summary/season script.
« Reply #7 on: May 15, 2016, 12:35:41 AM »
I suspect you know more about PHP/SQL than I do, anyway! [blush] I'll be gone most of tomorrow, going to a party to celebrate the 100th birthday of my Mother-in-law's Sister! Not sure what that makes her, my Mother-in-Law once removed, on my wife's side?! [lol]
THERE ARE TWO TYPES OF COUNTRIES
Those that use metric = #1 Measurement system
And the United States = The Banana system


Alan Rowley

  • Strong Breeze
  • ***
  • Posts: 150
  • It is far better to wear out than to rust away.
    • D3696
    • INOTTING15
    • Selston Weather
  • Station Details: Weather station: Davis Vantage Pro2. Software: WeatherCat. Computer: Mac Mini, High Sierra OS
Re: Help needed with detail/summary/season script.
« Reply #8 on: May 15, 2016, 10:00:21 PM »
Hi xair, I hope the party went well.

Firstly, I can look directly at the database using phpMyAdmin which is accessible via CPanel.

I started by trying the following query, replacing $s with 2015-07-13 09:42:00, the first record in the database, and $e with 2015-07-13 09:51:00, the 10th record in the database.

Code: [Select]
SELECT * FROM WeatherData WHERE CONVERT_TZ(Time,'UTC','GMT') BETWEEN '$s' AND '$e' ORDER BY Time ASC
This produced the following error message ...

Code: [Select]
MySQL returned an empty result set (i.e. zero rows). (Query took 0.2538 sec)
I then removed the BETWEEN part at the end of the query and got the same result, as did removing the ORDER BY part.

Finally, I removed the CONVERT_TZ part and the query executed successfully, displaying the whole database ...

Code: [Select]
Showing rows 0 - 29 (442731 total, Query took 0.0016 sec)
I then tried replacing SELECT * with SELECT Time, expecting the query to produce a list of ten date/time fields, instead I got ...

Code: [Select]
MySQL returned an empty result set (i.e. zero rows). (Query took 0.2348 sec)
The database does contain one column, Conditions, which is all letters of type varchar. All the other columns are numeric of type float, except Time, which is of type timestamp. I don't know if any of that is significant.

Finally, I tested whether removing the time conversion between UTC and GMT would make any difference, so I tried the following query ...

Code: [Select]
SELECT * FROM WeatherData WHERE Time BETWEEN '$s' AND '$e' ORDER BY Time ASC
Eh voila, this worked although the chart was one hour ahead of GMT. So it would appear that the CONVERT_TZ syntax is incorrect. So, what is the correct syntax?

Progress has been made.

ALAN.

elagache

  • Global Moderator
  • Storm
  • *****
  • Posts: 6490
    • DW3835
    • KCAORIND10
    • Canebas Weather
  • Station Details: Davis Vantage Pro-2, Mac mini (2018), macOS 10.14.3, WeatherCat 3
Best of luck with this . . . (Re: detail/summary/season script.)
« Reply #9 on: May 15, 2016, 10:58:45 PM »
Dear Alan, X-Air, and WeatherCat dabblers in IT.

Firstly, I can look directly at the database using phpMyAdmin which is accessible via CPanel.

I started by trying the following query,

. . . . .

Eh voila, this worked although the chart was one hour ahead of GMT. So it would appear that the CONVERT_TZ syntax is incorrect. So, what is the correct syntax?

Progress has been made.

My sympathy at wading through the complexity of database query languages.  They appear to have a similar futile wordiness that is supposed to mimic natural language like AppleScript and one of the earliest programming languages: COBOL.  Alas, these languages aren't close enough to natural language such that your nature language intuitions are of much help.

I took a course in college that compared the artificial intelligence programming language Prolog to database query languages.  Prolog was comparatively easy to remember and understand, but I could not make heads or tails of the database query languages.  I suppose it also explains why terse programming languages like C and its descendents far outnumber programming languages designed based on elegance like Pascal.

Best of luck in your attempt to finally getting this script to produce what you want!

Cheers, Edouard

xairbusdriver

  • Storm
  • *****
  • Posts: 3126
    • EW7115 (E7115)
    • KTNGERMA20
    • Mid-South Weather
  • Station Details: Davis VP2 wireless + remote Anemometer/2014 Mac min - 10.15.7/WC 3.0.5
Re: Help needed with detail/summary/season script.
« Reply #10 on: May 16, 2016, 04:22:23 AM »
Did a quick search on CONVERT_TZ and found this explanation of the correct formatting: http://www.w3resource.com/mysql/date-and-time-functions/mysql-convert_tz-function.php.

The explanation and format are simple and intuitive, even for me, and you can check those two values in the PHP. But if this is your own data, the thought occurs to me that there may not be any need for any times one conversion. In other words, if the date/time is already correct for your own time zone, there's no need for any conversion... except, of course, during the infamous "Day Light Savings" period! :( OTOH, if that was factored into the recorded date/time! So, the best scenario would be to have the correct times already recorded which I would think your weather software would have done, even taking DST into account!

I'm writing this on my iPhone so it is too tedious to do much page swapping! I sure hope this theory works!! :)
THERE ARE TWO TYPES OF COUNTRIES
Those that use metric = #1 Measurement system
And the United States = The Banana system


Alan Rowley

  • Strong Breeze
  • ***
  • Posts: 150
  • It is far better to wear out than to rust away.
    • D3696
    • INOTTING15
    • Selston Weather
  • Station Details: Weather station: Davis Vantage Pro2. Software: WeatherCat. Computer: Mac Mini, High Sierra OS
Re: Help needed with detail/summary/season script.
« Reply #11 on: May 16, 2016, 01:57:18 PM »
X-Air,

I am using the SQL Driver which is inbuilt into WeatherCat to get the information into the database. This driver uses UTC time.

For my timezone (Europe/London) UTC is the same outside of British Summer Time, but during BST, all times are UTC +01:00. I am missing data charts for July 2015 until February 2016, so only reports for July / August / September / October 2015 are one hour out. Reports from November 2015 until February 2016 will be correct.

The syntax for the SQL query in the script is ...

Code: [Select]
"SELECT * FROM WeatherData WHERE CONVERT_TZ(Time,'UTC','$tz') BETWEEN '$s' AND '$e' ORDER BY Time ASC"
... where $tz = 'Europe/London', $s='2015-07-13 09:42:00' - the date the database was created, $e is the last entry in the database.

If I enter the following query ...

Code: [Select]
SELECT * FROM WeatherData WHERE CONVERT_TZ(Time,'UTC','Europe/London') BETWEEN '2015-07-13 09:42:00' AND '2015-07-13 09:51:00' ORDER BY Time ASC
... I get a 'MySQL returned an empty result set (i.e. zero rows)' error.

The script doesn't seem to like the CONVERT_TZ command within that query.

I'm baffled.

ALAN.

xairbusdriver

  • Storm
  • *****
  • Posts: 3126
    • EW7115 (E7115)
    • KTNGERMA20
    • Mid-South Weather
  • Station Details: Davis VP2 wireless + remote Anemometer/2014 Mac min - 10.15.7/WC 3.0.5
Re: Help needed with detail/summary/season script.
« Reply #12 on: May 16, 2016, 02:29:24 PM »
I'm stil out of town, have a funeral service this am.

It might be worth checking the version number of the PHP that your ISP is using. It's possible they have an older (4.x) version. I've never used the Convert function, have no idea about what versions it needs, etc. nor did I study how to handle invalid data.

It looks like your data and the table structure is correct with appropriate names and types, and that $s and $e values are valid. Sorry but I'm probably well past the limits of my "knowledge" level. All I can suggest is to google that function as well as attempt contact with the developer of that script. Maybe I can find more info when I get back home.
THERE ARE TWO TYPES OF COUNTRIES
Those that use metric = #1 Measurement system
And the United States = The Banana system


Alan Rowley

  • Strong Breeze
  • ***
  • Posts: 150
  • It is far better to wear out than to rust away.
    • D3696
    • INOTTING15
    • Selston Weather
  • Station Details: Weather station: Davis Vantage Pro2. Software: WeatherCat. Computer: Mac Mini, High Sierra OS
Re: Help needed with detail/summary/season script.
« Reply #13 on: May 16, 2016, 07:29:27 PM »
Well, I thought I could simply ignore the conversion and accept that the times during British Summer Time are one hour out. Unfortunately, it isn't quite as simply as that. The script gets confused because of the time zone difference. If you take a look at the page it created, you will see what I mean.

http://selstonweather.info/wxtempdetail.php?year=2015

As you can see, August's data gets shunted into September and September's data is incorrect. The last hour of August is being pushed into September and giving a false reading for the 1st of the month.

Looks like I'll have to fins a way of converting the time after all.

Sigh!

Alan Rowley

  • Strong Breeze
  • ***
  • Posts: 150
  • It is far better to wear out than to rust away.
    • D3696
    • INOTTING15
    • Selston Weather
  • Station Details: Weather station: Davis Vantage Pro2. Software: WeatherCat. Computer: Mac Mini, High Sierra OS
Re: Help needed with detail/summary/season script.
« Reply #14 on: May 16, 2016, 08:24:40 PM »
Well, that was easier than I thought. All I had to do to create the monthly report was to take the NOAA report as produced by WeatherCat and convert it from a .txt document to a .htm document.

Sometimes you can't even see things that are staring you in the face.

Now to try and create the climate graphs, which are supposed to look like this.

http://selstonweather.info/data/climatedataout32016.html

These are not so important, but I have these charts going back to when I started recording the weather in 2009 and it would be nice to fill the gaps.

ALAN.