Updating multiple rows in mysql using php for a json array?

Question

I have a json array which needs to be parsed in such a way that I can update multiple rows in one mysql query.

This is my json array:

[{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1000"}
,{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1001"}
,{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1003"}]

I am trying a update query something like this:-

$query="UPDATE urd_voucher_table SET voucher_status ='$status', doc_no = '$docNo' WHERE voucher_id ='$vc_id'";

I want to know how can I parse the above json in order to get individual values for voucher_status, doc_no and voucher_id.

Any help is appreciated.Thank you.

Thank you @SameerJain can you help with some code.I am new to coding. — AndroidNewBee
– AndroidNewBee, Commented Apr 11, 2016 at 10:30

Pupil · Accepted Answer · 2016-04-11 10:32:05Z

1

Use json_decode()

Decode the JSON and loop over the decoded array.

<?php
$json = '[{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1000"},{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1001"},{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1003"}]';
$arr = json_decode($json, TRUE); // Observe the second parameter. If it is TRUE, associative array will be returned irrespective of what json is.
if (! empty($arr)) {
    foreach ($arr as $elem) {
        extract($elem);
        $query="UPDATE urd_voucher_table SET voucher_status ='$voucher_status', doc_no = '$doc_no' WHERE voucher_id ='$voucher_id'";
        // Add your code here.
    }
}

Reference of extract()

answered Apr 11, 2016 at 10:32

Pupil

24k6 gold badges49 silver badges71 bronze badges

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

Comments

Dipanwita Kundu · Accepted Answer · 2016-04-11 10:33:01Z

use json_decode() it will return an associative array with key, value pair like :

    $strJson = '[{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1000"},{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1001"},{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1003"}]';
  $arr = json_decode($strJson);

will return:

Array ( [0] => stdClass Object ( [doc_no] => ADV103 [voucher_status] => closed [voucher_id] => URDV1000 ) [1] => stdClass Object ( [doc_no] => ADV103 [voucher_status] => closed [voucher_id] => URDV1001 ) [2] => stdClass Object ( [doc_no] => ADV103 [voucher_status] => closed [voucher_id] => URDV1003 ) )

now using loop you can fetch data & insert/update database.

LMS94 · Accepted Answer · 2016-04-11 10:33:05Z

0

You need to decode the json string into an array, loop through the results and run the update query. Please see my example below...

$rows = json_decode('[{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1000"},{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1001"},{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1003"}]');

foreach ($rows as $row) {
    $query="UPDATE urd_voucher_table SET voucher_status ='{$row['voucher_status']}', doc_no = '{$row['doc_no']}' WHERE voucher_id ='{$row['voucher_id']}'";
    // ... Run query etc..
}

answered Apr 11, 2016 at 10:33

LMS94

1,0261 gold badge7 silver badges14 bronze badges

Comments

Manwal · Accepted Answer · 2016-04-11 10:33:33Z

0

First use json_encode() and then iterate result array in foreach. Like following:

$jsonString  = '[{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1000"},{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1001"},{"doc_no":"ADV103","voucher_status":"closed","voucher_id":"URDV1003"}]';
$json_decoded = json_decode($jsonString);
foreach($json_decoded as $index=>$val){
    $status = $val->voucher_status;
    $docNo = $val->doc_no;
    $vc_id = $val->voucher_id;
    $query="UPDATE urd_voucher_table SET voucher_status ='$status', doc_no = '$docNo' WHERE voucher_id ='$vc_id'";
    //execute query here
}

answered Apr 11, 2016 at 10:33

Manwal

23.9k15 gold badges65 silver badges95 bronze badges

Comments

Mangesh Sathe · Accepted Answer · 2016-04-11 10:40:58Z

0

More info on http://www.geeksengine.com/database/data-manipulation/bulk-insert.php

Follow these steps

decode json
apply foreach loop per record
Built and concat data for this part for each record in JSON for e.g. (a,e,r),(br,t,y),(t,y,c);
Hard code this part in query "Insert into table (c1,c2,c3) VALUES "
And attche data that was created in step 3.

Final query will be

 Insert into table (c1,c2,c3) VALUES . STEP 3

answered Apr 11, 2016 at 10:40

Mangesh Sathe

2,1835 gold badges26 silver badges42 bronze badges

Collectives™ on Stack Overflow

Updating multiple rows in mysql using php for a json array?

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related