codeigniter join query issue in email

Question

My Codeigniter join query is not work. In a function just try to match RC code in users table and get email, this function is properly work, next target is match email id from article table and get article its work properly, but ill try to join the users table and article table beacause i need to get the users firstname & lastname from users table, I don't know is that right way or not, check my code below. Controller Part :

  public function user_article()
{   

    $rc=$_GET['rc'];
    $data['title'] = "User Article";        
    if ($this->session->userdata ('is_logged_in')){
        $data['profile']=$this->model_users->profilefetch();
        $data['results']=$this->article_m->u_article($_GET);
        $this->load->view('sd/header',$data);
        $this->load->view('sd/user_article', $data);
        $this->load->view('sd/footer', $data);
    } 
    else {

    }
}

My Model :

  function u_article($rc)
    {  
        $query=$this->db->select('email')->where('rc',$rc['rc'])->get('users');

        $result=$query->result_array();
        if ($query->num_rows() > 0) {
            $row = $query->row_array(); 
            $array = array ('email' => $row['email'], 'a.status' => '1');   
            $query1=$this->db->select('a.id,title,a.status,description,image,a.email,tags,postdate,firstname,lastname,rc')->join('users u','u.email = a.email','left')->where($array)->get('articles a');

            if ($query1->num_rows() > 0) {
                foreach ($query1->result() as $row) {
                    $data[] = $row;
                }
                return $data;
            }
            else {return NULL;}
        } else {return NULL;}

    }

check my code and tell me my mistakes Thanks in advance.

Error Shoe in View :

  A Database Error Occurred

  Error Number: 1052

  Column 'email' in where clause is ambiguous

  SELECT `a`.`id`, `title`, `a`.`status`, `description`, `image`, `a`.`email`, `tags`, `postdate`, `firstname`, `lastname`, `rc` FROM (`articles` a) LEFT JOIN `users` u ON `u`.`email` = `a`.`email` WHERE `email` = '[email protected]' AND `a`.`status` = '1'

  Filename: F:\wamp\www\project\system\database\DB_driver.php

  Line Number: 331

Parag Tyagi · Accepted Answer · 2015-07-11 12:18:59Z

2

Refactored your code. Just copy-paste and check its working.

Controller:

public function user_article()
{   

    $rc = $this->input->get('rc');
    $data['title'] = "User Article";        
    if ($this->session->userdata ('is_logged_in'))
    {
        $data['profile']=$this->model_users->profilefetch();
        $data['results']=$this->article_m->u_article($rc);
        $this->load->view('sd/header',$data);
        $this->load->view('sd/user_article', $data);
        $this->load->view('sd/footer', $data);
    } 
    else 
    {

    }
}

Model:

function u_article($rc)
{  
    $query = $this->db->select('email')->where('rc',$rc)->get('users');
    $result = $query->result_array();

    if ($query->num_rows() > 0) 
    {
        $row = $query->row_array(); 
        $array = array ('u.email' => $row['email'], 'a.status' => '1'); 
        $query1 = $this->db->select('a.id,a.title,a.status,a.description,a.image,a.email,a.tags,a.postdate,u.firstname,u.lastname,u.rc')->join('users u','u.email = a.email','left')->where($array)->get('articles a');

        if ($query1->num_rows() > 0) 
        {
            foreach ($query1->result() as $row) 
            {
                $data[] = $row;
            }
            return $data;
        }
        else {return NULL;}
    } else {return NULL;}
}

edited Jul 11, 2015 at 12:18

answered Jul 11, 2015 at 11:26

Parag Tyagi

9,0003 gold badges45 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Vijay Arora Over a year ago

Hello, This code is not work , $_GET is properly work , i think this code create a problem ill add error message in my question u can chack ... $array = array ('email' => $row['email'], 'a.status' => '1'); $query1 = $this->db->select('a.id,title,a.status,description,image,a.email,tags,postdate,firstname,lastname,rc')->join('users u','u.email = a.email','left')->where($array)->get('articles a');

Vijay Arora Over a year ago

This error show in view : A Database Error Occurred Error Number: 1054 Unknown column '0' in 'where clause' SELECT a.id, title, a.status, description, image, a.email, tags, postdate, firstname, lastname, rc FROM (articles a) LEFT JOIN users u ON u.email = a.email WHERE 0 = 'email' AND 1 = '[email protected]' AND a.status = '1' Filename: F:\wamp\www\project\system\database\DB_driver.php Line Number: 331

Parag Tyagi Over a year ago

Basically $_GET['rc'] and $this->input->get('rc') are same. Its just the CI way of doing thing. ('email', $row['email'], 'a.status' => '1') there is a comma and it should =>

Vijay Arora Over a year ago

Error Change : Column 'email' in where clause is ambiguous

Parag Tyagi Over a year ago

You have to add alias to all the columns. Like u.email, a.title

|

Collectives™ on Stack Overflow

codeigniter join query issue in email

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related