CODEWITHSUNDEEP
phpmysqlphp-curl
Chris Dormani
Chris Dormani

Reputation: 510

Storing json from curl to mysql table

I have a function that stores a user's IP address and then makes a curl request to a 3rd party API to acquire the geographical location based on the ip credentials. The returning data is in json format.

Problem: The function as it stands works and I can echo out the returning location 

 echo $api_result['country_name'];

What I am struggling to achieve is to understand how i can get these values stored in my db. When I run the insert, the columns in my db state array yet I have used json_decode .

store_each_visitors_ip_address_and_jurisdiction() {
    $date = date("d/m/Y");
    $visitors_ip = $_SERVER["REMOTE_ADDR"];
    $query = query("SELECT * FROM unique_visitors WHERE 'todays_date' ='$date'");
    $result = mysqli_query($connection,$query);
    if($result->num_rows == 0) {
        $access_key = '12345678912345'; 
            $ch = curl_init('http://api.ipstack.com/'.$visitors_ip.'?access_key='.$access_key.'');
            curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
            $json = curl_exec($ch); curl_close($ch);
            $api_result = json_decode($json, true); 
            $api_result_two = json_decode($json, true); 
            echo $api_result['country_name']; 
            echo $api_result_two['region_name']; 
            $insert_query = query("INSERT INTO unique_visitors(todays_date, ip_address, country, region) VALUES(CURRENT_TIMESTAMP,'{$visitors_ip}','{$api_result}','{$api_result_two}') ");
            confirm($query);
    }else{
        $row = fetch_array($result);
        if(!preg_match('/'. $visitors_ip .'/i', $row['ip_address'])) {
            $newIp = $row['ip_address'] . $visitors_ip;
            $updateQuery = "UPDATE unique_visitors SET ip_address = '$newIp',
 'views' = 'views'+1 WHERE 'todays_date'= '$date'";
            mysqli_query($connection,$updateQuery);
        }
    }
}

Upvotes: 0

Views: 558

Answers (1)

Barmar
Barmar

Reputation: 781370

You can't store the arrays in the table, you need to store the specific elements of the array.

$insert_query = query("INSERT INTO unique_visitors(todays_date, ip_address, country, region) 
        VALUES(CURRENT_TIMESTAMP,'{$visitors_ip}','{$api_result['country_name']}','{$api_result['region_name']}') ");

But you should use a prepared statement to protect against SQL injection.

$insert_stmt = $connection->prepare("INSERT INTO unique_visitors(todays_date, ip_address, country, region) 
        VALUES (CURRENT_TIMESTAMP, ?, ?, ?)";
$insert_stmt->bind_param("sss", $visitors_ip, $api_result['country_name'], $api_result['region_name']);
$insert_stmt->execute();

Upvotes: 3

Related Questions