IMPORTANT! The server may go down soon - possibly permanently. Please read the latest post in Announcements and News

I strongly advise all users using my server for their web site to make alternative arrangements.

Outgoing mail is disabled because of the malware on the server. No new forum registrations are currently possible, and mail to the forum administrator will not work.

Please read the posts in the Announcements section about the current status of Cumulus development now that I have retired

Please read this post before posting

Latest Cumulus release v1.9.4 (build 1099) - Nov 28 2014
Latest Cumulus MX release - v3.0.0 build 3043 Jan 20 2017. See this post for download

SQL dayfile data Viewer

Other discussion about creating web sites for Cumulus that doesn't have a specific subforum

Moderators: daj, TNETWeather

User avatar
kocher
Posts: 67
Joined: Sat Apr 19, 2014 7:57 pm
Weather Station: Davis Vantage Pro2+
Operating System: Windows 10
Location: San Sebastian - Spain
Contact:

SQL dayfile data Viewer

Postby kocher » Sun Jan 22, 2017 12:28 pm

Here is another way to see our daily data taken from the table, created by cumulusMX, "dayfile".

Demo: http://kocher.es/cumulusMX/datatables/examples/server_side/dayfile-sql.php

This is possible thanks to the plugin created by https://www.datatables.net/

The advantages of this presentation in table form are:
- Speed. The query to our dayfile table is very fast.
- The columns are rearranged at will by simply pressing on a column heading.
- The last row of the table, presents a summary of the rows (The minimum temperature value, sometimes fails).
- The variables stand out with a color gradient, taken as a function of their value.
- The search is done in the format yyyy-mm-dd (the format of the field LogDate):

2016-10 filters the month of October 2016
-08-15 filters every day August 15th of every year

Installation:
- Download file: http://kocher.es/cajon/DataTables-cumulus-dayfile.rar (2221 KB)
- Unzip this file
- Install your content on our website, in a folder named as we want.

-Edit the file:

./examples/server_side/scripts/server_processing.php

Line 22: $ table = 'dayfile'; // sometimes called Dayfile

Lines 80-84:

Code: Select all

'User' => 'Our username',
'Pass' => 'Our password',
'Db' ​​=> 'Database name'
'Host' => 'The address of our mysql hosting'


Now, we call from our browser, to file:

../examples/server_side/dayfile-sql.php

I hope you find it useful :)
Last edited by kocher on Mon Jan 23, 2017 1:21 am, edited 4 times in total.
Image

User avatar
PaulMy
Posts: 1680
Joined: Sun Sep 28, 2008 11:54 pm
Weather Station: Davis Vantage Pro 2 w/Envoy USB
Operating System: Windows-8
Location: Komoka, ON Canada
Contact:

Re: SQL dayfile data Viewer

Postby PaulMy » Sun Jan 22, 2017 3:52 pm

Very nice, and I am learning Spanish (not) http://www.komokaweather.com/mysql/data ... le-sql.php and it goes back to 2008-10

Thank you for sharing.

Enjoy,
Paul

User avatar
Mapantz
Posts: 497
Joined: Sat Dec 17, 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 10 x64 - A beast.
Location: Wareham, Dorset - UK
Contact:

Re: SQL dayfile data Viewer

Postby Mapantz » Sun Jan 22, 2017 4:58 pm

Nice work! I'll have a look later this evening.

You're right about how fast it is.. I typed 2016-01, 2016-02, 2016-03 etc etc.. and it is almost instantaneous. Great job. :)
Image
Image

User avatar
grwkak
Posts: 83
Joined: Fri Jun 28, 2013 12:32 am
Weather Station: Davis Vantage Vue
Operating System: Windows 10
Location: Hambden, Ohio US
Contact:

Re: SQL dayfile data Viewer

Postby grwkak » Sun Jan 22, 2017 6:03 pm

Also playing - like the speed and format.

http://wilsonsweather.host-ed.me/DataTa ... le-sql.php

