Generate array with foreach PDO / MySQL

Question

I am doing a SELECT from MySQL that brings me a different number of rows for the same column. Then I:

• foreach to create an array
• merge the resulting arraywith another array
• echo using json_encode

My problem: I need to remove the extra array and the keys when generating my result. I tried different things but those didn't worked, could you tell me what I am doing wrong?

Here's my code:

    $dados2 = [
        'id' => $_POST['id_profissional']
    ];

    $sql2 = "
    SELECT nome
    FROM areas_de_atuacao_dos_advogados_e_escritorios 
    LEFT JOIN areas_de_atuacao ON areas_de_atuacao_dos_advogados_e_escritorios.id_areas_de_atuacao = areas_de_atuacao.id
    WHERE id_advogados_e_escritorios=:id
    ORDER BY principal DESC 
    ";

    $stmt2 = $pdo->prepare($sql2);
    $stmt2->execute($dados2);
    $areas_de_atuacao = $stmt2->fetchAll();


    $i = '0';
    foreach ($areas_de_atuacao as $row) {

        $aAreas_de_atuacao [] = array(
            'area_de_atuacao_'.$i   =>  $row["nome"]
        );
        $i++;

    }

With this output:

Array ( [0] => Array ( [area_de_atuacao_0] => Direito Civil ) [1] => Array ( [area_de_atuacao_1] => Direito Criminal ) [2] => Array ( [area_de_atuacao_2] => Direito Empresarial ) [3] => Array ( [area_de_atuacao_3] => Direito de Família ) [4] => Array ( [area_de_atuacao_4] => Direito das Sucessões ) [5] => Array ( [area_de_atuacao_5] => Direito do Trabalho ) )

But this is my desire output:

Array ( [area_de_atuacao_0] => Direito Civil [area_de_atuacao_1] => Direito Criminal [area_de_atuacao_2] => Direito Empresarial [area_de_atuacao_3] => Direito de Família [area_de_atuacao_4] => Direito das Sucessões [area_de_atuacao_5] => Direito do Trabalho )

Thank you for your time ;)

Answer 1

foreach ($areas_de_atuacao as $i => $row) {
    $aAreas_de_atuacao ['area_de_atuacao_'.$i] = $row["nome"];
}

Answer 2

Doing this functionally without the foreach loop, this an solution using array_walk to build the array with index number added to the key. use the sprintf for a little cleaner look than the concat 'area_de_atuacao_'. $key, but that would work too.

  $aAreas_de_atuacao = []; //Output array
  $names = array_columns($areas_de_atuacao, 'name'); //array of names
  array_walk($names, function ($name, $key) USE (&$aAreas_de_atuacao)  {//passing output array by reference
      $i = sprintf('area_de_atuacao_%d', $key);
      $aAreas_de_atuacao[$i] = $name; 
    });

Side note: In our code reviews we want developers to be consistent. In your code you started using brackets arrays [], then at the end you used array(), you want your code to flow I'd suggest you stick with one. Just something that stuck out to me, and it works the same, but it doesn't read the same.