Welcome to the new home of the Cumulus Support forum.

Latest Cumulus release v1.9.4 (build 1099) - Nov 28 2014
Latest Cumulus MX release - v3.0.0 build 3044 7 December 2018. See the Wiki for download

Top 10 Records Page

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

Moderator: daj

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

Re: Top 10 Records Page

Post by Hunter362 » Wed 15 Aug 2018 3:55 am

I know this is an old thread, but am moving my server to Ubuntu 16.04, with php 7
am having a heck of a time reading anything from my Mysql Database.

ERROR - Bad Select Statement (1) -
SELECT LogDate, ROUND(HeatDegDays, 1), Logdate >= DATE_SUB('2018-08-13', INTERVAL 32 DAY) FROM DayfileMX WHERE HeatDegDays IS NOT NULL ORDER BY HeatDegDays DESC, LogDate ASC LIMIT 10
Unknown column 'HeatDegDays' in 'field list'

The above query works fine and displays what it should in phpmyadmin, but on mark's most recent Top10 script thats the error I get.

User avatar
mcrossley
Posts: 5234
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Top 10 Records Page

Post by mcrossley » Wed 15 Aug 2018 7:37 pm

Do all the queries up to that point work OK?

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

Re: Top 10 Records Page

Post by Hunter362 » Thu 16 Aug 2018 1:33 am

Hi Mark,
It has to be the different mysql version's, not just your script.
My site is currently running Ubuntu 14.04 - mysql v 5.5.61
New server is runing Ubuntu 16.04 - mysql v 5.7.23

It seems to be the change in mysql query's and such, changes from "mysql" to "mysqli" and other's, there's a whole bunch
of different things in your most recent Top10 page, then the one I'm currently using, 0.17
I only have a couple scripts that use the mysql datbase, your Top10 and a previous year's one that I cobbled together,
if you saw it you'd laugh! php or mysql guru I'm not, but I can usually make it work eventually, just hoping that you had a somewhat
easier fix.
P.S. I am able to log into the server fine using the script, I had to add a $table_name parm as you have, it has to be the select and display and such.

Import Cumulus v 3.0
Running from web server
Script start: 15/08/18 - 21:50:22
Importing file: dayfile.txt, To table: Dayfile ...
Connected to database OK.
Set MySQL TZ OK.
Processing dayfile...
Target table already exists.
ERROR - Failed to prepare the insert statement: Dayfile, error: Unknown column 'HighHeatInd' in 'field list'
INSERT IGNORE INTO Dayfile (LogDate,HighWindGust,HWindGBear,THWindG,MinTemp,TMinTemp,MaxTemp,TMaxTemp,MinPress,TMinPress,MaxPress,TMaxPress,MaxRainRate,TMaxRR,TotRainFall,AvgTemp,TotWindRun,HighAvgWSpeed,THAvgWSpeed,LowHum,TLowHum,HighHum,THighHum,TotalEvap,HoursSun,HighHeatInd,THighHeatInd,HighAppTemp,THighAppTemp,LowAppTemp,TLowAppTemp,HighHourRain,THighHourRain,LowWindChill,TLowWindChill,HighDewPoint,THighDewPoint,LowDewPoint,TLowDewPoint,DomWindDir,HeatDegDays,CoolDegDays,HighSolarRad,THighSolarRad,HighUV,THighUV,HWindGBearSym,DomWindDirSym) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
End.

User avatar
mcrossley
Posts: 5234
Joined: Thu 07 Jan 2010 9:44 pm
Weather Station: Davis VP2
Operating System: Stretch Lite rPi
Location: Wilmslow, Cheshire, UK
Contact:

Re: Top 10 Records Page

Post by mcrossley » Thu 16 Aug 2018 6:41 pm

Ah, so it's the importcumulus script that had the error, not the top 10s. I recommend you use the last 2.x version, rather than 3.x as that seems to have issues for some people that I haven't got to the bottom of yet. But it looks like your tables were defined some time ago and new columns have been added since.
Just add the new columns using phpmyadmin

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

Re: Top 10 Records Page

Post by Hunter362 » Thu 16 Aug 2018 8:06 pm

Sorry, I should have said the ImportCumulus file does the same thing, I'll try an earlier version and see if it works, I still get the error with the Top10
but I believe it's the newer (for me) mysql 5.7
** Edit: for the record ImportCumulus V.27 worked as it should, thanks for the advice. **

ERROR - Bad Select Statement (1) -
SELECT LogDate, ROUND(HeatDegDays, 1), Logdate >= DATE_SUB('2018-08-15', INTERVAL 32 DAY) FROM Dayfile1 WHERE HeatDegDays IS NOT NULL ORDER BY HeatDegDays DESC, LogDate ASC LIMIT 10
Unknown column 'HeatDegDays' in 'field list'

Above is the error from v 0.17
This is from v 2.1 using a different named database, same (dayfile data) just the one written directly by MX

ERROR - Bad Select Statement (4) -Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'weather.DayfileMX.LogDate' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

sfws
Posts: 462
Joined: Fri 27 Jul 2012 11:29 am
Weather Station: Customised mix of Fine Offset
Operating System: Tempermental Win. 10 PC
Location: UK

Re: Top 10 Records Page

Post by sfws » Sat 24 Nov 2018 5:17 pm

Please note that I have stopped monitoring the forum (and PMs), because I am extremely busy preparing for moving out of this home, and after approximately ten years of collecting weather station statistics at this location I expect to be ceasing soon. I have probably done all the web page designing and experimenting I need to do for now (although I did recently get my PC back after it had been away for 2 weeks being repaired); I will plan for different hobbies in my new location (yet to be selected, it is my turn for increased exploring first). If anyone has any queries about anything I have posted in the past, then you may be lucky enough that others might help, otherwise you are on your own. Once I considered sharing some of my batch scripts, these add useful automation to Cumulus 1, including updating database tables on an end-of-day basis. My tables contain a large number of columns as well as rows because my schema is greatly extended from that originally designed by David Jamieson and now in MX. One table contains daily summary (1 row per day) and the other contains monthly summary (1 row per month). I suspect Cumulus MX has similar capability although implemented differently so perhaps no-one would have been interested, anyway my scripts are very specific to my set-up and I never got around to producing sharable versions.

I thought I would however treat you to one final visual contribution, as final thanks for all the help I have received on this support forum:
Weather Top Ten Statistics My Weather.zip
It is an edited snapshot of the top ten web page that I have (offline) I have output it in HTML rather than picture format, so you need to be logged on to download it. I was recently impressing myself looking at my original page (because it has almost reached 10 years every sub-table is full now). My version of Mark's script is based on my schema (I believe that was disclosed in at least one previous post), so it includes reading database columns that as far as I know nobody else has included, so the content coverage is pretty extensive. To avoid any comments about my styling, this file has none, and I have also removed the navigation menu that listed all my other scripts and web pages and also removed the footnotes that are relevant only to my set-up. Please note I do not include the script that produces this web page because it is my interlectual property, but my earlier postings in this thread disclose how much dependence on Mark's work there was, but there are some novel queries that I have added, and my presentation of dates and times is different to that chosen by Mark.
Possibly, this will inspire others to try new queries in this useful multi-year summary?
Good-bye.
You do not have the required permissions to view the files attached to this post.

Post Reply