phpmyadmin Question posted in
The official documentation can be found here.

How do i use JOIN with a get_where clause in CodeIgniter – Phpmyadmin

I’m trying to get the data from two tables after an “Open” button is clicked from previous table UI by sending an Id.
e.g. 

<a name="Open" href="<?php echo base_url('welcome/WellProfile/'.$post->Id) ?>">

(https://imgur.com/6IU1Tfo)

So I have two tables – namely “WellDataA” and “WellDataB” , I want to get the data where WellDataB data matches with the WellDataA by WellName(column). 

Example: 

WellDataA:
Id   Platform  WellName
.
.
4      ZE          ZE-A
5      ZE          ZE-B
6      ZE          Ze-B
.
.

WellDataB:
Id     WellName     CompleteDate
1        ZE-A           12/3
2        ZE-B           14/5
3        ZE-C           20/6

This is how my query so far, but it ended up error

public function get_news_by_id($Id = 0)
{
        if ($Id === 0)
        {
            $query = $this->db->get('WellDataA');
            return $query->result_array();
        }
         $this->db->select('*'); 
         $this->db->from('WellDataA'); 
         $this->db->join('WellDataB','WellDataA.WellName = 
         WellDataB.WellName','INNER');  
        $query = $this->db->get_where('WellDataA', array('Id' => $Id));
        return $query->row_array();
}

I expect the output would show ZE, ZE-A, 12/3 when “Open” button is clicked on ZE-A. But the actual output is ZE, ZE-A only. Thank you so much in advance!:)

Tags:

3

Answers


  1. 0

    Firstful I am sorry if my solution won’t be a good one,
    but I suggest you change the structure of your tables to this:

    WellDataA:
Id   Platform  WellDataB
.
.
4      ZE          1
5      ZE          2
6      ZE          3
.
.

WellDataB:
Id     WellName     CompleteDate
1        ZE-A           12/3
2        ZE-B           14/5
3        ZE-C           20/6

    Since I assume that the used model is relational, one thing must first achieved is to make the tables all relational. Instead of connecting WellDataA and WellDataB with the Id from WellDataB inside WellDataA.

    Login or Signup to reply.
  2. 0

    Try this, your issue is in get_where() so, i removed it and rewrite it

    public function get_news_by_id($Id = 0)
{
    if ($Id === 0)
    {
        $query = $this->db->get('WellDataA');
        return $query->result_array();
    }
    $this->db->select('a.Id,a.Platform,a.WellName,b.Id,b.CompleteDate'); 
    $this->db->from('WellDataA a'); 
    $this->db->join('WellDataB b','a.WellName = b.WellName','INNER');  //changes
    $this->db->where('b.Id',$Id); //changes
    $query = $this->db->get();
    if ($query->num_rows() > 0) {
        return $query->row_array();
    }else{
        return array();
    }
}
    Login or Signup to reply.
  3. 0

    I think you just need to remove some code. Just try this code

    public function get_news_by_id($Id = 0)
{
        if ($Id === 0)
        {
            $query = $this->db->get('WellDataA');
            return $query->result_array();
        }
         $this->db->join('WellDataB','WellDataA.WellName = 
         WellDataB.WellName','INNER');  
        $query = $this->db->get_where('WellDataA', array('Id' => $Id));
        return $query->row_array();
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search