how to combine 2 json data with php with different key but same value

I had some problem when I realize Codeigniter can run with a single MySQL query on the single server not run a sync with 2 different servers, so I decide to divide MySQL result into a single array

Here is my sample JSON data I got:

[{"id":"4416","id_user":"123@example.net","tagihan":"150000","tanggal":"2021-04-08 >00:00:00","tanggal_input":"Apr 14, 2020","metode":"CASH","bulan":"Apr->2021","tanggal_bayar":"14-Apr-2020","format_harga":"Rp 150,000"}]
[{"id":"1","username":"123@example"}]

Here is data1 from query->json codeigniter

[{"id":"4416","id_user":"123@example.net","tagihan":"150000","tanggal":"2021-04-08 >00:00:00","tanggal_input":"Apr 14, 2020","metode":"CASH","bulan":"Apr->2021","tanggal_bayar":"14-Apr-2020","format_harga":"Rp 150,000"}]

And this is data2

[{"id":"1","username":"123@example","saldo":"1000000","alamat":"example"}]

As u can see id_user == with username, how can combine those data into a single key id_user with saldo, alamat into a single JSON index?

public function getRekap(){
        $this->db2->SELECT("*,DATE_FORMAT(tanggal, '%b-%Y') AS bulan, DATE_FORMAT(tanggal_input, '%d-%b-%Y') AS tanggal_bayar, concat('Rp ', format( tagihan, 0)) AS format_harga, date_format(tanggal_input,'%b %d, %Y') AS tanggal_input")
                ->FROM('pembayaran')
                ->ORDER_BY('tanggal_input','DESC');
        $query = $this->db2->get();
        $output = array('data' => array());
        if($query->num_rows() > 0){
            return $query->result_array();
        }else{
            return array();
        }
    }

public function getTest(){
      $this->db->SELECT("id,username")
              ->FROM('usersinfo');
      $query = $this->db->get();
      $output = array('test' => array());
      if($query->num_rows() > 0){
          return $query->result_array();
      }else{
          return array();
      }
    }

public function getRekap()
    {
        $dataArray = array(
            'data' => $this->Home_m->getRekap(),
            'test' => $this->Home_m->getTest()
        );

        echo json_encode($dataArray);
    }

All I want is the result is the same as like MySQL query with where id_user = username;

Solution

I have two codes for you can apply both of them both of them will works.

First you can use do it in one hit, just use join.

public function getRekap()
  {
    $this->db2->SELECT("pembayaran.*,DATE_FORMAT(pembayaran.tanggal, '%b-%Y') AS bulan, DATE_FORMAT(pembayaran.tanggal_input, '%d-%b-%Y') AS tanggal_bayar, concat('Rp ', format( pembayaran.tagihan, 0)) AS format_harga, date_format(pembayaran.tanggal_input,'%b %d, %Y') AS tanggal_input, usersinfo.id as usersinfo_id,usersinfo.username")
      ->FROM('pembayaran')
      ->join('usersinfo', 'usersinfo.username = pembayaran.id_user', 'left')
      ->ORDER_BY('pembayaran.tanggal_input', 'DESC');
    $query = $this->db2->get();
    if ($query->num_rows() > 0) {
      return $query->result_array();
    } else {
      return array();
    }
  }

or you can use this method.

  public function getRekap()
  {
    $rekaps = $this->Home_m->getRekap();
    $tests =  $this->Home_m->getTest();
    foreach ($rekaps as $rekap) {
      $newData['id'] = $rekap['id'];
      $newData['id_user'] = $rekap['id_user'];
      $newData['tagihan'] = $rekap['tagihan'];
      $newData['tanggal'] = $rekap['tanggal'];
      $newData['tanggal_input'] = $rekap['tanggal_input'];
      $newData['metode'] = $rekap['metode'];
      $newData['bulan'] = $rekap['bulan'];
      $newData['tanggal_bayar'] = $rekap['tanggal_bayar'];
      $newData['format_harga'] = $rekap['format_harga'];

      foreach ($tests as $test) {
        if ($rekap['id_user'] == $test['username']) {
          $newData['username'] = $test['username'];
          $newData['saldo'] = $test['saldo'];
          $newData['alamat'] = $test['alamat'];
        }
      }

      $data[] = $newData;
    }
    echo json_encode($data);
  }