Alltime yearly Rainfall webtag

[PaulMy]

Not a quick task, but you could copy your dayfile.txt (or the daily summary if you have that) into Excel and then compare the daily rain column to the WU daily amounts and see where the differences are...

Paul

[bigmac]

Or a quick query to your SQL database

Ok, so middle of last year I started trying to fathom out how to do queries from the SQL database. I started looking in to it, got confused, ran out of time as other things took over my year, and gave it all up as a bad job. I've recently started looking at the SQL stuff again, but am still totally confused.
If I wanted to add up the totals from my database for a given year, I thought I could formulate something like this:

SELECT SUM(TotRainFall) FROM Dayfile WHERE DATEPART(yyyy,LogDate)=2012

but DATEPART doesn't seem to work on the MYSQL server, it says the function doesn't exist.
Am I on the right track or have I misunderstood something?
Also, what is the best way to test functions and their results? At the moment I am running them in the SQL section of the phpMyAdmin tool on the webserver but is there a better way?
Sorry, lots of questions.

Oh, and just to add, this what I am eventually trying to achieve (or something like):
http://www.lalocherie.com/weather/thisyearprevyears.php

[mcrossley]

For that query I would try

... WHERE EXTRACT(YEAR FROM LogDate)=2012

(http://dev.mysql.com/doc/refman/5.5/en/ ... tions.html)

I also use the phpMyAdmin interface for testing queries, I guess there will be clients that you can use, but I've never looked into it.

[mcrossley]

To fetch all the years in a single query, you could do something like (totally untested)...

Select extract(year from LogDate) as year, sum(TotRainfall) from day file group by extract(year from LogDate);

(Apologies, typed on a tablet, so case is a bit of a mess!)

[tobyspond]

This will work
select year(logdate) as year, sum(totrainfall) as rain from dayfile where year(logdate)=2012

and if you want all years

select year(logdate) as year, sum(totrainfall) as rain from dayfile group by year(logdate)

if your table is not called dayfile just replace that bit with the name of your table

Kerry

[bigmac]

Thanks Kerry and Mark, that's really helped and worked a treat.
Next problem, I wanted to get the minimum temp for a year and the date that it occurred on, so I did this:

Code: Select all

SELECT MIN(MinTemp) FROM Dayfile WHERE EXTRACT(YEAR FROM LogDate)=2012

to get the minimum temp, and this:

Code: Select all

SELECT DATE_FORMAT(LogDate, '%e %M') FROM Dayfile WHERE EXTRACT(YEAR FROM LogDate)=2012 AND MIN(MinTemp)

to get the date.

The first bit works to get the minimum temp but the date extraction throws up an error, I suspect because of the last bit as I am not explicitly saying what MIN(MinTemp) is equal to, but obviously I don't know what it is at this point. I have obviously got this bit wrong, but can't work out the correct way to do this yet. Any pointers?

[tobyspond]

Try this :

select year(logdate) as year, monthname(logdate) as month, day(logdate) as day, min(mintemp) as mintemp from dayfile

It should give you year, month, day and the mintemp

For example

2012 Jan 24 -10.7

Kerry

[bigmac]

Try this :

select year(logdate) as year, monthname(logdate) as month, day(logdate) as day, min(mintemp) as mintemp from dayfile

It should give you year, month, day and the mintemp

For example

2012 Jan 24 -10.7

Kerry

Thanks Kerry. I was just looking over this again and thought I started seeing the wood for the trees and that started to become obvious as the correct way to do it.
But, to give you the fuller picture, I am actually passing this out as a variable like this:

Code: Select all

$lymintemp=mysql_result(mysql_query("SELECT MIN(MinTemp) FROM Dayfile WHERE EXTRACT(YEAR FROM LogDate)=2012"),0);

so, I suppose the new question is how to pass out the individual parts i.e. year, month, mintemp etc, into the one overall variable and have it display on my website in a statement like this:

Code: Select all

<td><?php print $lymintemp;?> <?php print $tempunit;?> on <?php print  $lytmintemp;?></td>

[tobyspond]

The query part

$sql="SELECT MIN(MinTemp) FROM Dayfile WHERE EXTRACT(YEAR FROM LogDate)=2012";
$result = mysql_query($SQL)or die('Error: ' . mysql_error());


This is how I call the parts into a table

<?php while($row=mysql_fetch_object($result)){
?>
<tr>
<th ><?php echo $row->Year; ?></th>
<td><?php echo $row->MinTemp; ?></td>
</tr>

<?php
}
?>

[bigmac]

Sorry, I am probably being stupid, and I probably haven't explained things properly and also confused things by adding in those original queries.

This is what I wanted to achieve:

http://menstonweather.freeglo.net/YearlyMaxMin.php

(ignore the formatting issues and other erroneous data).
So what I thought I could do, using your earlier example, is:

Code: Select all

$querylymint='SELECT MIN(MinTemp) as mintemp, DAY(LogDate) as day, MONTHNAME(LogDate) as month FROM Dayfile WHERE EXTRACT(YEAR FROM LogDate)=2012';
$resultlymint=mysql_query($querylymint)or die('Error: '.mysql_error());

and then to display it:

Code: Select all

<?php while($row=mysql_fetch_object($resultlymint)){
?>
<td><?php echo $row->mintemp;?> on <?php echo $row->day;?> <?php echo $row->month;?></td>
<?php
}

This produces the correct lowest temperature value, but as you can see, returns a day of 1 and a month of January both of which aren't the correct values for the lowest temperature day in 2012.
Doing the same for the year 2011 which is when my data starts, again produces the correct lowest temperature but a day of 11 and a month of June, which is actually the first day I started recording data in Cumulus. So this doesn't appear to equate the date information with the minimum temp, it is just looking for the first date available for the years concerned regardless of what the temperature is.

[tobyspond]

Now I see what you want.

Try this: SELECT year(logdate) as year, month(logdate) as month, day(logdate) as day, mintemp FROM dayfile where year(logdate) =2012 order by mintemp limit 1

[bigmac]

Brilliant, that worked a treat.
Just to help my understanding (and others if looking here in future), the

Code: Select all

order by MinTemp limit 1

is the crucial bit here.
From what I understand, this effectively looks for all the values in the MinTemp column, orders them lowest first but then only takes the first returned value, i.e. the lowest value, and then the rest of the code extracts the date for that value. Is that correct?

[tobyspond]

That's correct. To make sure you get the ordering you want, you can include ASC or DESC after the order by XXXX. You use any number after the limit, so if you use 10 you can get the first 10 records.

Kerry

[bigmac]

Brill, many thanks again.
I have managed to get many of the other stats populated now so am pretty chuffed that I managed to work them out. If you want to have a look:

http://menstonweather.freeglo.net/yearcompare.php

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.
I will spend some time trying to figure this out now, but may well be back to ask for more help, but Kerry, if you are bored of me, don't feel you need to help anymore, you have already done plenty

[tobyspond]

No problem - happy to help. If you have more questions, please ask away.

Your page looks good - nice job.

Kerry