How do you get data from multiple tables using Active Record Class in CodeIgniter

Question

I'm trying to learn how to extract data from multiple tables and put them into a variable and return them to my controller.

I can get information from one single table like this:

            $this->db->select('first_name, last_name');
            $this->db->from('people');
            $this->db->where('first_name', $firstname);
            $this->db->where('last_name', $lastname);
            $result = $this->db->get();

but I'm confused as to how I can get data from another table and match them with other variables holding more of the users input.

I've read the Active Record documentation on the CI website but the only thing I found that could be what I'm looking for is the join() query, but I don't how to use it, the example doesn't help on the website because I don't understand this:

$this->db->join('comments', 'comments.id = blogs.id');

Does 'comments' mean the table 'comments'? And if you were comparing a variable with comments.id, would you have to include the variable outside of the single quotes like this: 'comments.id =' $blogid ?

Or can I do this: query from one table and put those results in a variable as follows:

$result1 = $this->db->get();

and then again do another query from the other table and put that in

$result2

Then I can say: $query = $result1 + $result2;

Is that possible?

If someone could please confirm this information/help me out I would be greatful. Thanks

I suggest you read up on SQL Joins. Here's a great article to get you started: codinghorror.com/blog/2007/10/… — Ayush
– Ayush, Commented Dec 3, 2012 at 19:37
Thanks I read it, it seems join is not what I'm looking for as I don't need to compare values from two different tables, I just want to get different values from column names in different tables and put them into one variable i.e. $result — a7omiton
– a7omiton, Commented Dec 3, 2012 at 19:43
@AZ1 Can you give an example of what you're expecting when you say you want two columns combined to one? — peacemaker
– peacemaker, Commented Dec 3, 2012 at 19:44
@peacemaker I'd be expecting something like this: from a table called 'car' I can get its brand and from a table called 'house' I can get its cost. Both of these things would then be combined into a variable called $result from the query and then I could return them into my controller to display in my view. Hope it makes sense — a7omiton
– a7omiton, Commented Dec 3, 2012 at 19:50

Sachin Prasad · Accepted Answer · 2012-12-03 20:17:40Z

5

If you are having trouble using active record Join you can use simply MySQL query in this way:

$query_str = "SELECT user.*,role.*  FROM user ,role  where role.id = user.role_id and  user.username = ? AND user.password = ?";
$result = $this->db->query($query_str,array($username,$password));

Your code: $this->db->join('comments', 'comments.id = blogs.id'); says that take data from comment and blogs table where comments table ID is equal to blogs table id.

Also active record of my above query will be :

  $this->db->select('user.*,role.*')
  $this->db->from('user');
  $this->db->where('user.username', $username);
  $this->db->where('user.password', $password);
  $this->db->join('role','role.id = user.role_id')
  $result = $this->db->get();

You need to make sure that both the tables are related.

answered Dec 3, 2012 at 20:17

Sachin Prasad

5,37212 gold badges56 silver badges101 bronze badges

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

8 Comments

a7omiton Over a year ago

does $this->db->select('user.*,role.*') mean select all from the user and role table?

a7omiton Over a year ago

but what if I wanted to select different pieces of data that that were present in different tables? i.e. the first and last name were present in the user table, and the age and gender were present in another table. How would I be able to get the data that matches what the user inputs?

Sachin Prasad Over a year ago

@AZ1 Instead of User.*,role.* use user.firstname,user.lastname,role.age and it will fetch firstname,lastname and age from those two tables.

Sachin Prasad Over a year ago

yes first parameter is table name,whenever we use 'blog.id' it is referring to blog tables ID column.

a7omiton Over a year ago

Okay, so join is used to match up pairs of data between tables?

|

Vignesh · Accepted Answer · 2014-08-02 11:16:09Z

1

I have another way of achieving it,

$this->db->select('a.*,b.course_name');
$this->db->from('fees_master a,course_master b');
$this->db->where('a.course_id = b.course_id',NULL,FALSE);
$query = $this->db->get();
return $query->result_array();

I tried this and it worked, I hope this may help someone.

answered Aug 2, 2014 at 11:16

Vignesh

1,0632 gold badges17 silver badges35 bronze badges

Collectives™ on Stack Overflow

How do you get data from multiple tables using Active Record Class in CodeIgniter

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related