Brian,
The mysql interface is great, but I want more!!!
Could you develop an interface within WD where the user enters one or more SQL queries to be sent to mysql at preset intervals e.g. 5/10/15 minutes/ hourly/daily. The goal would be to allow users to automatically update other tables they have created in mysql e.g. I have an “day” table and a “month” table. I currently have to manually update these tables with queries like:
[code:1]
SET @Year=YEAR(CURDATE()),@Month=MONTH(CURDATE()),@Monthname=DATE_FORMAT(CURDATE(),‘%M’) ;
REPLACE INTO Day
(Year,Monthnumber,Monthname,Day,MaxTemp,AvgTemp,MinTemp,MaxHum,AvgHum,MinHum,MaxDew,AvgDew,MinDew,MaxBaro,AvgBaro,MinBaro,MaxGust,AvgGust,MaxWind,AvgWind,AvgDir,Rain)
SELECT @Year as Year,@Month as Monthnumber,@Monthname as Monthname,
DayOfMonth(date) as Day,
Round(Max(temperature),1) AS MaxTemp,
Round(Avg(temperature),1) AS AvgTemp,
Round(Min(temperature),1) AS MinTemp,
Max(outdoor_humidity) AS MaxHum,
Round(Avg(outdoor_humidity)) AS AvgHum,
Min(outdoor_humidity) AS MinHum,
Round(Max(dew_point_temperature),1) AS MaxDew,
Round(Avg(dew_point_temperature),1) AS AvgDew,
Round(Min(dew_point_temperature),1) AS MinDew,
Round(Max(barometer),1) AS MaxBaro,
Round(Avg(barometer),1) AS AvgBaro,
Round(Min(barometer),1) AS MinBaro,
Round(Max(gust_windspeed)) AS MaxGust,
Round(Avg(gust_windspeed)) AS AvgGust,
Round(Max(average_windspeed)) AS MaxWind,
Round(Avg(average_windspeed)) AS AvgWind,
Round(Avg(wind_direction)) AS AvgDir,
Max(daily_rainfall) AS Rain
FROM wx_data
WHERE date=CURDATE()
GROUP BY Year(‘date’);
REPLACE INTO Monthly
(Year,Monthnumber,Monthname,MaxTemp,MinTemp,AvgTemp,MaxHum,MinHum,AvgHum,MaxDew,MinDew,AvgDew,MaxBaro,MinBaro,AvgBaro,MaxGust,AvgGust,MaxWind,AvgWind,AvgDir,Rain,Snow)
SELECT Year as Year,
Monthnumber as Monthnumber,
Monthname as Monthname,
Max(MaxTemp) AS MaxTemp,
Min(MinTemp) AS MinTemp,
Round(Avg(AvgTemp),1) AS AvgTemp,
Max(MaxHum) AS MaxHum,
Min(MinHum) AS MinHum,
Round(Avg(AvgHum)) AS AvgHum,
Max(MaxDew) AS MaxDew,
Min(MinDew) AS MinDew,
Round(Avg(AvgDew),1) AS AvgDew,
Max(MaxBaro) AS MaxBaro,
Min(MinBaro) AS MinBaro,
Round(Avg(AvgBaro),1) AS AvgBaro,
Max(MaxGust) AS MaxGust,
Round(Avg(AvgGust)) AS AvgGust,
Max(MaxWind) AS MaxWind,
Round(Avg(AvgWind)) AS AvgWind,
Round(Avg(AvgDir)) AS AvgDir,
Sum(Rain) AS Rain,
Sum(Snow) AS Snow
FROM Day
WHERE (Year=@Year AND Monthnumber=@Month)
GROUP BY Year,Monthnumber;
[/code:1]
My suggestion would let me enter this query in a field in WD, and then set the interval at which WD would execute the query.
Let me know if you think you can do it.
Cheers
Ian