使用PHP将JSON对象中的元素插入数据库

Question

I'm just a student so apologies in advance if this post isn't clear.


I'm working on importing the product data from a JSON response into a product DB. My goal is to just insert the elements from the JSON object into the database.

Currently I'm getting "Undefined index: product" errors and when corrected, "Invalid foreach statement" errors. Really struggling with understanding where and what my mistakes are.

If anyone could take a look over this code and see what I'm doing wrong I'd appreciate it greatly.

Code:

<?php
$params = http_build_query(array(
  "api_key" => "***",
  "format" => "json"
));

$result = file_get_contents(
    'https://www.parsehub.com/api/v2/projects/***/last_ready_run/data?'.$params,
    false,
    stream_context_create(array(
        'http' => array(
            'method' => 'GET'
        )
    ))
);

$result=gzdecode($result);
$parsed_result = json_decode($result, true);
$product[] = $parsed_result['product'];
    foreach($product as $item){ 
        $updated = $item['updated'];
        $name = $item['name'];
        $url = $item['url'];
        $currentPrice = $item['currentPrice'];
        $originalPrice = $item['originalPrice'];
        $picture = $item['picture'];
        $brand = $item['brand'];
        $model = $item['model'];

        require 'db_configuration.php';
        $sql2 = "INSERT INTO storeA (name, url, currentPrice, originalPrice, picture, brand, model, updated) VALUES ('{$name}',{$url},{$currentPrice},{$originalPrice},{$picture},{$brand},{$model},{$updated})";
        $result = run_sql($sql2);

    }
?>

JSON:

{
  "links": [
    {
      "link": "http://www.***.com",
      "product": [
        {
          "updated": "****",
          "name": "****",
          "url": "****",
          "currentPrice": "$****",
          "originalPrice": "$****",
          "picture": "http://****.jpg",
          "picture_url": "****",
          "brand": "***",
          "extra": "****"
        },
        {
          "updated": "****",
          "name": "****",
          "url": "****",
          "currentPrice": "$****",
          "originalPrice": "$****",
          "picture": "http://****.jpg",
          "picture_url": "****",
          "brand": "***",
          "extra": "****"
        }
      ]
    },
    {
      "link": "http://www.***.com",
      "product": [
        {
          "updated": "****",
          "name": "****",
          "url": "****",
          "currentPrice": "$****",
          "originalPrice": "$****",
          "picture": "http://****.jpg",
          "picture_url": "****",
          "brand": "***",
          "extra": "****"
        },
        {
          "updated": "****",
          "name": "****",
          "url": "****",
          "currentPrice": "$****",
          "originalPrice": "$****",
          "picture": "http://****.jpg",
          "picture_url": "****",
          "brand": "***",
          "extra": "****"
        }
      ]
    },
    {
      "link": "http://www.***.com",
      "product": [
        {
          "updated": "****",
          "name": "****",
          "url": "****",
          "currentPrice": "$****",
          "originalPrice": "$****",
          "picture": "http://****.jpg",
          "picture_url": "****",
          "brand": "***",
          "extra": "****"
        },
        {
          "updated": "****",
          "name": "****",
          "url": "****",
          "currentPrice": "$****",
          "originalPrice": "$****",
          "picture": "http://****.jpg",
          "picture_url": "****",
          "brand": "***",
          "extra": "****"
        }
      ]
    }
  ]
}

DB CONFIG

<?php
DEFINE('DATABASE_HOST', 'localhost');
DEFINE('DATABASE_DATABASE', '***');
DEFINE('DATABASE_USER', 'root');
DEFINE('DATABASE_PASSWORD', '');

$db = new mysqli(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_DATABASE);
$db->set_charset("utf8");
function run_sql($sql_script)
{
    global $db;
    // check connection
    if ($db->connect_error)
    {
        trigger_error(print_r(debug_backtrace()).'.Database connection failed: '  . $db->connect_error, E_USER_ERROR);
    }
    else
    {
        $result = $db->query($sql_script);
        if($result === false)
        {
            trigger_error('Stack Trace: '.print_r(debug_backtrace()).'Invalid SQL: ' . $sql_script . '; Error: ' . $db->error, E_USER_ERROR);
        }
        else if(strpos($sql_script, "INSERT")!== false)
        {
            return $db->insert_id;
        }
        else
        {
            return $result;
        }
    }
}

?>

Answer 1

Start by var_dumping the result of json_decode($result, true) - the array structure is:

array(1) {
  'links' =>
  array(1) {
    [0]
    array(2) {
      'link' =>
      string(18) "http://www.***.com"
      'product' =>
      array(2) {
        ...
      }
    }
  }
}

So, $parsed_result is an array with a single element with links as the key. This element contains the array of link/product pairs.

So, if you want to get all the products in the json response, you need to do this:

foreach ($link in $parsed_results['links']) {
    foreach ($product in $link['product']) {
        // construct your array
    }
}

This will work - but your database query is open to an SQL injection attack. You should never use interpolation to inject variables into an SQL query - you should never do this:

$sql2 = "INSERT INTO storeA (name, url, currentPrice, originalPrice, picture, brand, model, updated) VALUES ('{$name}',{$url},{$currentPrice},{$originalPrice},{$picture},{$brand},{$model},{$updated})";

Instead, you should be using prepared queries with parameters:

$insertProductQuery = mysqli_prepare("INSERT INTO storeA (name, url, currentPrice, originalPrice, picture, brand, model, updated) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

mysqli_stmt_bind_param($insertProductQuery, 'ssddssss', $name, $url, $currentPrice, $originalPrice, $picture, $brand, $model, $updated);

(I made an assumption on your database schema with the parameter types - although you shouldn't really be using real/float/double for storing currency values, either)

Better yet, use PDO and you can you used named parameters.