CODEWITHSUNDEEP
phpmysqlarraysjson
Saeed Heidarizarei
Saeed Heidarizarei

Reputation: 8916

Insert a Column of json Object to New Column via PHP

I Have a json Column and I Want to Collect id to a New Column With id2 Name. This Is one Row of json Column

[{"id":"26","answer":[{"option":"3","text":"HIGH"}],"type":"a"},
 {"id":"30","answer":[{"option":"3","text":"LOW"}],"type":"b"},
 {"id":"31","answer":[{"option":"3","text":"LOW"}],"type":"c"}]

I Mean How to add 26,30,31 40,40,10 52,12,12 to New Column. My Table Name is user_survey_start My json Column Name is survey_answersand My New id Column is id2

<?php
$con=mysqli_connect("localhost","root","","arrayy");
// Check connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql="SELECT `survey_answers` FROM `user_survey_start`";

if ($result=mysqli_query($con,$sql)) {
    while ($row = mysqli_fetch_row($result)) {
        $json = $row[0];
        $jason_array  = json_decode($json,true);

        // id   
        $id = array();
        foreach ($jason_array as $data){
            $id[] = $data['id'];
            // here code to insert/update values to db column
        }
        echo implode(',',$id)."</br>";
    }
}
mysqli_close($con);
?>

Upvotes: 2

Views: 1479

Answers (2)

Saeed Heidarizarei
Saeed Heidarizarei

Reputation: 8916

Solved With This Code

         $id = array(); 
            foreach ($jason_array as $data) {
                            $id[] = $data['id'];
                        }
                        $ids= implode(',',$id);
                        $sql1="update user_survey_start set id2='$ids' where id_s=".$row[1];//run update sql
                        echo $sql1."<br>";
                        mysqli_query($con,$sql1);               
        // awnser2          
        $answers = array();
        foreach ($jason_array as $data) {
                foreach($data['answer'] as $ans){
                $answers[] =$ans['text'] ;
                }
        }
        $answers= implode(',',$answers);
        $sql3="update user_survey_start set awnser2='$answers' where id_s=".$row[1];//run update sql
        echo $sql3."<br>";
        mysqli_query($con,$sql3);                                                                           
      }
}

Upvotes: 0

Albert221
Albert221

Reputation: 7052

Firstly, you have to update your table schema, so it will have new column named id2.

ALTER TABLE `user_survey_start` ADD `id2` VARCHAR(255) NOT NULL

You can delete NOT from NOT NULL if you want this column to be optional.

Then you just only have to insert these IDs from JSON to your newly created id2 column.

$id = implode(',', $id);
mysqli_query($con, "UPDATE `user_survey_start` SET `id2` = $id WHERE `id` = {$row['id']}");

Upvotes: 2

Related Questions