CODEWITHSUNDEEP
phpmysqlarraysjson
AndroidNewBee
AndroidNewBee

Reputation: 744

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

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.

Upvotes: 1

Views: 2241

Answers (5)

Mangesh Sathe
Mangesh Sathe

Reputation: 2177

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

Follow these steps

  1. decode json
  2. apply foreach loop per record

  3. Built and concat data for this part for each record in JSON for e.g. (a,e,r),(br,t,y),(t,y,c);

  4. Hard code this part in query "Insert into table (c1,c2,c3) VALUES "

  5. And attche data that was created in step 3.

Final query will be 

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

Upvotes: 0

Manwal
Manwal

Reputation: 23816

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
}

Upvotes: 0

LMS94
LMS94

Reputation: 1036

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..
}

Upvotes: 0

Dipanwita Kundu
Dipanwita Kundu

Reputation: 1667

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.

Upvotes: 0

Pupil
Pupil

Reputation: 23948

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()

Upvotes: 1

Related Questions