subhra subhra - 1 month ago 5
MySQL Question

How to insert data into table as per Json object value using PHP and Mysql

I need one help. I need to insert multiple data as per some Json object value into table using PHP and MySQL. I am explaining my code below.

$commnt=[{
'day_id':2,
'comment':'vodka1'
},{
'day_id':3,
'comment':'vodka2'
}
]

$result=[{
'day_id':1,
'restaurant':'193'
},{
'day_id':2,
'restaurant':'193'
},{
'day_id':3,
'restaurant':'193'
}
]


Here i need to enter all data from both Json object into this below table as per
day_id
. I am explaining column of my table below.


db_details:


id day_id restaurant comment


Here my requirement is when
day_id
will same the respective
comment
field value will entry into table other wise the
comment
filed value will remain blank.The expected out put is given below.

id day_id restaurant comment

1 1 193

2 2 193 vodka1

3 3 193 vodka3


My query is given below.

$insertintodetails=mysqli_query($connect,'INSERT INTO db_details
(day_id,restaurant,comment) values ("'. $result.'","'.$result[$i]['restaurant'].'","'.$commnt[$i]['comment'].'")');


Here there may be many use cases,like both Json object length may be same or different but the comment should insert as per
day_id
otherwise it will remain blank. In my query i can not insert as per required. Please help me to resolve this issue.

Answer

I create an example from above your question detail. You can try below code. And it should work for you...

//For example taken array and convert it to JSON    
$comment = json_encode(array(array('day_id' => '2', 'comment' => 'vodka1'), array('day_id' => '3', 'comment' => 'vodka2')));
$result = json_encode(array(array('day_id' => '1', 'restaurant' => '193'), array('day_id' => '2', 'restaurant' => '193'), array('day_id' => '3', 'restaurant' => '193')));

//Convert JSON to array...
$arrComment = json_decode($comment, true);
$arrResult = json_decode($result, true);

foreach($arrResult AS $keyResult => $dataResult){
    $day_id = $dataResult['day_id'];//day_id
    $restaurant = $dataResult['restaurant'];//rasturant
    $strComment = '';//comment
    //Check and extract comment value from multi dimensional comment($arrComment) array...
    if($getComment = find_comment_with_dayid($arrComment, $day_id)){
      $strComment = $getComment;
    }

    //Insert records...
    $insertintodetails=mysqli_query($connect,'INSERT INTO db_details
(day_id, restaurant, comment) values ("'. $day_id .'","'. $restaurant .'","'. $strComment .'")');
}


//Function will return comment for matched day_id
function find_comment_with_dayid($arrComment, $fieldKey) {
    foreach($arrComment as $indCommenr => $dataComment) {
        //Check for day_id, matched then return comment...
        if($dataComment['day_id'] == $fieldKey) return $dataComment['comment'];
    }
    return FALSE;
}

Here, I taken example array for avoid JSON issue reported on your question. Hope this work well!

Comments