Changing a numerical array into an associative array dynamically

This is kind of embarrassing, probably silly, but it might help someone. I was going to call this post "Freshman Problem and Solution," but it wasn't descriptive enough.

backstory is that I created a table with a dynamic number of columns based on an array drawn from an excel spreadsheet that the user uploads. Column titles in the database are simply 'col0', 'col1', 'col2' etc

So, DB::table()->insert() wants an associative array, which is a really pain in the patootie for an otherwise elegant system. Since I usually work with numerical array or arrays of objects, this threw me for a loop. This is what I was doing. Below is incorrect:

for($x=0; $x<count($cells); $x++){
    for($y=0; $y<count($cells[$x]); $y++){

        $insert[$x][] = array('col'.$x => $cells[$x][$y]);

    }
}

This yielded the annoying an probably obvious to many flaw that I spent way to long on.

$insert[$x] in general was looking something like this:

[0 => ['col0' => 'mystery data'],
1 => ['col1' => 'more mystery data'],
2 => ['col2' => 'etc']]

where I needed to look like this for this insert

['col0' => 'mystery data',
'col1' => 'more mystery data',
'col2' => 'etc']

So here's what I did to fix it. You don't have to kick me, I already kicked myself.

for($x=0; $x<count($cells); $x++){
    for($y=0; $y<count($cells[$x]); $y++){

        $cells[$x]['col'.$y] = $cells[$x][$y];
        unset($cells[$x][$y]);

    }
}

DB::table('my_table')->insert($cells);

The insert ran without issue. Thank you Google. Hopefully this helps someone.

Nakov

6 years ago

Level 73

@cookie_good this does not look pretty to me neither :) if you could share an input of what does the $cells array looks like before you start the loop, I bet you will get even better solution for yourself :)

there is an array_map method that you can use to transform the array. Turn the array into a collection is an option as well, and use the even more beautiful collections api given by Laravel.

cookie_good

6 years ago

Level 1

@nakov

This is $cells:

[0 => [0 => 'some data',
        1=> 'some more data',
        2=>'etc'],
1 => [0 => 'second array',
        1=>'this is cell B2',
        2=>'for example']
]

This was the direct output of phpoffice/phpspreadsheet:

$cells = $spreadsheet->getActiveSheet()->toArray();

Nakov

6 years ago

Best Answer

Level 73

Here is what I came up with using the Collection helper functions:

$cells = collect($cells = $spreadsheet->getActiveSheet()->toArray());

$cells->map(function ($value, $key) {
        return collect($value)->mapWithKeys(function ($value, $key) {
            return ['col' . $key => $value];
        });
    })
    ->toArray();

Output:

=> [
     [
       "col0" => "some data",
       "col1" => "some more data",
       "col2" => "etc",
     ],
     [
       "col0" => "second array",
       "col1" => "this is cell B2",
       "col2" => "for example",
     ],
   ]

In my opinion a bit more readable, nothing better here :)

cookie_good

6 years ago

Level 1

I like it. But suppose I wanted to go a tad further.

Let's say the first row of my spreadsheet is this:

[0 => [0 => 'id',
        1 => 'flavor',
        2 => 'price']

Let's say that I dynamically create a table based on the first row of the spreadsheet.

How would I format my insert for DB using collections and the map function?

Nakov

6 years ago

Level 73

@cookie_good I believe that using the excel library it can give you back each row with the header as a key. If it does not, then I would create a lookup table, like this:

$headers = ['id', 'flavor', 'price'];

$cells
    ->map(function ($value, $key) use ($headers) {
        return collect($value)->mapWithKeys(function ($value, $key) use ($headers) {
            return [$headers[$key] => $value];
        });
    })
    ->toArray();

cookie_good

6 years ago

Level 1

Great! Many Thanks!!!

Please or to participate in this conversation.