How to insert .json file having null values to mysql?

Question

i want to insert .json data to MySQL. I used code from here(Decode Json data Array and insert in to mysql) but i have a trouble with null values.

json files (example)

[{'counsel_id':'2019-000005', 'big_cate':'Smartphone', 
'mid_cate':'Orange', 'small_cate':'AA', 
'title':'faceID doesn't work',
 'question_date':'2019-08-01', 
'question':'faceID doesn't work. how can i fix it?',
'answer-date':'2019-08-01',
'answer':'hello blah blah'
},
{'counsel_id':'2019-000015', 'big_cate':'Smartphone',
 'mid_cate':'Star', 'small_cate':'BB', 
'title':'Fingerprint Recognition doesn't work',
 'question_date':'2019-08-10', 
'question':'Fingerprint Recognition doesn't work. how can i fix it?',
'answer-date':'2019-08-11',
'answer':'hello blah blah'
},
{'counsel_id':'2019-000018', 'big_cate':'Smartphone', 
'mid_cate':'Orange', 'small_cate':'AA',
 'title':'The screen broken', 
'question_date':'2019-08-16', 
'question':'How much will it cost to fix it??'
}]

MySQL TABLE

CREATE TABLE IF NOT EXISTS `counsel` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `counsel_id` CHAR(13) NOT NULL,
  `big_cate` VARCHAR(15) NOT NULL,
  `mid_cate` VARCHAR(15) NOT NULL,
  `small_cate` VARCHAR(15) NOT NULL,
  `title` VARCHAR(45) NOT NULL,
  `question_date` DATETIME NOT NULL,
  `question` TEXT NOT NULL,
  `answer_date` DATETIME NULL,
  `answer` TEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

php code

<?php

$jsonFile="final_03.json";
$jsondata = file_get_contents($jsonFile);
$data = json_decode($jsondata, true);

$servername = "localhost";
$username = "root";
$password = "mypassword";
$dbname = "myDBname";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


foreach ($data as $row) {
    $sql = "INSERT INTO counsel (counsel_id, big_cate, mid_cate, small_cate,
 title, question_date, question) 
VALUES ('" . $row["receiptNum"] . "', '" . $row["gooboon"] . "',
'" . $row["itemCode"] . "','" . $row["item"] . "',
'" . $row["title"] . "','" . $row["date"] . "',
'" . $row["question"] . "')";
    $conn->query($sql);
}

$conn->close();
?>

I want to insert all of json data to MySQL, but have an error when json file doesn't have 'answer-date', 'answer'. If json list does not have 'answer-date', 'answer', I would like to add a null value. Can anyone help me?

(+) i solved using function (array_key_exists())

    if (array_key_exists('answerDate', $row)) {
        $sql = "INSERT INTO counsel (counsel_id, big_cate, mid_cate, small_cate, title, question_date, question, answer_date, answer) VALUES ('" . $row["receiptNum"] . "', '" . $row["gooboon"] . "','" . $row["itemCode"] . "','" . $row["item"] . "','" . $row["title"] . "','" . $row["date"] . "','" . $row["question"] . "', '" . $row["answerDate"] . "','" . $row["answer"] . "')";
        echo "answer exists!";
    } else {
        $sql = "INSERT INTO counsel (counsel_id, big_cate, mid_cate, small_cate, title, question_date, question) VALUES ('" . $row["receiptNum"] . "', '" . $row["gooboon"] . "','" . $row["itemCode"] . "','" . $row["item"] . "','" . $row["title"] . "','" . $row["date"] . "','" . $row["question"] . "')";
        echo "answer doesn't exists!";
    }
    $conn->query($sql);

Serghei Leonenco · Accepted Answer · 2019-08-16 03:08:36Z

0

Try this, because you never know if the coming value will be empty or not, it will be better way to check it first and assign it to var for better manipulation:

SQL Table:

CREATE TABLE IF NOT EXISTS `counsel` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `counsel_id` CHAR(13) NOT NULL,
  `big_cate` VARCHAR(15) NULL,
  `mid_cate` VARCHAR(15) NULL,
  `small_cate` VARCHAR(15) NULL,
  `title` VARCHAR(45) NULL,
  `question_date` DATETIME CURRENT_TIMESTAMP,
  `question` TEXT NULL,
  `answer_date` DATETIME ON UPDATE CURRENT_TIMESTAMP,
  `answer` TEXT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

//represent SQL table attributes
//better way to add attribute NULL on EMPTY
 $data_init = [
  `counsel_id`,
  `big_cate`,
  `mid_cate`,
  `small_cate`,
  `title`,
  `question_date`,
  `question`,
  `answer_date`,
  `answer`,
}



     $json = //incoming JSON array

        $arrays = json_decode($json) //we have got an array with items

        //next run thru foreach loop
        foreach($data_init as $key => $data){
            (!isset($arrays[$data])) ? $small_cate = 'Not provided' : $data[`small_cate`];
            //... sinse you know exactly all rows just run like this thru it
            // and get all vars assigned
        }

    $sql = "INSERT INTO counsel (counsel_id, big_cate, mid_cate, small_cate,
     title, question_date, question) 
    VALUES (
         '" . $receiptNum . "'
         //...all values from $data_init
    )";
    $conn->query($sql);

//here bind() vars to query

edited Aug 16, 2019 at 3:08

answered Aug 16, 2019 at 2:48

Serghei Leonenco

3,5272 gold badges11 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

wendykr Over a year ago

Hi :) I saw the answer before you edit but it didn't work. But I got an idea from your answer and fixed it using a function to check the key value! Thank you.

Serghei Leonenco Over a year ago

@wendykr what wrong in my code, tell me and i will fix for future reference.

wendykr Over a year ago

Sorry for the late answer. Error occurs in ' //...all values from $data_init' here and error message is 'Undefined variable: receiveNum in /Users/.../.../counsel.php on line...'

Serghei Leonenco Over a year ago

@wendykr Oh, Hmm, that is interesting... i will catch on that, Thanks.

Collectives™ on Stack Overflow

How to insert .json file having null values to mysql?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related