public static function get_all_campaigns()
 {
     // create and check connection
     $link = CheckrouteDatabase::connect();
     // query all available measurement campaigns
     $stmt = mysqli_prepare($link, "SELECT x.id, x.name, x.description, COUNT(y.id_measurement) AS 'number_of_measurements' FROM MeasurementCampaign AS x, Campaign_has_Measurement AS y WHERE x.id = y.id_campaign GROUP BY x.id");
     if ($stmt) {
         mysqli_stmt_execute($stmt);
         //mysqli_stmt_bind_result($stmt, $result_sql);
         $result = array();
         while (mysqli_stmt_fetch($stmt)) {
             $result[] = $result_sql;
         }
         mysqli_stmt_close($stmt);
     }
     // close database connection
     mysqli_close($link);
     return $result;
 }
 public static function get_country_of_ip_address($ipv4_address, $source)
 {
     $ipv4_address_integer = ip2long($ipv4_address);
     echo $ipv4_address;
     echo '<br/>';
     echo $ipv4_address_integer;
     // create and check connection
     $link = CheckrouteDatabase::connect();
     // query all available measurement campaigns
     $stmt = mysqli_prepare($link, "SELECT country_code FROM IPRange_belongs_to_Country WHERE ip_from <= ? AND ip_to >= ? AND source = ?");
     if ($stmt) {
         mysqli_stmt_bind_param($stmt, "sss", $ipv4_address_integer, $ipv4_address_integer, $source);
         mysqli_stmt_execute($stmt);
         mysqli_stmt_bind_result($stmt, $result);
         $result_asn = array();
         while (mysqli_stmt_fetch($stmt)) {
             $result_asn[] = $result;
         }
         mysqli_stmt_close($stmt);
     }
     // close database connection
     mysqli_close($link);
     return $result;
 }
 public static function write_as_list_to_db($as_list, $source)
 {
     // create and check connection
     $link = CheckrouteDatabase::connect();
     // write all entries in AS list to database
     foreach ($as_list as $as_entry) {
         $as_number = $as_entry['number'];
         if ($as_entry['name'] == NULL) {
             $as_name = '?? (n/a) ??';
         } else {
             $as_name = $as_entry['name'];
         }
         if (array_key_exists('country', $as_entry)) {
             $as_country_code = $as_entry['country'];
         } else {
             $as_country_code = NULL;
         }
         $as_source = $source;
         $stmt = mysqli_prepare($link, "INSERT INTO AutonomousSystem (asn, name, owner, country_code, registration_date, source) VALUES (?, ?, NULL, ?, NULL, ?)");
         if ($stmt === false) {
             trigger_error('Error: Insert statement failed. ' . htmlspecialchars(mysqli_error($mysqli)), E_USER_ERROR);
         }
         $bind = mysqli_stmt_bind_param($stmt, "ssss", $as_number, $as_name, $as_country_code, $as_source);
         if ($bind === false) {
             trigger_error('Error: Bind of parameters failed. ', E_USER_ERROR);
         }
         $exec = mysqli_stmt_execute($stmt);
         if ($exec === false) {
             trigger_error('Error: execution of statement failed. ' . htmlspecialchars(mysqli_stmt_error($stmt)), E_USER_ERROR);
         }
     }
     // close database connection
     mysqli_close($link);
 }
 public static function write_geolocation_data_to_db($geolocation_data, $source)
 {
     // create and check connection
     $link = CheckrouteDatabase::connect();
     // write all entries in AS list to database
     foreach ($geolocation_data as $geolocation_entry) {
         $stmt = mysqli_prepare($link, "INSERT INTO IPRange_belongs_to_Country (ip_from, ip_to, country_code, assigning_rir, assigning_date, source) VALUES (?, ?, ?, ?, ?, ?)");
         if ($stmt === false) {
             trigger_error('Error: Insert statement failed. ' . htmlspecialchars(mysqli_error($mysqli)), E_USER_ERROR);
         }
         $bind = mysqli_stmt_bind_param($stmt, "ssssss", $geolocation_entry['ip_from'], $geolocation_entry['ip_to'], $geolocation_entry['country'], $geolocation_entry['assigning_rir'], $geolocation_entry['assigning_date'], $source);
         if ($bind === false) {
             trigger_error('Error: Bind of parameters failed. ', E_USER_ERROR);
         }
         $exec = mysqli_stmt_execute($stmt);
         if ($exec === false) {
             print_r($geolocation_entry);
             trigger_error('Error: execution of statement failed. ' . htmlspecialchars(mysqli_stmt_error($stmt)), E_USER_ERROR);
         }
     }
     // close database connection
     CheckrouteDatabase::close($link);
 }
 public static function get_active_probe_numbers()
 {
     // create and check connection
     $link = CheckrouteDatabase::connect();
     // query all probe IDs from database
     $stmt = mysqli_prepare($link, "SELECT `probe_id` FROM `AtlasProbe` WHERE `never_connected` = 0 AND `disconnected_or_abandoned` = 0 ORDER BY `probe_id` ASC");
     if ($stmt) {
         mysqli_stmt_execute($stmt);
         mysqli_stmt_bind_result($stmt, $result_sql);
         $result = array();
         while (mysqli_stmt_fetch($stmt)) {
             $result[] = $result_sql;
         }
         mysqli_stmt_close($stmt);
     }
     // close database connection
     mysqli_close($link);
     return $result;
 }