CODEWITHSUNDEEP
phpmysqlcodeigniter
Carson
Carson

Reputation: 57

Nested query in codeigniter

there are somethings wrong at

$this->db->where("Mark_ID,(SELECT Mark_ID FROM mark WHERE Course_ID=$Course_ID && Matric_No=$Matric_No)");

in my model?any suggestion idea?thank you.

 function update_record($Course_ID,$Matric_No)
     {  
         if($Course_ID && $Matric_No != NULL)
         {
         $data = array(
             //'Course_ID' => $this->input->post('Course_ID'),
             'Matric_No' => $this->input->post('Matric_No'),
             'Student_Name' => $this->input->post('Student_Name'),
             'Result_Mark_1' => $this->input->post('Result_Mark_1'),
             'Result_Mark_2' => $this->input->post('Result_Mark_2'),
             'Result_Mark_3' => $this->input->post('Result_Mark_3'),
             'Result_Mark_4' => $this->input->post('Result_Mark_4'),
             'Result_Mark_5' => $this->input->post('Result_Mark_5')

                 );

           $this->db->where("Mark_ID,(SELECT Mark_ID FROM mark WHERE Course_ID=$Course_ID && Matric_No=$Matric_No)");

           $this->db->update('mark', $data);
         }
     }

Upvotes: 1

Views: 579

Answers (3)

waqas
waqas

Reputation: 4505

I suggest you to make your life easier by forgetting about nested queries. If you still want to use nested query, answer posted above is correct. You need to use IN

function update_record($Course_ID,$Matric_No)
     {  
         if($Course_ID && $Matric_No != NULL)
         {
         $data = array(
             //'Course_ID' => $this->input->post('Course_ID'),
             'Matric_No' => $this->input->post('Matric_No'),
             'Student_Name' => $this->input->post('Student_Name'),
             'Result_Mark_1' => $this->input->post('Result_Mark_1'),
             'Result_Mark_2' => $this->input->post('Result_Mark_2'),
             'Result_Mark_3' => $this->input->post('Result_Mark_3'),
             'Result_Mark_4' => $this->input->post('Result_Mark_4'),
             'Result_Mark_5' => $this->input->post('Result_Mark_5')

                 );

            // pre update query
            $this->db->select('Mark_id');
            $this->db->where('Course_ID', $Course_ID);
            $this->db->where('Matric_No', $Matric_No);
            $tmp_result = $this->db->get();
            $result = $tmp_result->row();
            $mark_id = $result->Mark_id;

            //updation
           $this->db->where("Mark_ID",$mark_id);
           $this->db->update('mark', $data);
         }
     }

Upvotes: 0

Jinesh Gandhi
Jinesh Gandhi

Reputation: 77

I think you can use below code for get your result.

$this->db->select("Mark_id");

$this->db->where("Course_ID",$Course_ID);

$get_id = $this->db->get($this->tbl_user)->row();

after getting mark id simply pass it to below query.

$this->db->where('Mark_id', $get_id->Mark_id);

$this->db->update($this->tbl_user,$data);

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You need to use IN() clause 

$subquery="SELECT Mark_ID FROM mark WHERE Course_ID=$Course_ID && Matric_No=$Matric_No";
$this->db->where("Mark_ID IN($subquery)",null,FALSE);

But you are using subquery from same table to update you will face the error of

you can't specify target table in update query

For this you need to give new alias to your subquery like 

$subquery="SELECT t.Mark_ID FROM(
           SELECT Mark_ID 
           FROM mark  
           WHERE Course_ID=$Course_ID && Matric_No=$Matric_No
           ) t ";

$this->db->where("Mark_ID IN($subquery)",null,FALSE);

Upvotes: 2

Related Questions