Codeigniter - How to inner join?

Question

I want to do inner join between estate table and estatetype table. I make a edit page but I want to show customer information by cusId. I can draw data from estate table. But estateType in estate table. I hold estateType names in estatetype table. When I call estateType from estate table, just showing number(1,2,3) But I want to show their names from estatetype table. How I can do it?

I want to show customer estate information by Id from estatetype table. But I cannot do this.

Controller:

$viewData = new stdClass();

            $this->load->model('join_model');
            $viewData->estateList = $this->join_model->estatetypes();

            $viewData->customers = $this->db->where("cusId", $cusId)->get("customer")->row();
            $viewData->property = $this->db->where("cusId", $cusId)->get("estate")->row();
            $viewData->estype = $this->db->get("estatetype")->result();
            $viewData->heating = $this->db->get("heating")->result();
            $viewData->cities = $this->db->get("city")->result();
            $this->load->view('property_edit',$viewData);

Model:

<?php

class Join_model extends CI_Model {

    public function __construct()
    {
        parent::__construct();
    }

    public function estatetypes()
    {
        $this->db->select('estate.CusId,estate.estateType,estatetype.estateTypeId,estatetype.estateTypeAr,estatetype.estateTypeEng');
        $this->db->from('estate');
        $this->db->join('estatetype', 'estate.estateType = estatetype.estateTypeEng');

        $results = $this->db->get()->row();
        return $results;

        //return $this->db->get_where('users', array('userId' => $id), 1);
    }

}

I want to show when I add estateType from estate table in view, show estataTypeEn from estateType. For this, I did the inner join but when I add this to view and controller. There is nothing. How do I do this?

View:

<!-- Basic select -->
                                <div class="form-group">
                                    <label class="control-label col-lg-3">Estate Type <span class="text-danger">*</span></label>
                                    <div class="col-lg-9">
                                        <select name="estateType" class="form-control">
                                            <option value="<?php echo $property->estateType; ?>" readonly><?php echo $estateList->estateType; ?></option>
                                            <?php
                                            foreach($estype as $etype){ ?>
                                            <option value="<?php echo $etype->estateTypeId; ?>"><?php echo $etype->estateTypeEng; ?></option>
                                            <?php }?>
                                        </select>
                                    </div>
                                </div>

estatetype table (types names are here)

CREATE TABLE `estatetype` (
  `estateTypeId` int(11) NOT NULL AUTO_INCREMENT,
  `estateTypeNameEn` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `estateTypeNameAr` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`payTypeId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

estate table

 CREATE TABLE `estate` (
  `estateId` int(11) NOT NULL AUTO_INCREMENT,
  `CusId` int(11) DEFAULT NULL,
  `estateType` int(11) DEFAULT NULL COMMENT '',
  `estateCentare` varchar(6) COLLATE utf8_bin DEFAULT NULL,
  `estateRoom` varchar(2) COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `estateSalon` varchar(2) COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `estateBathroom` varchar(2) COLLATE utf8_bin DEFAULT NULL COMMENT '',
  `estateHeating` int(11) DEFAULT NULL COMMENT '',
  `estateCity` int(11) DEFAULT NULL COMMENT '',
  `estateAddress` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `estateCoord` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `estateGarden` tinyint(1) DEFAULT NULL COMMENT '',
  `estateBalcony` tinyint(1) DEFAULT NULL COMMENT '',
  `estatePackage` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `estatePackageDate` datetime DEFAULT NULL,
  `estatePackageUser` int(11) DEFAULT NULL,
  `estateCreateDate` datetime DEFAULT NULL,
  `estateCreateUser` int(11) DEFAULT NULL,
  `estateEditDate` datetime DEFAULT NULL,
  `estateEditUser` int(11) DEFAULT NULL,
  `estateDue` decimal(5,0) DEFAULT NULL,
  `estateImg` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`estateId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Answer 1

You don't need to use Join you can create a custom_helper.php

A CodeIgniter helper is a PHP file with multiple functions. It is not a class

Create a file and put the following code into it.

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

if ( ! function_exists('get_name'))
{
    function get_name($id,$value)
    {
        $CI=& get_instance();
        $number_data = $CI->db->get_where('estatetype', array('estateTypeId' => $id))->num_rows();
        if($number_data > 0){
            $data = $CI->db->get_where('estatetype', array('estateTypeId' => $id))->row()->$value;
        }
        else{
            $data = translate('Not_Found');
        }
        return $data;
    }   
}

Save this to application/helpers/ .

Using The Helper

---

Load this in your controller

$this->load->helper('custom_helper');

If you use this helper in a lot of locations you can have it load automatically by adding it to the autoload configuration file i.e. <your-web-app>\application\config\autoload.php.

$autoload['helper'] = array('custom_helper');

And in your view, you can call this function like this.

<select name="estateType" class="form-control">
<?php
$estype = $this->db->get("estate")->result_array();
foreach($estype as $row){ ?>
<option value="<?php echo $row['estateType']; ?>"><?php echo get_name($row['estateType'],'estateTypeNameEn'); ?></option>
<?php }?>
</select>

if you want to get other table or other columns from a table you can change the function like this.

function get_name($table,$field,$equal,$value)
{
    $CI=& get_instance();
    $number_data = $CI->db->get_where($table, array($field => $equal))->num_rows();
    if($number_data > 0){
        $data = $CI->db->get_where($table, array($field => $equal))->row()->$value;
    }
    else{
        $data = translate('Not_Found');
    }
    return $data;
}

and in view you can add this.

<select name="estateType" class="form-control">
<?php
$estype = $this->db->get("estate")->result_array();
foreach($estype as $row){ ?>
<option value="<?php echo $row['estateType']; ?>"><?php echo get_name('estatetype','estateTypeId',$row['estateType'],'estateTypeNameEn'); ?></option>
<?php }?>
</select>