CODEWITHSUNDEEP
phpmysqldatabasecodeigniter
Firman
Firman

Reputation: 473

Codeigniter - How to passing multiple data from multiple table to a single View?

I've table in database.

tbl_parameter column:

parameter_ID | parameter_name | parameter_unit | parameter_method
------------ | -------------- | -------------- | ----------------
3001         | Salt           | %              | Titrimetri
3002         | pH             | -              | pH meter
3003         | Visco          | poise          | Viscometer

Next is tbl_coa to store the COA page input. parameter_ID come from multi select input, so it stored comma separated.

coa_ID | coa_number  | coa_year | product_ID | customer_ID | parameter_ID
------ | ----------- | -------- | ---------- | ----------- | ------------
4001   | 001/01/2017 | 2017     | 10001      | 2111        | 3001,3002,3003

Next I'm working the View page that display COA details, the view saved as view_coa.php. The code as below. I'm defining {PRE} to replace tbl_.

Model Coa_model.php:

function get_coa_detail($coa_ID){
    $this->db->select('*');
    $this->db->from('{PRE}coa');
    $this->db->join('{PRE}product', '{PRE}product.product_ID  = {PRE}coa.product_ID', 'LEFT');
    $this->db->join('{PRE}customer', '{PRE}customer.customer_ID  = {PRE}coa.customer_ID', 'LEFT');
    $this->db->join('{PRE}parameter', '{PRE}parameter.parameter_ID  = {PRE}coa.parameter_ID', 'LEFT');
    $this->db->where('{PRE}coa.coa_ID', $coa_ID);
    $query = $this->db->get();

    if($query->num_rows() > 0){
        $data = $query->row();
        $query->free_result();
    }
    else{
        $data = NULL;
    }

    return $data;
}

Controller Coa.php:

public function view_coa($coa_ID=0){
    if($coa_ID != 0 && !empty($coa_ID)){
        $data = array(
                'record' => $this->Coa_model->get_coa_detail($coa_ID)
            );
        //print_r($data);
        $this->site->view('view_coa', $data);
    }
    else{
        redirect(set_url('coa/data_table/'.date('Y')));
    }
}

The problem is, when I want to show the parameter details (that stored as comma separated parameter_ID) it just shown the datas from first parameter_ID only. In the example above, it just shown the detail for parameter_ID = 3001. I want to display the datas (parameter_name, parameter_unit, parameter_method) from all parameter_ID (3001,3002, and 3003).

The print_r data result:

Array ( [record] => stdClass Object ( [coa_ID] => 4001 [coa_number] => 001/01/2017 [coa_year] => 2017 [product_ID] => 10001 [product_type] => Sweet Soy [product_name] => Product A [Salt] => 5.5 [pH] => 4.4 [Visco] => 12.5 [customer_ID] => 2111 [customer_name] => Company 1 [parameter_ID] => 3001 [parameter_name] => Salt [parameter_unit] => % [parameter_method] => Titrimetri ) )

My question is, how to display all parameters details for [parameter_ID] => 3002 and [parameter_ID] => 3003?

