Top 10 Records Page

[water01]

Hmm confusing I get a ERROR - Bad Select Statement (1) error I use the old databse from David (the first one) http://apeldoorn.tk/weer/top10.php

I got this error with the Old ImportCumulusData.php which had the wrong named in the table (DePint instead of DewPoint).

As Mark says either use the latest script, or amemd your script to fix the error, drop the table and re-import it.

Alos check you table is called the same as Marks' in the SELECT statement. Mine was called DayData not daydata and MySQL databases are case sensitive.

[nitrx]

Mark thanks for the explanation works fine have to tune it up a bit

David I've setup the latest database

Alos check you table is called the same as Marks' in the SELECT statement. Mine was called DayData not daydata and MySQL databases are case sensitive.

I saw Mark has edited the top10 script the table must be configured now in the parameters anyway thanks for the advice (I think the old database script was already corrected)

[mcrossley]

I saw Mark has edited the top10 script the table must be configured now in the parameters anyway thanks for the advice (I think the old database script was already corrected)

Yep, I just changed the script to use a variable for the table name - hopefully that will make it easier for people to adapt. Not sure what I should do about my site specific stuff at the top of the page, I think I'll just leave that for people to clean up on their own.

[nitrx]

OK, the humidity problem is caused by the day file table having the high/low humidity fields defined as varchar(3) so SQL was performing a text based sort (100 < [anything above 19]). I have amended the ImportCumulusFile script to declare them as decimal(4,1) to fix this.

For my own table, as I have a Davis which only supplies integer humidity values I have changed my field definitions to int(3) and that has sorted the problem out. (That is a hint for those that do not know, you can change table field types on the fly so long as the data contained in them is compatible. You do not have to drop the whole table and recreate it)

Hmm I see more thimgs with no data (ignore sunreadings I dont have them) http://apeldoorn.tk/weer/top10.php the other stuff like location etc I will cleunup myself

[mcrossley]

Is it missing from your day file? I suspect the script is picking up NULLs on the minimum queries. If you use the Cumulus dayfile editor you can get it regenerate days created using older versions, unfortunately it can only recreate whole days from the monthly log files, not individual fields within days, so there is a chance the recreated days will miss some highs/lows.

As a quick test you could amend the simpleHiLo SQL query to...

Code: Select all

    $query = "SELECT LogDate, ROUND($fld, $dec) " .
             "FROM $dayFileTableName ".
             "WHERE $fld IS NOT NULL " .
             'ORDER BY ' . $fld . ($hi ? ' DESC ' : ' ASC ') .
             'LIMIT 10';

[nitrx]

Lowdewpoints and Lowwindchills are in my database (not from the beginning.. because these didn't exist then)

[nitrx]

Is it missing from your day file? I suspect the script is picking up NULLs on the minimum queries. If you use the Cumulus dayfile editor you can get it regenerate days created using older versions, unfortunately it can only recreate whole days from the monthly log files, not individual fields within days, so there is a chance the recreated days will miss some highs/lows.

As a quick test you could amend the simpleHiLo SQL query to...

Code: Select all

    $query = "SELECT LogDate, ROUND($fld, $dec) " .
             "FROM $dayFileTableName ".
             "WHERE $fld IS NOT NULL " .
             'ORDER BY ' . $fld . ($hi ? ' DESC ' : ' ASC ') .
             'LIMIT 10';

Do you mean in the script or phpadmin I get an error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT LogDate, ROUND($fld, $dec) " . "FROM $dayFileTableName ". ' at line 1

[nitrx]

Aha the new query in the script is the solution great thanks http://apeldoorn.tk/weer/top10.php

[water01]

I had NULLs in some of my data as it was converted in the early days from EasyWeather.

I amended the select statement in top10s.php to read

Code: Select all

    $query = "SELECT LogDate, ROUND($fld, $dec) " .
             'FROM DayData '.
             'WHERE ' . $fld . ' IS NOT NULL '.
             'ORDER BY ' . $fld . ($hi ? ' DESC ' : ' ASC ') .
             'LIMIT 10';

to get around the problem.

[mcrossley]

Ron, I see you have fixed it now, I have made the change to my script now so it should fix this issue for others.

David, I'd forgotten your change, now incorporated (the curse of having complete data! )

[nitrx]

I had NULLs in some of my data as it was converted in the early days from EasyWeather.

I amended the select statement in top10s.php to read

Code: Select all

    $query = "SELECT LogDate, ROUND($fld, $dec) " .
             'FROM DayData '.
             'WHERE ' . $fld . ' IS NOT NULL '.
             'ORDER BY ' . $fld . ($hi ? ' DESC ' : ' ASC ') .
             'LIMIT 10';

to get around the problem.

Yes this works fine !

[water01]

Mark, I have just noticed that there is something wron with the Wet days calculation.

Code: Select all

'SELECT MAX(LogDate) AS date_to, COUNT(*) cnt ' .
             'FROM (' .
               'SELECT @r := @r + (@rain != (TotRainFall > 0.1) OR @rain IS NULL) AS gr, ' .
                 '@rain := (TotRainFall > 0.1), ' .
                 'IF (TotRainFall < 0.2, "D", "W") AS wd, ' .
                 'df.LogDate ' .
               'FROM (' .
                 'SELECT @r := 0, ' .
                 '@rain := NULL' .
               ') vars, DayData df ' .
               'ORDER BY LogDate ' .
             ') x ' .
             'WHERE wd = "' . $dryWet . '" ' .
             'GROUP BY gr ' .
             'ORDER BY cnt DESC ' .
             'LIMIT 10';

My data holds inches not mm so I think it something to do with >0.1 and <0.2.

For instance my longest wet period is 27 days ending 23rd Dec 2011 (confirmed on database) with 0.1 rainfalls every few days (maximum between 0.1s 8 days).
I tried changing 0.1 to 0.0 which improved things and then 0.2 to 0.1 but it was still wrong.
I will keep experimenting, but have you any suggestions?

[mcrossley]

Yes, let me fix my code, then come back to you - at present it is only set up for mm...

[mcrossley]

OK, ver 0.6 should now cope with mm or inches, by default the thresholds are >= 0.2 mm or 0.01 inches.

The query has changed to incorporate a new $rainDayThreshold variable (and I switched the '>' to '>=' so the same value can be used everywhere in the query)...

Code: Select all

    $query = 'SELECT ' .
//               'MIN(LogDate) AS date_from, ' .
               'MAX(LogDate) AS date_to,' .
               'COUNT(*) AS cnt ' .
             'FROM ( ' .
               'SELECT ' .
                 "@r := @r + (@rain != (TotRainFall >= $rainDayThreshold) OR @rain IS NULL) AS gr," .
                 "@rain := (TotRainFall >= $rainDayThreshold)," .
                 "IF (TotRainFall < $rainDayThreshold, 'D', 'W') AS wd," .
                 'df.LogDate ' .
               'FROM (' .
                 'SELECT @r := 0,' .
                 '@rain := NULL' .
               ') vars,' .
               "$dayFileTableName df ".
               'ORDER BY LogDate' .
             ') x '.
             "WHERE wd = '$dryWet' " .
             'GROUP BY gr ' .
             'ORDER BY cnt DESC ' .
             'LIMIT 10';

EDIT: I hope this fixes anyway, I expect you'll let me know!

[water01]

Mark, great as usual, implemented the PHP changes and everything is fixed!!

Thank you.