Doing some conversion to English and Imperial units. Color codes will need adjusting. Not sure why but getting a NaN in the windrun sum.

Guy

User avatar
Mapantz
Posts: 497
Joined: Sat Dec 17, 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 10 x64 - A beast.
Location: Wareham, Dorset - UK
Contact:

Re: SQL dayfile data Viewer

Postby Mapantz » Mon Jan 23, 2017 1:03 am

Something missing from the package.. ;)

dataTables.scroller.js


Install that to: ../examples/resources

The table will now list the entire contents (pretty fast, I might add) which you can scroll through. It also automatically updates the text: Displaying 1 - 14 days from 328 days to show you exactly how many days are on view at a given time. :P

http://www.warehamwx.co.uk/cumulus/dayfile_sql.php
You do not have the required permissions to view the files attached to this post.
Last edited by Mapantz on Tue Oct 03, 2017 1:17 am, edited 2 times in total.
Image
Image

User avatar
PaulMy
Posts: 1680
Joined: Sun Sep 28, 2008 11:54 pm
Weather Station: Davis Vantage Pro 2 w/Envoy USB
Operating System: Windows-8
Location: Komoka, ON Canada
Contact:

Re: SQL dayfile data Viewer

Postby PaulMy » Mon Jan 23, 2017 4:37 am

Thanks kocher, I really like this. And doing the full db as per Mapantz update is great, thanks for that. This is something I always wished for but don't have the ability to do by myself.

Just a couple of interesting facts on my 3,013 days of data:
Of these 3013 days of data there were 1,119 days that had a low of below 0.0°C (days of frost)
Of the 3,013 days there were 400 days that had a high that was below 0.0°C (days that had frost all day).

Now if I could figure out how to add or show more of the dayfile.txt data, i.e. sunshine, heating and cooling degree days...

Enjoying,
Paul

User avatar
kocher
Posts: 67
Joined: Sat Apr 19, 2014 7:57 pm
Weather Station: Davis Vantage Pro2+
Operating System: Windows 10
Location: San Sebastian - Spain
Contact:

Re: SQL dayfile data Viewer

Postby kocher » Mon Jan 23, 2017 1:54 pm

I would like to add more variables to our table, but the reality is that I can not.
In the file "server_processing.php" yes, you can incorporate all the fields of the table dayfile;
but when you add any of them to the "dayfile-sql.php" file, everything stops working :groan:
Last edited by kocher on Thu Jan 26, 2017 10:13 pm, edited 1 time in total.
Image

User avatar
Mapantz
Posts: 497
Joined: Sat Dec 17, 2011 11:55 am
Weather Station: Davis Vantage Pro2
Operating System: Windows 10 x64 - A beast.
Location: Wareham, Dorset - UK
Contact:

Re: SQL dayfile data Viewer

Postby Mapantz » Mon Jan 23, 2017 1:59 pm

kocher wrote:I would like to add more variables to our table, but the reality is that I can not.
In the file "server_processing.php" yes you can incorporate all the fields of the table dayfile;
but when you add any of them to the "dayfile-sql.php" file, everything stops working :groan:


I don't really have enough brain power to do much to it. I could probably add solar radiation, as that would just about fit in to the table when using my custom widths. I suspect that you would need a horizontal scrollbar to fit in more contents such as humidity/degree days/dew point etc..

Maybe someone would be able to expand on it in the future, as it is a very useful script imo. :)
Image
Image

User avatar
grwkak
Posts: 83
Joined: Fri Jun 28, 2013 12:32 am
Weather Station: Davis Vantage Vue
Operating System: Windows 10
Location: Hambden, Ohio US
Contact:

Re: SQL dayfile data Viewer

Postby grwkak » Wed Jan 25, 2017 11:20 pm

Here is my php file adapted to US/imperial units. The color scales are adapted to the range of weather I see here in NE Ohio.
I do not know much about php etc so it could do with some cleaning up but it seems to work. Not sure about the pressure - may need to redo the colors.

Guy

dayfile-sql.zip
You do not have the required permissions to view the files attached to this post.

