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

Question

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?

Yes, I aready did it and it stores values comma separeted, but how to proceed then? — Serpentdriver, Mar 18 '23 at 08:36
What on the EARTH is "comma-separated"? Forget your database, you need an ARRAY that looks like the second table. THEN, after getting it, you'll be able to easily insert it in a loop. — Your Common Sense, Mar 18 '23 at 08:38
Sorry if i expressed myself wrong. That's what I meant: en,de,it,es and so on. — Serpentdriver, Mar 18 '23 at 08:41
PHP users definitely must learn basic array operations. https://3v4l.org/uBudi — Your Common Sense, Mar 18 '23 at 08:49

score -1 · Answer 1 · answered Mar 18 '23 at 15:29

-1

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.

answered Mar 18 '23 at 15:29

csaw

172
11

Learning how to execute SQL from PHP would be also a good idea – Your Common Sense Mar 18 '23 at 16:09
@YourCommonSense because of people like you SO has become a crap place. the guy is a noob, he will get there. ranting about what he should and shouldn't serves no purpose. you are talking about what to store in a database, when it is clear the guy doesn't even know how to write a query – csaw Mar 18 '23 at 16:11
Because of people like me, the guy got the answer he asked FOR and happily moved on several hours ago. Because of people like me, nowadays SO does less harm than it used to do, due to all that low quality hastily written answers from people who scarcely know the very basic stuff. – Your Common Sense Mar 18 '23 at 16:18
that's what you tell yourself while you cry your self to sleep? – csaw Mar 18 '23 at 16:20

score -1 · Answer 2 · answered Mar 18 '23 at 19:22

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']

Sure https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Your Common Sense, Mar 26 '23 at 18:10
Please refrain from writing answers before you take that look. Stack Overflow is not a place for amateur code. — Your Common Sense, Mar 27 '23 at 04:01

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

2 Answers2