加入两个数据库表，然后将结果分组为子阵列

Question

我很难关联两个数据库查询的结果集。我的“ barang”行和“ harga”行之间可能有零或许多相关的行。

我的codeigniter查询看起来像这样：

$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();

这些查询填充了类似的数组结构（为简单起见，许多列删除了许多列）：

$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,
    ]
);

我想加入它们以形成一个多维数组，其中包含由id_barang分组的数据子集，但是我的代码仍然错了。

$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);

输出是：

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

所需的结果：

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

尝试以下代码，看看它是否有效：

$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);

Answer 2

以您的后续问题当ID_BARANG不是null时。

然后，使用php在id_barang值上迭代行和组，然后将“ Harga”数据推入dataharga subarre。

推荐（未经测试的）代码签名代码：

$rows = $this->db
    ->select('pj_barang.*, pj_detailsupplier.*')
    ->from('pj_barang')
    ->join('pj_detailsupplier', 'pj_barang.id_barang = pj_detailsupplier.id_barang AND pj_barang.id_barang IS NOT NULL', 'LEFT')
    ->where('dihapus', 'tidak')
    ->get()
    ->result_array();

$grouped = [];
foreach ($rows as $row) {
    if (!isset($grouped[$row['id_barang']])) {
        $grouped[$row['id_barang']] = [
            'id_barang' => $row['id_barang'],
            'kode_barang' => $row['kode_barang'],
            'nama_barang' => $row['nama_barang'],
            'total_stok' => $row['total_stok'],
            'harga_beli' => $row['harga_beli'],
            'id_kategori_barang' => $row['id_kategori_barang'],
            'id_merk_barang' => $row['id_merk_barang'],
            'tanggal_expired' => $row['tanggal_expired'],
            'deskripsi' => $row['deskripsi'],
            'gambar' => $row['gambar'],
            'dihapus' => $row['dihapus'],
        ];
    }
    $grouped[$row['id_barang']]['dataharga'][] =  [
        'id_detailsupplier' => $row['id_detailsupplier'],
        'id_supplier' => $row['id_supplier'],
        'no_batch' => $row['no_batch'],
        'stok' => $row['stok'],
        'tanggal' => $row['tanggal'],
        'status_detailsupplier' => $row['status_detailsupplier'],
    ];
}
var_export(array_values($grouped));
// return array_values($grouped));