Join two database tables then group the results as subarrays

Question

I am having a hard time relating the result set of two database queries. There can be zero or many related rows between my "barang" rows and my "harga" rows.

My CodeIgniter queries look like this:

$barang = $this->db
    ->select('pj_barang.*')
    ->where('dihapus', 'tidak')
    ->get('pj_barang')->result_array();

$harga = $this->db
    ->select('pj_detailsupplier.*')
    ->join('pj_barang', 'pj_detailsupplier.id_barang = pj_barang.id_barang', 'right')
    ->get('pj_detailsupplier')
    ->result_array();

These queries populate array structures like these (many columns removed for simplicity):

$barang=array(
    [
        'id_barang' => 01,
        'nama_barang' => "laptop",
        'merk' => 'lenovo'
    ],
    [
        'id_barang' => 02,
        'nama_barang' => "RAM",
        'merk' => 'lenovo'
    ],
    [
        'id_barang' => 03,
        'nama_barang' => "Keyborad",
        'merk' => 'lenovo'
    ],
);

$harga=array(
    [
        'id_barang' => 01,
        'harga' => 2000000,
    ],
    [
        'id_barang' => 02,
        'harga' => 100000,
    ]
);

I want to join them to form a multidimensional array with subsets of data grouped by id_barang, but my code is still wrong.

$result=array();
foreach ($barang as $value) {
    foreach ($harga as $k => $val) {
        $result[$value['id_barang'] = $val["id_barang"]] = [
            'harga' => $val["harga"]
        ];
    }
}
echo "<pre>";
echo print_r($result);

The output is:

Array
(
   [1] => Array
    (
        [harga] => 2000000
    )
   [2] => Array
    (
        [harga] => 100000
    )
)

The desired result:

Array
(
  [0] => Array
  (
    [id_barang] => 01
    [nama_barang] => laptop
    [merk] => lenovo
    [dataharga] => Array
            (
                [0] => Array
                    (
                        [id_barang] => 01
                        [harga] => 2000000
                    )
            )
  )
  [1] => Array
  (
    [id_barang] => 02
    [nama_barang] => RAM
    [merk] => lenovo
    [dataharga] => Array
            (
                [1] => Array
                    (
                        [id_barang] => 02
                        [harga] => 100000
                    )
            )
  )
  [2] => Array
  (
    [id_barang] => 03
    [nama_barang] => Keyboard
    [merk] => lenovo
    [dataharga] => Array
            (
                [2] => Array
                    (
                        [id_barang] => ""
                        [harga] => ""
                    )
            )
  )
)

Answer 1

Try the following code and see whether it works:

$barang=array(
    [
        'id_barang' => 01,
        'nama_barang' => "laptop",
        'merk' => 'lenovo'
    ],
    [
        'id_barang' => 02,
        'nama_barang' => "RAM",
        'merk' => 'lenovo'
    ],
    [
        'id_barang' => 03,
        'nama_barang' => "Keyborad",
        'merk' => 'lenovo'
    ],
);
$harga=array(
    [
        'id_barang' => 01,
        'harga' => 2000000,
    ],
    [
        'id_barang' => 02,
        'harga' => 100000,
    ]
    );


foreach ($barang as &$value) {
    $index =array_search($value['id_barang'], array_column($harga, 'id_barang'));

    if($index > -1) {
      $value['dataharga'] = array($harga[$index]); // Do you really need additional array() ?
    } else {
      $value['dataharga'] = array(['id_barang' => "", 'harga' => ""]); // Do you really need additional array() ?
    }
}

echo "<pre>";
echo print_r($barang);