3xtr3m3d
asked on
get only needed values
[{"id":"1","itemcode":"FGH_00989","qty":"1"},{"id":"2","itemcode":"FAH_9876","qty":"1"}]
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());
?>
it builds the query to insert all the available data.is it possible to get values for given fields like id & itemcode?
ASKER
yeah but still values continue to loop
like this
like this
INSERT INTO xxx (id,itemcode) VALUES ('1','FGH_00989','5')
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, ',') . '),';
}
// ...
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, ',') . '),';
}
// ...
ASKER
yes thats the coding part
is there way to get relevant values to given fields only?
sorry for the bad english
is there way to get relevant values to given fields only?
sorry for the bad english
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Did you look at my last code snippet?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
imantas: yes it works, thats what i was looking for.
leakim971 : if i provide custom fields as a array, your solution will work right?
leakim971 : if i provide custom fields as a array, your solution will work right?
ASKER
imantas: this works perfectly fine :)
Thank you both
$fields = "id,itemcode";
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, ',') . '),';
}
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, ',');
Yes, just an update, we need to explode the string to have an array :
if( in_array($field, explode(",",$fields)) ) {
$query .= "'$value',";
}
if( in_array($field, explode(",",$fields)) ) {
$query .= "'$value',";
}
ASKER
imantas: i tried by
but still values coming in order of json
if (in_array($key, array("itemcode", "id")))
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.
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.
ASKER
yeah sorry i understand your point after i posted that comment :(
is it also possible if i give fields as
then values comes as same order?
is it also possible if i give fields as
$fields = 'itemcode,id';
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, ',') . '),';
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
yeah understood your point of rearranging array
Thank you
Thank you
Open in new window