Current view view_coa to display data from database:

  <!-- COA title and number row -->
  <div class="row">
    <div class="col-xs-12 coa-title-number">
      <h3>Certificate of Analysis</h3>
      <p>NO. <?php echo $record->no_coa;?></p>
    </div>
  </div>

  <!-- COA detail row -->
  <div class="row">
    <div class="col-xs-1">
    </div>
    <div class="col-xs-11">
      <table class="table-coa-detail">
        <tr class="text-nowrap">
          <th>Product Name</th>
          <td><strong>: <?php echo $record->product_name;?></strong></td>
        </tr>
        <tr class="text-nowrap">
          <th>Customer Name</th>
          <td><strong>: <?php 
            if($record->customer_ID == "0" || $record->customer_ID == NULL){
              echo "-";
            }
            else{
              echo $record->customer_name;
            }
          ?></strong></td>
        </tr>
        <tr class="text-nowrap">
          <th>Analysis Results</th>
          <td>:</td>
        </tr>
      </table>
    </div>
  </div>

  <!-- COA results row -->
  <div style="padding-right: 50px;" class="row">
    <div class="col-xs-1">                      
    </div>
    <div class="col-xs-11">
      <table class="table-results">
        <thead>
          <tr>
          <th>No</th>
          <th>Parameter</th>
          <th>Unit</th>
          <th>Results</th>
          <th>Method</th>
          <th>Conclusion</th>
          </tr>
        </thead>
        <tbody class="text-nowrap">
          <tr>
          <td style="text-align: center;">1</td>
          <td><strong>Analytical</strong>
            <?php 
            if($record->product_type == "Sweet Soy"){
              echo "<p>pH</p>"; // it should "<p>".$record->parameter_name."</p>"; from [parameter_ID] => 3002
              echo "<p>Visco</p>"; // it should "<p>".$record->parameter_name."</p>"; from [parameter_ID] => 3003
              echo "<p>Salt</p>"; // it should "<p>".$record->parameter_name."</p>"; from [parameter_ID] => 3001
            }
            else{
              echo "<p>pH</p>"; // it should "<p>".$record->parameter_name."</p>"; from [parameter_ID] => 3002
              echo "<p>Salt</p>"; // it should "<p>".$record->parameter_name."</p>"; from [parameter_ID] => 3001
            }
            ?>
          </td>
          <td style="text-align: center;">&nbsp;
            <?php 
            if($record->product_type == "Sweet Soy"){
              echo "<p>-</p>"; // it should "<p>".$record->parameter_unit."</p>"; from [parameter_ID] => 3002
              echo "<p>poise</p>"; // it should "<p>".$record->parameter_unit."</p>"; from [parameter_ID] => 3003
              echo "<p>%</p>"; // it should "<p>".$record->parameter_unit."</p>"; from [parameter_ID] => 3001
            }
            else{
              echo "<p>-</p>"; // it should "<p>".$record->parameter_unit."</p>"; from [parameter_ID] => 3002
              echo "<p>%</p>"; // it should "<p>".$record->parameter_unit."</p>"; from [parameter_ID] => 3001
            }
            ?>
          </td>
          <td style="text-align: center;">&nbsp;
            <?php 
            if($record->product_type == "Sweet Soy"){
              echo "<p>".$record->pH."</p>";
              echo "<p>".$record->Visco."</p>";
              echo "<p>".$record->Salt."</p>";
            }
            else{
              echo "<p>".$record->pH."</p>";
              echo "<p>".$record->Salt."</p>";
            }
            ?>
          </td>
          <td style="text-align: center;">&nbsp;
            <?php 
            if($record->product_type == "Sweet Soy"){
              echo "<p>pH meter</p>"; // it should "<p>".$record->parameter_method."</p>"; from [parameter_ID] => 3002
              echo "<p>Viscometer</p>"; // it should "<p>".$record->parameter_method."</p>"; from [parameter_ID] => 3003
              echo "<p>Titrimetri</p>"; // it should "<p>".$record->parameter_method."</p>"; from [parameter_ID] => 3001
            }
            else{
              echo "<p>pH meter</p>"; // it should "<p>".$record->parameter_method."</p>"; from [parameter_ID] => 3002
              echo "<p>Titrimetri</p>"; // it should "<p>".$record->parameter_method."</p>"; from [parameter_ID] => 3001
            }
            ?>
          </td>
          <td style="text-align: center;">&nbsp;
            <p>OK</p>
            <p>OK</p>
            <p>OK</p>
          </td>
          </tr>
        </tbody>
      </table>

    </div>
  </div>

Upvotes: 1

Views: 4125

Answers (1)

Ferenc Kurucz
Ferenc Kurucz

Reputation: 142

In the function get_coa_detail, you only fetch the 1st row from the result set.

 if($query->num_rows() > 0){
        $data = $query->row();
        $query->free_result();
    }
    else{
        $data = NULL;
    }

If you want to return all lines use the below and in your view iterate over it

if($query->num_rows() > 0){
        $data = $query->result();
        $query->free_result();
    }
    else{
        $data = NULL;
    }

For the fixing your view you can do the following:

In your controller, change the variable name that contains the data (this is so you can use the view code with minimal modifications).

So i change this in Coa.php

 $data = array(
                'coa' => $this->Coa_model->get_coa_detail($coa_ID)
            );

and in the view file do the following modification:

<?php foreach ($coa as $record): ?>
    <!-- COA title and number row -->
    <div class="row">

and close it with:

<?php endforeach;?>

EDIT: Here is the sample code i've used:

Controller: Coa.php

<?php defined('BASEPATH') OR exit('No direct script access allowed');

class Coa extends CI_Controller {

    public function index() {

        $this->view_coa(4001);
    }

