Welcome to the Cumulus Support forum.

Latest Cumulus MX release 3.9.1 (build 3096) - 31 October 2020 (please see announcement regarding releases since 3.5.0)
Legacy Cumulus 1 release v1.9.4 (build 1099) - 28 November 2014 (a patch is available for 1.9.4 build 1099 that extends the date range of the NOAA report and Snow Index drop-down menus to 2030)

Use this Wiki link to Download the Software (Cumulus MX / Cumulus 1 and other related items).

MySQL code help

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

Moderator: daj

Post Reply
User avatar
PaulMy
Posts: 2295
Joined: Sun 28 Sep 2008 11:54 pm
Weather Station: Davis VP2+ Cumulus1 / CummulusMX
Operating System: Windows8 / Windows10
Location: Komoka, ON Canada
Contact:

MySQL code help

Post by PaulMy »

I have virtually no knowledge about MySQL but with considerable help here in the past I had been able to create some scripts from my MySQL Dayfile DB
Monthly temp http://www.komokaweather.com/mysql/monthly-temp.php
Monthly data http://www.komokaweather.com/mysql/monthly-data.php
Today to previous http://www.komokaweather.com/weather/maxmin.php
This date http://www.komokaweather.com/weather/thisdate.php

I have been trying to create a new script that would have a table of each day's temp for the 10 years of my data -
Year as column header (11 columns from 2008 to 2018)
Each day's date in the left column (365 lines or 366 in leap year) possibly beak it down in monthly blocks
Each day's high temp filled in the table

I would then do the same for low temp and average temp.

Unfortunately my attempts at deciphering and editing the MySQL code from those working scrips has not given me any results for my new script idea :bash:

I have this code in my This Date script

Code: Select all

	// the actual query for the grid data the query displays date for
	$q1 = "SELECT year(LogDate) as year, month(LogDate) as month, day(LogDate) as day, LogDate, MinTemp, LowAppTemp, LowWindChill, MaxTemp, HighAppTemp, round(AvgTemp,1) as AvgTemp, MinPress, MaxPress, HighWindGust, TotWindRun, round(TotRainFall,1) as TotRainFall, round(HoursSun,1) as HoursSun  FROM Dayfile WHERE day(LogDate)=day(now()) and month(LogDate)=month(now())  ORDER BY year(LogDate) DESC";
	$result1 = mysql_query($q1)or die('Error: ' . mysql_error());
And in my Maxin

Code: Select all

		// the actual query for the grid data 
		$SQL = "SELECT LogDate,  Year(LogDate) as Year, maxtemp, max(IF(month(logdate)=1, maxtemp,null)) as January, max(IF(month(logdate)=2, maxtemp,null))as February, max(IF(month(logdate)=3, maxtemp,null))as March, max(IF(month(logdate)=4, maxtemp,null))as April, max(IF(month(logdate)=5, maxtemp,null))as May, max(IF(month(logdate)=6, maxtemp,null))as June, max(IF(month(logdate)=7, maxtemp,null))as July, max(IF(month(logdate)=8, maxtemp,null))as August ,max(IF(month(logdate)=9, maxtemp,null))as September,max(IF(month(logdate)=10, maxtemp,null))as October, max(IF(month(logdate)=11, maxtemp,null))as November, max(IF(month(logdate)=12, maxtemp,null))as December, max(maxtemp) as total FROM Dayfile  group by year";
		$resultmaxtemp = mysql_query($SQL)or die('Error: ' . mysql_error()); 
Any code suggestion for obtaining the temp for each day and year?

Thanks,
Paul

jlmr731
Posts: 166
Joined: Sat 27 Aug 2016 12:11 am
Weather Station: Davis vantage pro 2
Operating System: Debian
Location: Youngstown, Ohio
Contact:

Re: MySQL code help

Post by jlmr731 »

I have something like that but I use the monthly tables instead of the dayfile table. Once you have the mysql query its then all about formatting to your page.
Here is an example that just prints out date min max avg temps for that day using the dayfile table (the one cumulusMX made and writes to)

Code: Select all

SELECT DATE_FORMAT(LogDate, '%m/%d/%Y') as u_date,MinTemp, MaxTemp, AvgTemp   FROM `Dayfile`
Now this will break it down to separate year month day

Code: Select all

