連接兩個資料庫表，然后將結果分組為子陣列

我很難關聯兩個資料庫查詢的結果集。我的“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] => ""
                    )
            )
  )
)

uj5u.com熱心網友回復：

試試下面的代碼，看看它是否有效：

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

uj5u.com熱心網友回復：

從您隨后的問題來看，我會推薦一個 LEFT JOIN 并且僅在不為空pj_detailsupplier時鏈接表。id_barang

然后使用 PHP 在值上迭代行和分組，id_barang并將“harga”資料推送到dataharga子陣列中。

推薦的（未經測驗的）CodeIgniter 代碼：

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

標籤：php 代码点火器 加入 多维数组 分组

上一篇：加入多個表并從第三個表中獲取總行數

下一篇：如何檢查一個物件是否在另一個查詢集中