Link to home
Start Free TrialLog in
Avatar of 3xtr3m3d
3xtr3m3d

asked on

get only needed values

[{"id":"1","itemcode":"FGH_00989","qty":"1"},{"id":"2","itemcode":"FAH_9876","qty":"1"}]

Open in new window


in the solution posted by JRandel
<?php
/**
 * Experts Exchange solution for JSON into MySQL
 * March 31st, 2011
 * 
 * This takes the JSON string, decodes it, then loops through the first item
 * to find the fields to insert into. It then loops the the entire JSON array
 * once more, constructing the INSERT statement as it goes.
 */

// set up some blank variables for use later on
(string) $server = 'DatabaseServer';
(string) $username = 'Username';
(string) $password = 'Password';
(string) $database = 'MySQL database to use';
(string) $table = 'MySQL table to insert into';
(string) $json = '[{"id":"1","itemcode":"FGH_00989","qty":"1"},{"id":"2","itemcode":"FAH_9876","qty":"1"}]';

// ignore after this point
(string) $fields = '';
(array) $array = json_decode($json);
(string) $query = '';

// sort out the MySQL connections and database selection
mysql_connect($server, $username, $password);
mysql_select_db($database);

// loop through the JSON data to pull out the field names
foreach ($array[0] as $key => $value) {
	$fields .= $key . ',';
}

// get rid of the trailing comma (used a couple more times in this script
$fields = rtrim($fields, ',');

// now we loop through all the data, building the INSERT as we go
$query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES ';
foreach ($array as $key => $data) {
	$query .= '(';
	foreach ($data as $field => $value) {
		$query .= "'$value',";
	}
	$query = rtrim($query, ',') . '),';
}

$query = rtrim($query, ',');

// run the statement
mysql_query($query) or die(mysql_error());
?>

Open in new window

it builds the query to insert all the available data.

is it possible to get values for given fields like id & itemcode?




Avatar of leakim971
leakim971
Flag of Guadeloupe image

This is the purpose of lines 28 to 31. Replace them by :

$fields = 'id,itemcode';

Open in new window

Avatar of 3xtr3m3d
3xtr3m3d

ASKER

yeah but still values continue to loop

like this

INSERT INTO xxx (id,itemcode) VALUES ('1','FGH_00989','5')

Open in new window

I'm not sure if I understood your question well, but maybe this is the point you were talking about:
// ...
$query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES ';
foreach ($array as $key => $data) {
        $query .= '(';
        foreach ($data as $field => $value) {
                // HERE $field refers to the field name like id, itemcode, qty, while $value is the value of the field
                $query .= "'$value',";
        }
        $query = rtrim($query, ',') . '),';
}

// ...

Open in new window

Oh, missed your last comment. Try this:

// ...
$query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES ';
foreach ($array as $key => $data) {
        $query .= '(';
        foreach ($data as $field => $value) {
                // HERE $field refers to the field name like id, itemcode, qty, while $value is the value of the field

                // This will only add values specified
                if ($field == "id" || $field == "itemcode")
                  $query .= "'$value',";
        }
        $query = rtrim($query, ',') . '),';
}

// ...

Open in new window

yes thats the coding part

is there way to get relevant values to given fields only?


sorry for the bad english
SOLUTION
Avatar of leakim971
leakim971
Flag of Guadeloupe image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Did you look at my last code snippet?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
imantas: yes it works, thats what i was looking for.

leakim971 : if i provide custom fields as a array, your solution will work right?
imantas:  this works perfectly fine :)

Thank you both

$fields = "id,itemcode";
foreach ($array as $key => $data) {
        $query .= '(';
        foreach ($data as $field => $value) {
                if (in_array($field, explode(',', $fields)))
                        $query .= "'$value',";
        }
        $query = rtrim($query, ',') . '),';
}

Open in new window

Please note that it won't work if you define your field names in different order than they are provided using JSON. If you face problems regarding this point, you should build your $fields as this:
// loop through the JSON data to pull out the field names
foreach ($array[0] as $key => $value) {
    if (in_array($key, array("id", "itemcode")))
        $fields .= $key . ',';
}

// get rid of the trailing comma (used a couple more times in this script
$fields = rtrim($fields, ',');

Open in new window

Yes, just an update, we need to explode the string to have an array :

  if( in_array($field, explode(",",$fields)) ) {
            $query .= "'$value',";
  }
imantas: i tried by

if (in_array($key, array("itemcode", "id")))

Open in new window


but still values coming in order of json
Yes, that is how it supposed to work. I mean that you may need to synchronize order of $fields and the values pushed into an sql query.
Sorry, forgot to mention:

if you need to reorder these values in the sql query (even though I don't see a point doing that), you should reorganize your JSON or $array, then the other part of the code will adopt to this.
yeah sorry i understand your point after i posted that comment :(

is it also possible if i give fields as

$fields = 'itemcode,id';

Open in new window


then values comes as same order?

You may sort the array by key :  http://www.php.net/manual/en/function.ksort.php

$fields = "id,itemcode";

$query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES ';
foreach ($array as $key => $data) {
	ksort($data);
	$query .= '(';
	foreach ($data as $field => $value) {
		if( in_array($field, explode(",",$fields)) ) {
			$query .= "'$value',";
		}
	}
	$query = rtrim($query, ',') . '),';
}

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
yeah understood your point of rearranging array

Thank you