User avatar
HRVistaWeather
Posts: 172
Joined: Mon Apr 09, 2012 2:38 pm
Weather Station: Davis VP2 Plus - 24hr FARS
Operating System: Windows 10 Home (64bit)
Location: Franklin, Huon Valley, Tasmania
Contact:

Re: SQL dayfile data Viewer

Postby HRVistaWeather » Fri Jan 27, 2017 11:09 pm

Thank you greatly Kocher and Mapantz.
I have adapted it to my Saratoga Template.
sfdayfilesql.png

Kind Regards,
You do not have the required permissions to view the files attached to this post.
Tony
Huon River Vista - South Franklin Weather - Tasmania
Davis Vantage Pro 2 Plus - FARS, Cumulus MX, 2 Soil Temp/Moist Solar/Rad.
Image

92merc
Posts: 39
Joined: Wed Feb 15, 2017 6:07 pm
Weather Station: Davis VP2
Operating System: Windows 10 Pro
Location: Bismarck, ND USA
Contact:

Re: SQL dayfile data Viewer

Postby 92merc » Thu Mar 23, 2017 5:29 pm

Digging up an old thread here. I was able to get the dayfile scripts configured and uploaded. 2 issues hopefully someone can help with.

Can the Date format be changed to MM/DD/YYYY?

My totals at the bottom are in US, but the labels inside are coming up with metric instead of US. The numbers are right, just the labels are wrong.

Thanks.

http://www.bismarckweather.net/dayfile/ ... le-sql.php

User avatar
kocher
Posts: 67
Joined: Sat Apr 19, 2014 7:57 pm
Weather Station: Davis Vantage Pro2+
Operating System: Windows 10
Location: San Sebastian - Spain
Contact:

Re: SQL dayfile data Viewer

Postby kocher » Thu Mar 23, 2017 8:02 pm


Can the Date format be changed to MM/DD/YYYY?



To change the date format. You must edit the file: "./server_side/scripts/server_processing.php"

Approximately, on line 39, you must change:

Code: Select all

      'formatter' => function( $d, $row ) {
         return date( 'Y/m/j', strtotime($d));
         


to

Code: Select all

      'formatter' => function( $d, $row ) {
         return date( 'm/j/Y', strtotime($d));
Image

92merc
Posts: 39
Joined: Wed Feb 15, 2017 6:07 pm
Weather Station: Davis VP2
Operating System: Windows 10 Pro
Location: Bismarck, ND USA
Contact:

Re: SQL dayfile data Viewer

Postby 92merc » Thu Mar 23, 2017 8:37 pm

Thank you. And I saw my labels in the PHP file as well that I updated.

Warrandyte
Posts: 41
Joined: Sat Apr 06, 2013 11:46 pm
Weather Station: Fine Offset with Solar
Operating System: Windows XP, 7 8 and 10
Location: TIrrannaville NSW Australia

Re: SQL dayfile data Viewer

Postby Warrandyte » Tue May 23, 2017 11:17 am

Mapantz wrote:Something missing from the package.. ;)

dataTables.scroller.js

Install that to: ../examples/resources

The table will now list the entire contents (pretty fast, I might add) which you can scroll through. It also automatically updates the text: Displaying 1 - 14 days from 328 days to show you exactly how many days are on view at a given time. :P

http://www.warehamwx.co.uk/cumulus/dayfile-sql.php

Thanks. Done a great job on the site. Have you templated your creation?
Rob

Hunter362
Posts: 82
Joined: Tue Oct 19, 2010 12:20 am
Weather Station: Ambient Weather WS-2080
Operating System: Windows 7 - Ubuntu 14.4
Location: Fairmount, New York (USA)
Contact:

Re: SQL dayfile data Viewer

Postby Hunter362 » Sun Jul 23, 2017 2:37 pm

Where did you change the "highlight" code for the rows? I can't seem to find the right one.


Return to “Web site - General”

Who is online

Users browsing this forum: No registered users and 2 guests