CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Carson
Carson

Reputation: 57

how to change the mysql query in codeigniter

How can I change this mysql query in codeigniter ?

This query is working:

/$query = $this->db->query("SELECT * FROM user_registration WHERE Ins_ID=(SELECT Ins_ID FROM login WHERE User_Name='$User_Name')");

I want to change it to the format as below the comment.thank you.

 public function view($User_Name)
     {
         if($User_Name != NULL)
         {
            //$query = $this->db->query("SELECT * FROM user_registration WHERE Ins_ID=(SELECT Ins_ID FROM login WHERE User_Name='$User_Name')"); //edited

             $Ins_ID=$this->db->query("SELECT Ins_ID FROM login WHERE User_Name='$User_Name'");
             $this -> db -> select('*');
             $this -> db -> from('user_registration');
             $this -> db -> where('Ins_ID',$Ins_ID);
             $query = $this -> db -> get();

             return $query->row();
         }

     }

Upvotes: 0

Views: 75

Answers (3)

xelber
xelber

Reputation: 4637

http://ellislab.com/codeigniter/user-guide/database/active_record.html Use Active records as follows

         $this->db->where('Ins_ID',$Ins_ID)
                ->get('user_registration');

Note that you can Chain the calls (where(), select(), join()) which looks nicer and saves few lines of codes. If you select all columns, no need to use $this->db->select('user_registration.*'); etc You can pass the table in ->get(), so again no need to use ->from(), if you don't have to.

Upvotes: 0

YouSer
YouSer

Reputation: 393

try:

$this->db->where("Ins_ID=(SELECT Ins_ID FROM login WHERE User_Name='$User_Name')");
$this->db->get('user_registration');

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You need to join your tables don't use subquery

$this->db->select('r.*');
$this->db->from('user_registration r');
$this->db->join('login l','r.Ins_ID=l.Ins_ID')
$this->db-> where('l.User_Name',$User_Name);
$query =$this->db->get();

Active Record

Upvotes: 2

Related Questions