How to select rows from a table using join query in codeigniter?

Question

I have two table competition_registration and competition_schedule. I have wa select query for selecting rows from competition_registration. this is my code as shown below:

$this->db->select('competition_registration.permenent_registration_number');
    $this->db->from('competition_registration');
    $where = "permenent_registration_number is  NOT NULL";
    $this->db->where($where);
    $this->db->join('competition_schedule', 'competition_schedule.competition_schedule_id = competition_registration.competition_schedule_id');
    $this->db->join('competition_schedule', 'competition_schedule.period_id = 6');
    $this->db->join('competition_schedule', 'competition_schedule.competition_level_id = 3');
    $query = $this->db->get();  echo $this->db->last_query();exit;

But this is showing an error. Can anyone check this query and rectify it for me ?

I want to select the columns from the both tables with same competition_schedule_id and competition_level_id equal to 3 and period_id equal to 6 from competition_schedule table .

Answer 1

Hope this will help you :

Put competition_schedule.period_id = 6 and this competition_schedule.competition_level_id = 3 in where clause like this :

$this->db->select('competition_registration.permenent_registration_number');
$this->db->from('competition_registration');
$this->db->join('competition_schedule', 'competition_schedule.competition_schedule_id = competition_registration.competition_schedule_id');
$where = "competition_registration.permenent_registration_number is  NOT NULL";

$this->db->where($where);
$this->db->where('competition_schedule.period_id' , '6');
$this->db->where('competition_schedule.competition_level_id','3');

//$this->db->join('competition_schedule', 'competition_schedule.period_id = 6');
//$this->db->join('competition_schedule', 'competition_schedule.competition_level_id = 3');
$query = $this->db->get();  
echo $this->db->last_query();
exit;

For more : https://www.codeigniter.com/user_guide/database/query_builder.html