PHP Read Txt file and send it to MYSQL

[Carlios]

This is for fun and I want to learn. I have this weatherstation that gives me a txt file every 3 minutes on local host

I wounder if I can do a php scripts that runs every 3 min and read the data in the txt file and send the to mysql

My plan is to have a crontab job running. 

 

Here is a exampel of my textfile and descriptions:

example:
          
12345 18.0 18.0 239 13.2 98 1018.6 0.0 156.9 835.2 0.000 0.000 16.6 41 17.8 2 0.0 0 0 0.0 -100.0 255.0 -100.0 16.6 -100.0 -100.0 -100 -100 -100 00 15 58 West_Coast_Road-0:15:58_AM 0 0 24 9 100 100 100 100 100 100 100 13.2 15.9 13.2 13.1 1 Night_time/Dry/A_few_clouds_ -0.3 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 18 28.7 12.9 407.2 24/9/2014 16.0 15.7 13.2 13.1 0.0 18 17 19 21 24 19 24 20 22 18 13.2 13.2 13.2 13.2 13.2 13.2 13.2 13.2 13.2 13.2 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 13.2 13.1 13.2 25.3 0 --- --- 250 0 0 -100.0 -100.0 0 0 0 0 0 0.0 16.7 16.6 7.9 1018.7 1018.5 34 11:40PM 12:05_AM  4.0E+0252  7.9E-0067 12.9 12.7 18 2014 -9.4 -1 0 -1 285 247 274 246 250 253 240 246 253 239 15.0 255.0 20.1 13.0 -37.11667 -174.58333 0.0 98 98 0.0 12:02_AM 197.6 0.0 106.5 0.0 0.0 0.0 4.4 12:13_AM 12:02_AM !!C10.37R!! 
          
