Paul
Since you are using Cumulus 1, rather than MX, I reckon you should use a query greatly simplified from the one you quote first.
If instead of naming multiple weather columns (e.g. Max, Average, and Min temperatures) in a single query, you have one in each of three queries, then you can more easily pick out from the results array the value relevant to the particular table.
Also you want to select by day of year,
https://dev.mysql.com/doc/refman/5.7/en ... _dayofyear and order by that then by year.
Code: Select all
$q1 = "SELECT DATE_FORMAT(LogDate, '%e %b'), // this formats the row label as day of month (without leading zero) and month (3 letter abbreviation)
$columnName, // this one variable can contain one or two column names
DAYOFYEAR(LogDate) AS d1, // My d1 expression will define the row to put value in
YEAR(logdate) AS y1 // My y1 was introduced to determine which column to put value in
FROM $dayFileTableName
WHERE MONTH(LogDate) = $monthToDisplay
ORDER BY d1 ASC, y1 DESC"; // sort by row and within row by column
Ordering by day of year means that your output array is arranged by the rows you want for your eventual output and the second level of ordering gives you the table cells within those rows. If you continue to struggle, I might find time to produce more, but now I am gardening when at home and out most days each week. Also I reverted to an old version of SQL as I had a few problems with the latest (and can't understand how Mark resolved them a few years ago with e.g. top ten script), and I don't recall which version you use.
There is I believe somewhere on the forum a data summary script that shows values for each day on a month by month basis with 31 rows based on a database approach, your two versions of it both use JavaScript reading dayfile.txt. But in theory you could alternatively just modify that to display 366 rows and change the month columns into year columns.
My daily summary table in my database uses a different schema to that used by Mark (that I assume you have implemented, and I know that was adopted by Cumulus MX), so any script I already have for extracting statistics from it would not be ready for you to adapt.
In case anyone is interested, my schema is:
Schema has been edited from as originally posted - partly to make my new web page - see later post - work better and partly because I have changed it as part of a re-examination of my whole design
CREATE TABLE $dayFileTableName ( `MaxRainRate` decimal(4,1) DEFAULT NULL, `TMaxRainRate` varchar(5) DEFAULT NULL, `HighHourRain` decimal(4,1) DEFAULT NULL, `THighHourRain` varchar(5) DEFAULT NULL, `MaxRainFall` decimal(6,2) DEFAULT NULL, `TMaxRainFall` varchar(5) DEFAULT NULL COMMENT 'From`today.ini`, null if no rain', `SnowFalling` tinyint(1) DEFAULT NULL COMMENT 'from Cumulus Weather Diary', `SnowLying` tinyint(1) DEFAULT NULL COMMENT 'from Cumulus Weather Diary', `SnowDepth` smallint(6) UNSIGNED DEFAULT NULL COMMENT 'from Cumulus Weather Diary (in mm)', `Entry` varchar(500) DEFAULT NULL COMMENT 'from Cumulus Weather Diary', `TotChillHours` decimal(4,2) DEFAULT NULL COMMENT 'Calculated value, no daily value in Cumulus', `CumChillHours` decimal(6,2) DEFAULT NULL COMMENT 'From`today.ini`', `MinTemp` decimal(5,1) DEFAULT NULL, `TMinTemp` varchar(5) DEFAULT NULL, `TotHeatDegDays` decimal(4,1) DEFAULT NULL, `AvgTemp` decimal(4,2) DEFAULT NULL, `MaxTemp` decimal(5,1) DEFAULT NULL, `TMaxTemp` varchar(5) DEFAULT NULL, `TotCoolDegDays` decimal(4,1) DEFAULT NULL, `LogDate` date NOT NULL COMMENT 'format yyyy-mm-dd, not format in `dayfile.txt`', `LowDewPoint` decimal(4,1) DEFAULT NULL, `TLowDewPoint` varchar(5) DEFAULT NULL, `LowHum` decimal(4,1) DEFAULT NULL, `TLowHum` varchar(5) DEFAULT NULL, `HighHum` decimal(4,1) DEFAULT NULL, `THighHum` varchar(5) DEFAULT NULL, `HighDewPoint` decimal(4,1) DEFAULT NULL, `THighDewPoint` varchar(5) DEFAULT NULL, `GreatWindChill` decimal(4,1) DEFAULT NULL, `TGreatWindChill` varchar(5) DEFAULT NULL, `LowAppTemp` decimal(4,1) DEFAULT NULL, `TLowAppTemp` varchar(5) DEFAULT NULL, `HighAppTemp` decimal(4,1) DEFAULT NULL, `THighAppTemp` varchar(5) DEFAULT NULL, `HighHeatInd` decimal(4,1) DEFAULT NULL, `THighHeatInd` varchar(5) DEFAULT NULL, `MinPress` decimal(6,2) DEFAULT NULL, `TMinPress` varchar(5) DEFAULT NULL, `MaxPress` decimal(6,2) DEFAULT NULL, `TMaxPress` varchar(5) DEFAULT NULL, `HighAvgWSpeed` decimal(3,1) DEFAULT NULL, `THighAvgWSpeed` varchar(5) DEFAULT NULL, `StrongestWindGust` decimal(4,1) DEFAULT NULL, `TStrongestWindGust` varchar(5) DEFAULT NULL, `BearStrongestWindGust` smallint(3) UNSIGNED ZEROFILL DEFAULT NULL, `BearStrongestWindGustSym` varchar(3) DEFAULT NULL COMMENT 'calculated compass direction, `BearDomWind` smallint(3) UNSIGNED ZEROFILL DEFAULT NULL, `BearDomWindSym` varchar(3) DEFAULT NULL COMMENT 'calculated compass direction', `TotWindRun` decimal(5,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Daily Summary Log (from Cumulus today.ini, log.xml, and dayfile.txt)';
Using the prefix 'T' for time-stamps in front of the column name for the corresponding value means my scripts can easily pick out the time-stamp to associate with a value. All statistics that represent daily totals start with 'Tot', this makes it easy for retrieval scripts to detect those without time-stamps. Although total daily rain was originally 'TotRainFall' following that system, I changed it to 'MaxRainFall' when I decided to record the time of last rain tip in 'TMaxRainFall'.
As my comments indicate I add new rows to my table by reading three Cumulus log files, and you will note that I have picked a column order that groups statistics about particular weather phenomena together, rather than following the order in which the various fields were added to dayfile.txt as Steve developed Cumulus 1. My mix of 'Strong', 'High' and 'Max' prefixes is however a relic of some longer more descriptive text column names in earliest design (trying to separate directly read from calculated values), now column names are shorter but I also have a corresponding set of caption names.
Having 'LogDate' in the middle makes use of phpMyAdmin easier as I can still see the date as I scroll sideways.
FWIW, I do also have a monthly summary table, that has one row per month, and can be entirely populated from my daily summary table (yes I know I have broken rules of database normalisation), but for unfinished months I can use Cumulus monthly web tag outputs to populate some of it. I use this for variants on various standard Cumulus web templates e.g. displaying consecutive months, or months from different years imitating the thismonthT.htm web page but in a multi-month extended way.