MX Administrative Interface: Difference between revisions

MX Administrative Interface (view source)

Revision as of 11:33, 4 September 2020

286 bytes added , 11:33, 4 September 2020

m

→‎Another daily summary table

Sfws

5,838

edits

@@ Line 372: / Line 372: @@
 **# A tick box to enable or disable this upload (so you can leave the SQL recorded, but stop running it when you like.
 **# The SQL you want to run, what you type in this small text box should include INSERT IGNORE (or REPLACE) to insert a row,  or include UPDATE to change columns in a row that already exists, like any SQL it must include the name of the table, the columns to be updated, and the values you want to insert into the columns are either expressed as web tags, as SQL functions on web tags, or as a sub-query reading the values from somewhere else.
-**#* Here is an example of a suitable query that MX can process for you [note I have had to include some yesterday tags e.g. for primary key ('''<#metdateyesterday format=yyyy-MM-dd>''', and I have used the SUBSTRING function at one point, but I don't have a sub-query in this example):
+**#* Here is an example of a suitable query that MX can process for you [note I have had to include some yesterday tags e.g. for primary key ('''<#metdateyesterday format=yyyy-MM-dd>''', I have used the SUBSTRING function at one point, and also be aware that the column names listed here are different from the previous example but if you are doing a custom update it will be because you have different column names!  I don't have a sub-query in this example, but any SQL that is syntactically valid could appear here, and you could have more than one query in sequence.):
 <pre>INSERT IGNORE INTO `test_daily_summary` (`MaxRainRate`, `TMaxRainRate`, `HighHourRain`, `THighHourRain`, `TotRainFall`, `SnowFalling`, `SnowLying`, `SnowDepth`,  `CumChillHours`, `LogDate`, `RollOver`,  `MinTemp`, `TMinTemp`, `HeatDegDays`, `AvgTemp`, `MaxTemp`, `TMaxTemp`, `CoolDegDays`, `LowDewPoint`, `TLowDewPoint`, `LowHum`, `TLowHum`, `HighHum`, `THighHum`, `HighDewPoint`, `THighDewPoint`, `GreatWindChill`, `TGreatWindChill`, `LowAppTemp`, `TLowAppTemp`, `HighAppTemp`, `THighAppTemp`, `HighHeatInd`, `THighHeatInd`, `MinPress`, `TMinPress`, `MaxPress`, `TMaxPress`, `HighAvgWSpeed`, `THighAvgWSpeed`, `StrongestWindGust`, `TStrongestWindGust`, `BearStrongestWindGust`,   `BearStrongestWindGustSym`,`BearDomWind`, `BearDomWindSym`,  `TotWindRun`) VALUES ('<#rrateTM>', '<#TrrateTM>', '<#hourlyrainTH>', '<#ThourlyrainTH>', '<#rfall> ', '<#snowfalling>', '<#snowlying>', '<#snowdepth>', '<#chillhours>', '<#metdateyesterday format=yyyy-MM-dd>',  '(1 * SUBSTRING(<#rollovertime>,0,2))', '<#tempYL>', '<#TtempYL> ', '<#heatdegdays> ', '<#avgtemp>', '<#tempTH>', '<#TtempTH> ', '<#cooldegdays> ', '<#dewpointTL>', '<#TdewpointTL>', '<#humTL>', '<#ThumTL>', '<#humTH>', '<#ThumTH>', '<#dewpointTH>', '<#TdewpointTH>', '<#wchillTL>', '<#TwchillTL>', '<#apptempTL>', '<#TapptempTL>', '<#apptempTH>', '<#TapptempTH>', '<#heatindexTH>', '<#TheatindexTH>', '<#pressTL>', '<#TpressTL>', '<#pressTH>', '<#TpressTH>', '<#windTM>', '<#TwindTM>', '<#wgustTM>', '<#TwgustTM>', '<#bearingTM>', '<#directionTM>',  '<#domwindbearing>', '<#domwinddir>', '<#windrun>', '<#feelslikeTH>','<#TfeelslikeTH>','<#feelslikeTL>','<#TfeelslikeTL>','<#humidexTH>','<#ThumidexTH>');</pre>
 ** Again before you enable this option, there is a facility lower down this setting page (under the heading '''Create database table''') where you can type some SQL to be run immediately, that can create the table you want this option to update, (although it could even populate any table with historic data, it is only intended for a small query). I am using a table that already exists as I have used it for testing changes to my PHP scripts, so I did not need to create a table before I enabled the query shown above.