Alltime yearly Rainfall webtag

[mcrossley]

I have reached another stumbling block in the wettest & driest months as well as the hottest and coolest months, as these clearly need to have a lot more data compared to work out which is the correct answer.

So for wettest month in a year I'd use something llike:

Code: Select all

SELECT month( LogDate ) AS MONTH , 
sum( TotRainFall ) AS total
FROM `daydata` 
WHERE year( LogDate ) = 2012
GROUP BY month( LogDate ) 
ORDER BY total DESC 
LIMIT 1

Or if you want highest rainfall month for each year:

Code: Select all

SELECT year, month, total
FROM (
SELECT year( LogDate ) AS year, month( LogDate ) AS month, sum( TotRainFall ) AS total
FROM `daydata` 
GROUP BY year, month
ORDER BY total DESC 
) AS tmp
GROUP BY year

But Kerry is the SQL whizz and probably has a more elegant solution;)

[tobyspond]

I'd probably set the queries up the same way . And if you want consecutive days see https://cumulus.hosiene.co.uk/viewtopic.php?f=14&t=2608.

Kerry

[bigmac]

Aww, c'mon guys, you made that too easy for me! I was expecting at least a week of tearing my hair out trying to work it out!
I have now succeeded in completing those stats (unless someone points out a fatal flaw in what I have done!). I suspect I haven't done it the prettiest of ways, but it works for me.

Thank you Kerry and Mark for all your help.
I think I want to imitate some more of Helen's pages but I'm determined to do that on my own......

Cheers again

[bigmac]

Me again!
Having developed my annual comparison page, I have now started developing a monthly comparison, so that the same month in different years can be compared (and ultimately I would like to select which years I want to compare, but with only 2 full years worth of data collected so far, not really a priority!)

I could have based it on the monthly record template that I already have on my site http://www.menstonweather.co.uk/monthlyrecord.php, but I wanted to do this slightly differently by using a user select-able drop down box and making it all php based:
http://www.menstonweather.co.uk/monthcompare.php.

So I found out how to do the drop down box, but I cannot fathom out how to use that selection in the MYSQL query.
I know I need to use something like a $monthselected=$_POST['month'] variable and then pass the $monthselected variable back in the MYSQL query statement, but I have tried capturing the selection in many different places and can't seem to get it to pass back the variable. I also started reading stuff about SQL injection attacks and I got scared..........

Any pointers welcome if anyone is feeling a little bored and charitable.

Thanks