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; }