0         12345    ID code
1         6.1      Average windspeed (kts) (rolling average of last 60 readings (updated every second), unless station provides average 
2         8.5      Current windspeed (kts)
3         235      Wind direction
4         16.3     Temperature (oC)
5         79       Outdoor humidity (%)
6         1010.0   Barometer (hpa)
7         0.0      Daily rainfall (mm)
8         17.6     Monthly  "       "
9         376.8    Yearly   "       "
10        0.0      Rain rate (mm/min)
11        0.0      Max rain rate (mm/min) for the day
12        20.8     Indoor temperature (oC)
13        0        Indoor humidity (%)
14        15.9     Soil temperature (oC)
15        0        Forecast icon
16        0.0      WMR968 extra temperature (oC)
17        0        WMR968 extra humidity (%)
18        0        WMR968 extra sensor number
19        6.6      Yesterday rainfall (mm)
20        20.8     Extra temperature sensor #1 (oC)
21        22.6     Extra temperature sensor #2   
22        -20.0    Extra temperature sensor #3    
23        -20.0    Extra temperature sensor #4   
24        -20.0    Extra temperature sensor #5     
25        -20.0    Extra temperature sensor #6     
26        0.0      Extra humidity sensor #1   (%)
27        0.0      Extra humidity sensor #2   
28        0.0      Extra humidity sensor #3    
29        21        Hour
30        29        Minute
31        42        Seconds
32        Awhitu    Stationname and time added
33        0         Lightning counts since noon. If using nextstorm, then its the total counts supplied by nexstorm
34        0         Actual solar reading
35        7         Day
36        4         Month
37        0         WMR968 battery level 1, but will be battery volts 1 if not WMR968 station
38        0         WMR968 battery level 2 , but will be battery volts 2 if not WMR968 station
39        7         WMR968 battery level 3
40        4         WMR968 battery level 4
41        100       WMR968 battery level 5
42        100       WMR968 battery level 6
43        100       WMR968 battery level 7
44        19.5      Current windchill reading  (oC)
45        18.5      Current humidex value (oC)
46        15.5      Maximum daily temperature (oC)
47        12.5      Minimum daily temperature (oC)
48        0         Current Condition Icon # - see list:
                   0= imagesunny
                   1= imageclearnight
                   2= imagecloudy
                   3= mainly fine (only a few clouds)
                   4= imagecloudynight
                   5= imagedry (cloudy periods)
                   6= imagefog
                   7= imagehaze
                   8= imageheavyrain (heavy rain)
                   9= imagemainlyfine
                   10=imagemist
                   11=imagenightfog
                   12=imagenightheavyrain
                   13=imagenightovercast
                   14=imagenightrain
                   15=imagenightshowers
                   16=imagenightsnow
                   17=imagenightthunder
                   18=imageovercast
                   19=imagemainlycloudy
                   20=imagerain (normal rain)
                   21=light rain
                   22=imageshowers2  (light rain/drizzle)
                   23=imagesleet
                   24=imagesleetshowers
                   25=imagesnow
                   26=imagesnowmelt
                   27=imagesnowshowers2 (light snow/snow showers)
                   28=imagesunny
                   29=imagethundershowers
                   30=imagethundershowers2
                   31=imagethunderstorms
                   32=imagetornado
                   33=imagewindy
                   34=stopped rainning
                   35=windy rain
                   36=sunrise
                   37=sunset
49        Current weather description
50        Baro trend last hour (hPa):for the local file clientraw.txt
51-70     the next 20  positions = the windspeed array for the wind speed graph
71        maximum gust for the day (kts)
72        dew pointer temperature (oC)
73        cloud height in feet
74        current date  (either as day/mth/year or mth/day/year, depending on your date format setting in WD)
75        maximum humidex
76        minumim humidex
77        maximum windchill
78        minimum windchill
79        Davis VP UV
80-89     array of last hour windspeed (10 positions)
90-99     array of last hour temperature (10 positions)
100-109   array of last rain last hour (10 positions) (daily rain total)
110       max heat index
111       min heat index
112       heat index value (oC)
113       Maximum average speed for the day (kts)
114       Lightning count last minute
115       Time of last lightning count 
116       date of last lightning count 
117       Average wind direction (degrees)
118       Nexstorm distance of last strike
119       Nexstorm bearing of last strike
120       Extra temperature sensor #7
121       Extra temperature sensor #8
122       Extra humidity sensor #4
123       Extra humidity sensor #5
124       Extra humidity sensor #6
125       Extra humidity sensor #7
126       Extra humidity sensor #8
127       VP solar wm/2 reading
128       Max indoor temperature
129       Min indoor temperature
130       Apparent temperature
131       Max barometer
132       Min barometer
133       Max gust last hour
134       Max gust last hour time
135       Max gust today time
136       Max Apparent temperature
137       Min apparent temperature
138       max dew point
139       min dew point
140       max gust in the last minute
141       Year
142       THSWI index (needs to be enabled in WD)
143       Temp Trend (-1/0/1)
144       Humidity Trend (-1/0/1)
145       Humidex Trend (-1/0/1)
146-155   next 10 data positions = wind direction last hour
156       VP leaf wetness
157       VP soil moisture
158       10 minute average windspeed (kts)
159       wetbulb
160       latitude
161       longitude
162       9am reset rain total (mm)
163       daily high humidity
164       daily low humidity
165       midnight reset rain total (mm)
166       time of daily low windchill  //position 166
167       current cost channel 1 (watts)
168       current cost channel 2
169       current cost channel 3
170       current cost channel 4
171       current cost channel 5
172       current cost channel 6
173       daily wind run (km) (9am or midnight reset)
174       Time of daily max temp
175       Time of daily min temp
176       10 minute average wind direction
177       Version of WS OS

Any one point me to the right directions

Would this be tricky ?

[mac_gyver]

you would need to run the php script at 1.5 minute intervals (twice the frequency of the data) to insure that you don't miss any data.

you would read the contents of the file and explode it into an array. if you want to only keep some of the elements, you can use array_intersect_key() to only keep those elements that you want.

to store the data, rather than to have a table with 177+ columns, have one table which you insert a row for each data sample, with an id (auto-increment primary index), a datetime column, and any other columns that describe the data, such as a unit id, so that you can record data for multiple stations. the id column in this table produces a data_id. you would get this id and use it when storing the actual data. the data table would have an id (auto-increment primary index), data_id, field number (0-177), and value columns. the icon mapping values would be stored in another database table, that you would JOIN with the field number 48 values to get the corresponding text/image.