Page 3 of 3
Re: Alltime yearly Rainfall webtag
Posted: Mon 28 Jan 2013 9:54 am
by mcrossley
bigmac wrote: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;)
Re: Alltime yearly Rainfall webtag
Posted: Mon 28 Jan 2013 12:04 pm
by 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
Re: Alltime yearly Rainfall webtag
Posted: Mon 28 Jan 2013 10:58 pm
by 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
Re: Alltime yearly Rainfall webtag
Posted: Wed 03 Apr 2013 11:12 am
by 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