Question 40266 in PHP Question posted in
Official manual can be found here.

[PHP][MySQL][JSON] How to group json and insert data for MySQL with PHP

These are the json data. name and vals are always fix, but the values are generated dynamically.

[
    {
        "name": "language",
        "vals": [
            "en",
            "de",
            "it",
            "es"
        ]
    },
    {
        "name": "currency",
        "vals": [
            "usd",
            "eur"
        ]
    }
]

I now need to convert this json data to a PHP array to store this data in the database, however this data needs to be grouped for later use. Using json_decode I created an array:

array(2) {
  [0]=>
  array(2) {
    ["name"]=>
    string(22) "language"
    ["vals"]=>
    array(4) {
      [0]=>
      string(2) "en"
      [1]=>
      string(2) "de"
      [2]=>
      string(2) "it"
      [3]=>
      string(2) "es"
    }
  }
  [1]=>
  array(2) {
    ["name"]=>
    string(22) "currency"
    ["vals"]=>
    array(2) {
      [0]=>
      string(3) "usd"
      [1]=>
      string(3) "eur"
    }
  }
}

I can save the array in the database, but I can’t save the data in the DB according to the following scheme:

name1   |   vals1   |   name2   |   vals2
language    |   en  |   currency    |   usd
language    |   en  |   currency    |   eur
language    |   de  |   currency    |   usd
language    |   de  |   currency    |   eur
language    |   it  |   currency    |   usd
language    |   it  |   currency    |   eur
language    |   es  |   currency    |   usd
language    |   es  |   currency    |   eur

Does anyone have a solution for this?

Tags:

2

Answers


  1. 0

    you have a table with fields;

    autoId (primary key), (langauge, currency [unique index])

    then use an insert query.

    your data should look like
    [{"language":["en","de","it","es"]},{"currency":["usd":"eur"]}]

    considering, your imported php data as is presented in your question is in a var called $data;

    $langaugeArr = $data[0]->vals;

    $currencyArr = $data[1]->vals;

    foreach($langaugeArr as $lang)
    foreach($currencyArr as $curr)$tmpArr[] = "(‘$lang’,’$curr’)";

    $sql = "insert ignore into tablename(language,currency) values " . implode(‘,’, $tmpArr);

    if this is external source data, you will have to sanitize data before insert.

    Login or Signup to reply.
  2. 0

    If I understood correctly, once you decode your json, you want to insert your json data in one DB table like this:

    name1   |   vals1   |   name2   |   vals2
language    |   en  |   currency    |   usd
language    |   en  |   currency    |   eur
language    |   de  |   currency    |   usd
language    |   de  |   currency    |   eur
language    |   it  |   currency    |   usd
language    |   it  |   currency    |   eur
language    |   es  |   currency    |   usd
language    |   es  |   currency    |   eur

    If I’m right, try this:

    $jsonDecoded = json_decode(your_json);
$insertQueries = [];

foreach ($jsonDecoded[0]['vals'] as $languaje){
    foreach ($jsonDecoded[1]['vals'] as $currency){
        array_push($insertQueries , "insert into table_name (name1, vals1, name2, vals2) 
        values 
        ('languaje', ". $languaje . ", 'currency'," . $currency . ")");
    }
 }

    What I’ve done is generate all queries you need to insert all rows you wanted using nested loops.

    With that you just need to execute all the generated queries inside a transaction in order to insert them all at once preventing errors.

    You can also replace ‘languaje’ and ‘currency’ in the query by $jsonDecoded[0][‘name’] and $jsonDecoded[1][‘name’]

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search