0

I get stuck with my code. If you know about it, please respond to this question, cause I very need your help.

I have 2 tables (proposal and surveyor).

Proposal field :

proposal_id, proposal_name, surveyor_proposal

Example row : ('1', 'this is proposal name', '3,18,22')

As you can see, this is have 3 surveyor id separated by comma)

Surveyor field :

surveyor_id, surveyor_name

Example row :

  1. ('3', 'Randy')
  2. ('18', 'Bella')
  3. ('22', 'Zidan')

! here it is, I want to print out Randi, Bella, and Zidan in the view table

Model.php :

function get_all_datatables($x) {
    $this->datatables->select('proposal_name, surveyor_name');
    $this->datatables->from('proposal');
    $this->datatables->join('surveyor','surveyor_id = surveyor_proposal','left');
    return $this->datatables->generate();
}

Controller.php :

function get_data_json($x) { //get product data and encode to be JSON object
    header('Content-Type: application/json');
    echo $this->m_bl->get_all_datatables($x);
}

View.php :

   $("#table").dataTable({
      initComplete: function() {
          var api = this.api();
          $('#table_filter input')
              .off('.DT')
              .on('input.DT', function() {
                  api.search(this.value).draw();
          });
      },
          oLanguage: {
          sProcessing: "loading..."
      },
      processing: true,
      serverSide: true,
      ajax: {"url": "<?= base_url('dashboard/bl/get_data_json/'.$uri); ?>", "type": "POST"},
          {"data": "proposal_name"},
          {"data": "surveyor_name"},

      rowCallback: function(row, data, iDisplayIndex) {
          var info = this.fnPagingInfo();
          var page = info.iPage;
          var length = info.iLength;
          $('td:eq(0)', row).html();
      }
  });

Update!

I've been add 1 table junction, the fields is :

proposal_id_junction, surveyor_id_junction

Example Value :

  1. ('1','3') --> as Randy
  2. ('1','18') --> as Bella
  3. ('1','22') --> as Zidan

And i update my table join :

$this->datatables->join('junction','proposal_id_junction = proposal_id','left');
$this->datatables->join('surveyor','surveyor_id = surveyor_id_junction','left');

But, thats showing same proposal and different surveyor name, like this :

'this is proposal name','Randy'

'this is proposal name','Bella'

'this is proposal name','Zidan'

I want to thats view like this :

'this is proposal name','Randy, Bella, Zidan'

Please help.

Community
  • 1
  • 1
Yogi Irhandi Simamora
  • 13
  • 7
  • as a side note : you have a denormalized `surveyor_proposal` column, please read [this answer](https://stackoverflow.com/a/3653574/8566549) for more information about list of problems caused by denormalized column – Hasta Dhana Sep 24 '19 at 06:36
  • @HastaDhana thankyou for information. But now in my case, what will i do? adding more column? or any suggestion? – Yogi Irhandi Simamora Sep 24 '19 at 07:01
  • you could add a new [junction table](https://en.wikipedia.org/wiki/Associative_entity), for example `surveyor_proposal` table containing `proposal_id` and `surveyor_id` columns – Hasta Dhana Sep 24 '19 at 07:18
  • @HastaDhana great suggestion. But, i'm still don't solve this problem. Cause, in the view, that's showing multiple rows with different surveyor and same proposal name (,etc). Not 1 single row and have 2 data in single column :( – Yogi Irhandi Simamora Sep 24 '19 at 09:07
  • https://stackoverflow.com/questions/19101550/mysql-join-two-tables-with-comma-separated-values – Mohammed Shafeek Sep 25 '19 at 05:20

0 Answers0