SELECT DATE_FORMAT(LogDate, '%m/%d/%Y') as u_date, DATE_FORMAT(LogDate, '%Y') as year, DATE_FORMAT(LogDate, '%m') as month, DATE_FORMAT(LogDate, '%d') as day,MinTemp, MaxTemp, AvgTemp FROM `Dayfile`
so you can try something like this (will need some tweaking not sure if i have the table layout perfect)

Code: Select all

<?php
	

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT DATE_FORMAT(LogDate, '%m/%d/%Y') as u_date, DATE_FORMAT(LogDate, '%Y') as year, DATE_FORMAT(LogDate, '%m') as month, DATE_FORMAT(LogDate, '%d') as day, MinTemp, MaxTemp, AvgTemp FROM Dayfile";

$result = $conn->query($sql);
$col = 0;


	if ($result->num_rows > 0) {
	echo " <tbody><tr><th>Year</th><th>Month</th><th>Day</th><th>Max Temp</th><th>Min Temp</th><th>Avg Temp</th></tr>";
    // output data of each row
   	 while($row = mysqli_fetch_array($result)) {	
$years = $row['years'];
		if ($col == 1) {     //this will be how many columns you want
			echo "</tr><tr>";
			$col = 0;
		}
 echo "<table><td>";
	echo " .$row["year"]. "</td><td>" .$row["month']. "</td><td>" .$row["day"].;
	echo 	"</td><td class=red>" . $row["maxtemp"]. "</td><td class=blue>" . $row["mintemp"]. "</td><td class=green>" . $row["avgtemp"]."</td><td></td>";
$col++;
    }
echo "</tr></tbody></table>";
} else {
    echo "0 results";
}
$conn->close();
?>

That will hopefully get you on the right track atleast

sfws
Posts: 931
Joined: Fri 27 Jul 2012 11:29 am
Weather Station: --
Operating System: --

Re: MySQL code help

Post by sfws »

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.
Last edited by sfws on Mon 30 Apr 2018 9:40 am, edited 5 times in total.

User avatar
PaulMy
Posts: 2295
Joined: Sun 28 Sep 2008 11:54 pm
Weather Station: Davis VP2+ Cumulus1 / CummulusMX
Operating System: Windows8 / Windows10
Location: Komoka, ON Canada
Contact:

Re: MySQL code help

Post by PaulMy »

Thanks for the suggestions. I have experimented some more, and also looked at the MySQL documentation https://dev.mysql.com/doc/refman/5.7/en/examples.html but not ready yet to enroll in a MySQL credit course... my head feels like :bash:

What I have so far is http://www.komokaweather.com/weather/th ... test1e.php which has several issues and still a long way to go:
I would want one row for say April 7 instead of separate for April 7 each year
and the year's data should go under each of the applicable Year heading only.

I presume this code needs to be expanded

Code: Select all

	// the actual query for the grid data the query displays date for
	$q1 = "SELECT 
	year(LogDate) as year, 
	month(LogDate) as month, 
	day(LogDate) as day, 
	LogDate, MinTemp  
	FROM Dayfile
	
	ORDER BY month(LogDate), day(logdate) DESC";
as well as this code so that the specific day's data will display under the specific year.

Code: Select all

			<!-- Display the Past Years' data from MySQL Dayfile database -->
			<tr>
				<td style="width: 10px; text-align:right;"><?php echo $row->LogDate; ?></td>
				<td style="width: 10px; text-align:right;"><?php echo $row->MinTemp; ?></td>
				<td style="width: 10px; text-align:right;"><?php echo $row->MinTemp; ?></td>
				<td style="width: 10px; text-align:right;"><?php echo $row->MinTemp; ?></td>
				<td style="width: 10px; text-align:right;"><?php echo $row->MinTemp; ?></td>
				<td style="width: 10px; text-align:right;"><?php echo $row->MinTemp; ?></td>
				<td style="width: 10px; text-align:right;"><?php echo $row->MinTemp; ?></td>
				<td style="width: 10px; text-align:right;"><?php echo $row->MinTemp; ?></td>
				<td style="width: 10px; text-align:right;"><?php echo $row->MinTemp; ?></td>
				<td style="width: 10px; text-align:right;"><?php echo $row->MinTemp; ?></td>
				<td style="width: 10px; text-align:right;"><?php echo $row->MinTemp; ?></td>
				<td style="width: 10px; text-align:right;"><?php echo $row->MinTemp; ?></td>
			</tr>
but don't know how!

Paul

sfws
Posts: 931
Joined: Fri 27 Jul 2012 11:29 am
Weather Station: --
Operating System: --

Re: MySQL code help

