Sending JSON information to a database using PHP?

Question

I am trying to send data from a JSON file to a MySQL database using PHP.

I have 99% work, but I ran into a small problem that I cannot understand. Here is my code:

$con = mysqli_connect($host, $username, $password, $dbname) or die('Error in Connecting: ' . mysqli_error($con));

$st = mysqli_prepare($con, 'INSERT INTO url_feed(url, results, current_date, networks, identifier) VALUES (?, ?, ?, ?, ?)');

mysqli_stmt_bind_param($st, 'sssss', $url, $results, $current_date, $networks, $identifier);

$filename = 'https://www.example.com/random.json';
$json = file_get_contents($filename);   

$data = json_decode($json, true);

foreach ($data as $row) {
$url = $row['url'];
$identifier = $row['identifier'];
$current_date = $row['current_date'];
$results = $row['results'];
$networks = $row['networks'];

    mysqli_stmt_execute($st);
}

mysqli_close($con);

Here's a copy of JSON with three objects in it:

[
   {
      "url":"http://example1.com",
      "identifier":495755330,
      "current_date":"2015-12-30 17:05:45",
      "results":3,
      "networks":{
         "FaceBook":{"detected":true,"result":"no-result"},
         "Twitter Inc":{"detected":false,"result":"no-result"},
         "Pinterest.com":{"detected":true,"result":"no-result"},
         "Other Sites":{"detected":true,"result":"some-result"}
      }
   },
   {
      "url":"http://example2.com",
      "identifier":495755331,
      "current_date":"2015-12-30 17:05:46",
      "results":0,
      "networks":{
         "FaceBook":{"detected":false,"result":"what-result"},
         "Twitter Inc":{"detected":false,"result":"some-result"},
         "Pinterest.com":{"detected":false,"result":"some-result"},
         "Other Sites":{"detected":false,"result":"what-result"}
      }
   },
   {
      "url":"http://example3.com",
      "identifier":495755332,
      "current_date":"2015-12-30 17:05:47",
      "results":1,
      "networks":{
         "FaceBook":{"detected":false,"result":"some-result"},
         "Twitter Inc":{"detected":true,"result":"some-result"},
         "Pinterest.com":{"detected":false,"result":"some-result"},
         "Other Sites":{"detected":false,"result":"some-result"}
      }
   }
]

Right now, if I run the script, it inserts it into the database as follows:

id | url                 | results   | current_date        | networks | identifier  | status
1  | http://example1.com | 3         | 2015-12-30 17:05:45 | Array    | 495755330   | queued
2  | http://example2.com | 0         | 2015-12-30 17:05:46 | Array    | 495755331   | queued
3  | http://example3.com | 1         | 2015-12-30 17:05:47 | Array    | 495755332   | queued

But here is how I want :

id | url                 | results   | current_date        | networks                           | identifier | status
1  | http://example1.com | 3         | 2015-12-30 17:05:45 | FaceBook,Pinterest.com,Other Sites | 495755330  | queued
2  | http://example3.com | 1         | 2015-12-30 17:05:47 | Twitter Inc                        | 495755332  | queued

Here is the part I cannot understand:

It tries to enter as an array that obviously does not work - and it just inserts the text “Array” under the column networks. I only want to insert into the database if the parameter is detectedset to true. If this is not the case, I do not want this social network to be listed in the database.

​​ true , , . , , .

Answer 1

$networks = $row['networks']; :

$a = json_decode($row['networks'], true);
$b = array_filter($a, function($el) {
  if ($el['detected'] == true) {
    return true;
  }
});
$c = implode(', ', array_keys($b));
$networks = $c;

Answer 2

, $netwrok. ,

    foreach ($data as $row) {
$url = $row['url'];
$identifier = $row['identifier'];
$current_date = $row['current_date'];
$results = $row['results'];
$networks = $row['networks'];
//**insert you checks here ***//
    mysqli_stmt_execute($st);
}

Answer 3

$data = json_decode($json, true);

foreach ($data as $row) {
    $url = $row['url'];
    $identifier = $row['identifier'];
    $current_date = $row['current_date'];
    $results = $row['results'];
    $network_row = $row['networks'];

    $networks = '';

    foreach($network_row as $key => $val) {
        if ($val->detected == true) {
            $networks .= $key . ',';
        }
    }

    if (mb_strlen($networks, 'utf-8') > 0) {
        $networks = substr($networks, 0, mb_strlen($networks, 'utf-8')-1);
        mysqli_stmt_execute($st);
    }
}

Answer 4

implode() . explode().

http://php.net/manual/en/function.implode.php