SQL country / State & Cities find and insert any help?

[PHPhelpCN]

little help needed.

I have three tablets in SQL databases by the name of
1 countries
2 states
3 cities

I am using api-ip to get country name, states, cities , however, sometimes the specific city or state does not exist in the main database, some specific countries the state is also missing i.e.

[status] => success
[country] => Singapore
[countryCode] => SG
[region] =>
[regionName] =>
[city] => Singapore
[zip] =>
[lat] => 1.28009
[lon] => 103.851
[timezone] => Asia/Singapore
[isp] => Cloudflare, Inc.
[org] => Cloudflare, Inc.
[as] => AS13335 Cloudflare, Inc.
[query] => 172.69.135.73

if someone can write me shortcode in PHP to find out which place is missing in the database and how to insert new record automatically in the table?

some experts? any help? Thanks for your prompt assistance.

[astonecipher]

Paying someone would get more attention than asking someone to write an ETL process for you for free. It isn’t a simple “shortcode” to write your ETL.

[PHPhelpCN]

thanks for your support,

here we go, if someone facing the same problem as I done, here is what i wrote,

if (!empty($_SERVER['HTTP_CLIENT_IP'])) {
    $ip = $_SERVER['HTTP_CLIENT_IP'];
} elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
    $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
    $tmp = explode(',', $ip); 
    $ip = $tmp[0];
} else {
    $ip = $_SERVER['REMOTE_ADDR'];
}


     $query = @unserialize(file_get_contents('http://ip-api.com/php/'. $ip));

      if($query['status'] == 'success') {
        $country = $query['country'];
        $state = $query['regionName'];
        $city = $query['city'];
        $lat = $query['lat'];
        $lon = $query['lon'];
        $error = $query['status'];
      
      } else {
        $error = $query['status'];
      }

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
} else {
}

 $query = "SELECT * FROM `countries` WHERE `name` LIKE '%$country%'";

                        if ($result = mysqli_query($conIP, $query)) {
                        
                            while ($row = mysqli_fetch_row($result)) {
                           $getcountryID = $row[0];
                           
                           
                           }
                          
                       } 
                        

$query = mysqli_query($conIP, "SELECT * FROM states WHERE name = '$state'");

if(mysqli_num_rows($query) > 0){

                           while ($row = mysqli_fetch_row($query)) {
                           $getStateID = $row[0];
                           }

}else{

	        $query = mysqli_query($conIP, "INSERT INTO `states`(`name`,`country_id`) VALUES ('$state', '$getcountryID')");
	        if ($query > 0) {
	        }

}


 $query = "SELECT * FROM `states` WHERE `name` LIKE '$state'";

                        if ($result = mysqli_query($conIP, $query)) {
                        
                            while ($row = mysqli_fetch_row($result)) {
                           $getStateID = $row[0];
                           
                           
                           }
                          
                       } 



$query = mysqli_query($conIP, "SELECT * FROM cities WHERE name = '$city'");

if(mysqli_num_rows($query) > 0){

                           while ($row = mysqli_fetch_row($query)) {
                           $getCityID = $row[0];

                           }

}else{

	        $query = mysqli_query($conIP, "INSERT INTO `cities`(`name`,`state_id`) VALUES ('$city', '$getStateID')");

	        if ($query > 0) {
	        }

}

mysqli_close($conIP);

?>