/* Insert the pruchase to the table Purchase_List */
$query = "INSERT INTO Purchase_List(IP, Country_Name) ";
$query .= "VALUES ('" . $ip . "', '" . $delivery_country . "') ";
mysqlQuery($connection, $query);
/* If the user want to empty the table Purchase_List */
} else {
if (isset($_POST['clear'])) {
$query = "TRUNCATE TABLE Purchase_List";
mysqlQuery($connection, $query);
}
}
}
/* Display the current Purchase_List on the page */
$query = "SELECT * FROM Purchase_List ";
$query .= "ORDER BY Purchase_ID DESC ";
$result = mysqlQuery_Result($connection, $query);
$count = mysqli_num_rows($result);
/* Check if the table is empty */
if ($count == 0) {
echo "Current purchase list is empty";
} else {
while ($count) {
$row = mysqli_fetch_assoc($result);
echo "Purchase ID: " . $row['Purchase_ID'] . " | IP Address: " . $row['IP'] . " | Delivery Country: " . $row['Country_Name'] . "<br >";
$count--;
}
}
mysqli_free_result($result);
mysqli_close($connection);
?>
function check($connection)
{
$count_items = 0;
// Will be used to count the items whose the two countries do not match
/* Firstly, spliting the target IP address to four parts. Example: 85.45.1.122 -> part1='85', part2='45', part3='1' and part4='122' */
$query = "SELECT SUBSTRING_INDEX(IP,'.',1), ";
$query .= "SUBSTRING_INDEX(SUBSTRING_INDEX(IP,'.',2),'.',-1), ";
$query .= "SUBSTRING_INDEX(SUBSTRING_INDEX(IP,'.',-2),'.',1), ";
$query .= "SUBSTRING_INDEX(IP,'.',-1), ";
$query .= "Country_Name, Purchase_ID ";
$query .= "FROM Purchase_List";
$result = mysqlQuery_Result($connection, $query);
while ($row = mysqli_fetch_row($result)) {
$part1 = $row[0];
//part1 of the target IP address
$part2 = $row[1];
$part3 = $row[2];
$part4 = $row[3];
$country_name = $row[4];
// the delivery country of the purchase
$Purchase_ID = $row[5];
/* Core algorithm: convert the IP address to decimal, then compare */
$Decimal_ip = $part1 * 256 * 256 * 256 + $part2 * 256 * 256 + $part3 * 256 + $part4;
/* Find the actual country which the target IP belongs to.*/
$query = "SELECT Country_Name FROM IP ";
$query .= "WHERE Decimal_IP_Start <=" . $Decimal_ip . " ";
$query .= "ORDER BY Decimal_IP_Start DESC ";
$query .= "LIMIT 1 ";
if (!($checking_result = mysqli_query($connection, $query))) {
echo "<br />1 " . mysqli_errno($connection) . ":" . mysqli_error($connection) . "<br />";
} else {
$row = mysqli_fetch_row($checking_result);
$actual_country_name = $row[0];
/* Check if the actual country is same as the delivery country. If not, display the item on the webpage*/
if ($country_name != $actual_country_name) {
$count_items++;
//Count the items whose the two countries do not match
echo "Purchase ID: " . $Purchase_ID . " | Delivering Country: " . $country_name . " | Country of the IP address: " . $actual_country_name . " (" . $part1 . "." . $part2 . "." . $part3 . "." . $part4 . ")";
echo "<br />";
}
}
}
mysqli_free_result($result);
echo "<br>Total items:" . $count_items . "<br><br>";
}