Post by sfws »

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, and order by that then by year.
You totally ignored everything I said. I gave you my idea for column selection, my ordering suggestion, and said it was a very simplified query. I have now had time to test it for myself, so it is here.

Code: Select all

<section id="container">
	<?php
	for($monthToDisplay = 1; $monthToDisplay <= 12; $monthToDisplay++)
	{	
			
	?>
		
	<details>
		<summary>Click here to open or close <?php echo $monthArray[$monthToDisplay - 1]; ?> table</summary>
		<table class='titre'>  <!-- ignore my class names, they are just to give a good look, you choose what you want where I have named a class -->
			<thead>
				<tr>
					<th>Date</th>
					<th>2018</th> <!--  For ease of setting up I have hard coded years, but you should be able to arrange better way of deriving years -->
					<th>2017</th>
					<th>2016</th>
					<th>2015</th>
					<th>2014</th>
					<th>2013</th>
					<th>2012</th>
					<th>2011</th>
					<th>2010</th>
					<th>2009</th>
				</tr>
				<colgroup> <!-- used to make each column have different look, well worthwhile -->
					<col class='label'>
					<col class='b_LH'>
					<col class='b_MID'>
					<col class='b_RH'>
					<col class='b_LH'>
					<col class='b_MID'>
					<col class='b_RH'>
					<col class='b_LH'>
					<col class='b_MID'>
					<col class='b_RH'>
					<col class='b_LH'>
				</colgroup>
			</thead>
			<tbody> 
	<?php
		$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
								. " DATE_FORMAT(LogDate, '%m%d') 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
						 // I found that DAYOFYEAR did not work, so use my d1 expression instead
				
			$result = $con->query($q1);
			if (!$result) die ('query failure');
			$rowLabel = null;
			$rowEnd = null;
			
			while ($items = $result->fetch_array())
			{				
				#	print_r($items);  // This allows you to see results from query, I have commented it out
					if($items[0] != $rowLabel) // if row header changes, need to start new row
					{
							echo $rowEnd . "\n";  // end previous row
							$colStart = '2018'; // set to whatever is latest year
							echo '<tr><th>' . $items[0] . '</th>'; // output row label
							$rowLabel = $items[0]; // reassigns row label, so can detect when it changes and need new row
							$rowEnd = '</tr>'; // redefine, as now have started a row
					} 
					if($items['y1'] == $colStart) // check if result is for the next table cell
					{
							if($items[1] > 0)
									echo '<td>' . $items[1] . '</td>'; // output column value
							else
									echo '<td class="zerovalue">' . $items[1] . '</td>'; // output column value
							$colStart--; // decrement year as move to next table cell
					}else{							
							for($i = $colStart; $i > $items['y1']; $i--) // loop through years with no value
							{
								echo '<td>n/a</td>'; // content just so you can see it working
								$colStart--;// decrement year as move to next table cell
							}
							echo '<td>' . $items[1] . '</td>'; // output column value
							$colStart--;// decrement year as move to next table cell
							
					}
					$rowEnd = '</tr>'; // redefine, as now have started a row
			}
			echo $rowEnd . "\n";  // end previous row 
			mysqli_free_result($result);  # Free result set
	?>
			</tbody>
		</table>
		</details>
	<?php
	}
	?>
	</section><!-- end container -->

Here it is in operation on my PC, with a few extras to what shown above!
New web page (closed).PNG
New web page (open).PNG
Is this virtually what you want?

The URL I gave was simply for 'DAYOFYEAR' syntax (and I did not use that in the end - see note in code above), if you want SQL examples, better to go to a simpler tutorial e.g. https://www.w3schools.com/sql/default.asp
You do not have the required permissions to view the files attached to this post.
Last edited by sfws on Fri 13 Apr 2018 12:05 pm, edited 2 times in total.

User avatar
PaulMy
Posts: 2295
Joined: Sun 28 Sep 2008 11:54 pm
Weather Station: Davis VP2+ Cumulus1 / CummulusMX
Operating System: Windows8 / Windows10
Location: Komoka, ON Canada
Contact:

Re: MySQL code help

Post by PaulMy »

You have accomplished what I had envisioned, and even more. Well done!
I haven't had a lot of time the past few days so very little progress on my efforts in cobbling up from my other scripts
http://www.komokaweather.com/weather/th ... test1h.php
but this is not yet correct, and I would gladly abandon this approach. But I have tried the other methods but that failed miserably for me :oops: . I would greatly appreciate looking at your script.

Thanks,
Paul

Post Reply