    public function view_coa($coa_ID = 0) {

        $this->load->model('coa_model');

        if ($coa_ID != 0 && !empty($coa_ID))
        {
            $data['record'] = $this->coa_model->get_coa_detail($coa_ID);
            $data['parameters'] = $this->coa_model->get_parameter($data['record']->parameter_ID);

            $this->load->view('view_coa', $data);
        } else
        {
            redirect(set_url('coa/data_table/' . date('Y')));
        }
    }

}

This is the model Coa_model.php

<?php if (!defined('BASEPATH'))    exit('No direct script access allowed');

class Coa_model extends CI_Model {

    public function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function get_coa_detail($coa_ID) {

        $pre = 'tbl_';

        $this->db->select('*');
        $this->db->from($pre . 'coa');
        $this->db->join($pre . 'product', $pre . 'product.product_ID  = tbl_coa.product_ID', 'LEFT');
        $this->db->join($pre . 'customer', $pre . 'customer.customer_ID  = tbl_coa.customer_ID', 'LEFT');
        //  $this->db->join($pre . 'parameter', $pre . 'parameter.parameter_ID  = tbl_coa.parameter_ID', 'LEFT');
        $this->db->where($pre . 'coa.coa_ID', $coa_ID);
        $query = $this->db->get();

        if ($query->num_rows() > 0)
        {
            $data = $query->row();
            $query->free_result();
        } else
        {
            $data = NULL;
        }

        return $data;
    }

    /**
     *   This get's all the parameters
     * 
     * @param string $parameter_ID
     * @return object
     */
    public function get_parameter($parameter_ID = NULL) {

        // Delimiter
        $delimiter = ',';

        // Check the position of the first delimiter,
        // if there are no delimiters this will return false
        $multiple = strpos($parameter_ID, $delimiter);

        // Table prefix
        $pre = 'tbl_';

        $this->db->select('*');

        if ($multiple !== FALSE)
        {
            // We have multiple parameters delimited by $delimiter
            $params = explode($delimiter, $parameter_ID);

            $this->db->where_in('parameter_ID', $params);
        } else
        {
            // We have a single parameter
            $this->db->where('parameter_ID', $parameter_ID);
        }

        $this->db->from($pre . 'parameter');
        $query = $this->db->get();

        if ($query->num_rows() > 0)
        {
            $data = $query->result();
            $query->free_result();
        } else
        {
            $data = NULL;
        }

        return $data;
    }

}

This is the view file: view_coa.php

<!-- COA title and number row -->
<div class="row">
    <div class="col-xs-12 coa-title-number">
        <h3>Certificate of Analysis</h3>
        <p>NO. <?php echo $record->coa_ID; ?></p>
    </div>
</div>

<!-- COA detail row -->
<div class="row">
    <div class="col-xs-1">
    </div>
    <div class="col-xs-11">
        <table class="table-coa-detail">
            <tr class="text-nowrap">
                <th>Product Name</th>
                <td><strong>: <?php echo $record->product_name; ?></strong></td>
            </tr>
            <tr class="text-nowrap">
                <th>Customer Name</th>
                <td><strong>: <?php
                        if ($record->customer_ID == "0" || $record->customer_ID == NULL)
                        {
                            echo "-";
                        } else
                        {
                            echo $record->customer_name;
                        }
                        ?></strong></td>
            </tr>
            <tr class="text-nowrap">
                <th>Analysis Results</th>
                <td>:</td>
            </tr>
        </table>
    </div>
</div>

<!-- COA results row -->
<div style="padding-right: 50px;" class="row">
    <div class="col-xs-1">
    </div>
    <div class="col-xs-11">
        <table class="table-results">
            <thead>
                <tr>
                    <th>No</th>
                    <th>Parameter</th>
                    <th>Unit</th>
                    <th>Results</th>
                    <th>Method</th>
                    <th>Conclusion</th>
                </tr>
            </thead>
            <tbody class="text-nowrap">
                <?php $c = 1; // counter?>
                <?php foreach ($parameters as $params): ?>
                    <tr>
                        <td><?= $c; ?></td>
                        <td><?= $params->parameter_name; ?></td>
                        <td><?= $params->parameter_unit; ?></td>
                        <td>result would come here</td>
                        <td><?= $params->parameter_method; ?></td>
                        <td>OK</td>
                    </tr>
                    <?php $c ++; // increase row counter ?>
                <?php endforeach; ?>
            </tbody>
        </table>

    </div>
</div>

This is the result i see on my screen: enter image description here

Upvotes: 2

Related Questions