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?




LVL 3
3xtr3m3dAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

$fields = 'id,itemcode';

Open in new window

0
3xtr3m3dAuthor Commented:
yeah but still values continue to loop

like this

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

Open in new window

0
imantasCommented:
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

imantasCommented:
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

0
3xtr3m3dAuthor Commented:
yes thats the coding part

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


sorry for the bad english
0
leakim971PluritechnicianCommented:
You may use : http://php.net/manual/en/function.in-array.php


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

Open in new window

0
imantasCommented:
Did you look at my last code snippet?
0
imantasCommented:
Ok, here's a full code that might work:
<?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, ',');



// Fixed fields
$fields = "id,itemcode";
// ----


// 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) {
                if (in_array($field, explode(',', $fields)))
                        $query .= "'$value',";
        }
        $query = rtrim($query, ',') . '),';
}

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

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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
3xtr3m3dAuthor Commented:
imantas: yes it works, thats what i was looking for.

leakim971 : if i provide custom fields as a array, your solution will work right?
0
3xtr3m3dAuthor Commented:
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

0
imantasCommented:
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

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

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

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

Open in new window


but still values coming in order of json
0
imantasCommented:
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.
0
imantasCommented:
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.
0
3xtr3m3dAuthor Commented:
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?

0
leakim971PluritechnicianCommented:
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

0
imantasCommented:
No, the values will come in the order declared in JSON. To make sure order of your field names mathes the values themselves, you should build your $fields as you can find in my comment above:

// 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


Please note that there are only a single line added to the original code:
    if (in_array($key, array("id", "itemcode")))

Open in new window

0
3xtr3m3dAuthor Commented:
yeah understood your point of rearranging array

Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.