datainserter.php (5130B)
1 <?php 2 // Server connection details 3 $servername = "86.92.67.21"; 4 $username = "friedel"; 5 $password = "hailiwa"; 6 $dbname = "wap2"; 7 8 $redis = new Redis(); 9 $redis->connect('127.0.0.1', 6379); 10 11 // Include data validator function 12 include 'datavalidator.php'; 13 // Create connection 14 $db_connection = mysqli_connect($servername, $username, $password, $dbname); 15 16 // Check connection 17 if (!$db_connection) { 18 die("Connection failed: " . mysqli_connect_error()); 19 } 20 21 // Check if request method is POST 22 if ($_SERVER['REQUEST_METHOD'] == 'POST') { 23 24 // Retrieve the raw request body data 25 $request_body = file_get_contents('php://input'); 26 27 // Decode the JSON data into a PHP associative array 28 $data = json_decode($request_body, true); 29 30 // Access the "WEATHERDATA" array from the decoded data 31 $weather_data = $data['WEATHERDATA']; 32 33 // Loop through each weather data object and insert it into the database 34 foreach ($weather_data as $weather_obj) { 35 $stn = $weather_obj['STN']; 36 $date = $weather_obj['DATE']; 37 $time = $weather_obj['TIME']; 38 $temp = $weather_obj['TEMP']; 39 $dewp = $weather_obj['DEWP']; 40 $stp = $weather_obj['STP']; 41 $slp = $weather_obj['SLP']; 42 $visib = $weather_obj['VISIB']; 43 $wdsp = $weather_obj['WDSP']; 44 $prcp = $weather_obj['PRCP']; 45 $sndp = $weather_obj['SNDP']; 46 $frshtt = $weather_obj['FRSHTT']; 47 $cldc = $weather_obj['CLDC']; 48 $wnddir = $weather_obj['WNDDIR']; 49 50 // $stn = 637200; 51 // $date = "2022-02-09"; 52 // $time = "00:00:58"; 53 // $temp = 10.1; 54 // $dewp = 1.5; 55 // $stp = 984.1; 56 // $slp = 1012.6; 57 // $visib = 23.4; 58 // $wdsp = 13.8; 59 // $prcp = 0.00; 60 // $sndp = 0.0; 61 // $frshtt = "000000"; 62 // $cldc = 96.8; 63 // $wnddir = 228; 64 65 // Validate temperature 66 $valid = validate_temperature($stn, $temp, $redis); 67 68 // Prepare SQL INSERT-statement 69 $sql = "INSERT INTO weather_data (station_name,date_time,validated,temperature,dew_point,pressure_sea,pressure_station,visibility, 70 wind_speed,precipitation,snow_depth,events,frost_boolean,rain_boolean,snow_boolean,hail_boolean,thunder_boolean,tornado_boolean,cloud_count,wind_direction) 71 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; 72 73 // Create prepared statement with the SQL statement 74 $stmt = mysqli_prepare($db_connection, $sql); 75 76 // Concatenate date and time 77 $datetime = $date . " " . $time; 78 79 // Extract the boolean values from $frshtt 80 $frost = (int)substr($frshtt, 0, 1); 81 $rain = (int)substr($frshtt, 1, 1); 82 $snow = (int)substr($frshtt, 2, 1); 83 $hail = (int)substr($frshtt, 3, 1); 84 $thunder = (int)substr($frshtt, 4, 1); 85 $tornado = (int)substr($frshtt, 5, 1); 86 87 // Bind parameters to prepared statement 88 mysqli_stmt_bind_param($stmt, "isiddddddddsiiiiiidi", $stn, $datetime, $valid, $temp, $dewp, $stp, $slp, $visib, $wdsp, $prcp, $sndp, $frshtt, $frost, $rain, $snow, $hail, $thunder, $tornado, $cldc, $wnddir); 89 90 // Execute prepared statement, only if the data is not within the first 30 entries 91 $result = mysqli_stmt_execute($stmt); 92 93 94 // wanneer $valid -1 is zal deze tot de eerste 30 waarden van een station behoren, en dus niet in de database worden gemikt 95 // if ($valid == -1) { 96 // echo "entry geskipt"; 97 // $result = false; 98 // } 99 100 // Second Insert for shadow table 101 if ($valid == -1) { 102 $sql2 = "SELECT data_id from weather_data where station_name = ? and date_time = ?"; 103 $stmt2 = mysqli_prepare($db_connection, $sql2); 104 mysqli_stmt_bind_param($stmt2, "is", $stn, $datetime); 105 $stmt2->execute(); 106 $result = $stmt2->get_result(); 107 $row = $result->fetch_assoc(); 108 $data_id = $row['data_id']; 109 110 $sql3 = "INSERT INTO weather_data_corrected (station_name, origin_data_id, date_time,temperature,dew_point,pressure_sea,pressure_station,visibility, 111 wind_speed,precipitation,snow_depth,events,frost_boolean,rain_boolean,snow_boolean,hail_boolean,thunder_boolean,tornado_boolean,cloud_count,wind_direction) 112 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; 113 $stmt3 = mysqli_prepare($db_connection, $sql3); 114 $new_temp = round($redis->lIndex('average'.$stn, 0), 1); 115 mysqli_stmt_bind_param($stmt3, "iisddddddddsiiiiiidi", $stn, $data_id, $datetime, $new_temp, $dewp, $stp, $slp, $visib, $wdsp, $prcp, $sndp, $frshtt, $frost, $rain, $snow, $hail, $thunder, $tornado, $cldc, $wnddir); 116 $result = mysqli_stmt_execute($stmt3); 117 } 118 119 120 // Check if the insertion was successful 121 if ($result) { 122 echo "Data inserted successfully"; 123 } 124 else { 125 echo "Error inserting data: " . mysqli_error($db_connection); 126 } 127 } 128 } 129 130 // Close the database connection 131 mysqli_close($db_connection); 132 ?>