Connect SQL in CodeIgniter with Active Record

Question

I try to circle my head, but it seems that I see circles. I am trying to list user topics one by one, with quotes related to this particular topic below. If that makes sense.

I have 3 tables, for example:

[USERS] user_id username

[TOPICS] topic_id user_id topic_name

[QUOTES] quote_id topic_id quote_name

I want to be able to do something like this in my opinion:

Username: Thomas

Theme 1: Whatever

Quotations: one quote, another quote and a third quote, all of which relate to topic 1.

Theme 2: Another Thomas Theme

Citations: Yes, yes, thanks, I love Stack Overflow, These quotes belong to topic 2.

But I can’t make it work, I tried everything, including strange things like:

public function get_quotes() { $this->db->select('*'); $this->db->from('topics'); $this->db->join('quotes', 'topic_id = quote_id'); $query = $this->db->get(); if($query->num_rows() > 0) { foreach ($query->result() as $row) { $data[] = $row; } } return $data; } 

Is this weird, should I use "where" instead? Sort of:

 $this->db->where('user', $user_id); $this->db->where('topic', $topic_id); $this->db->where('quote', $quote_id); 

I really appreciate any help I can get, or just a finger pointing in the right direction!

Answer 1

From the beginning, I would ask, “What doesn’t work?” . Secondly, I suggest starting the profiler to show you EXACT SQL , so that you can make a reliable assessment of where the ACTIVE QUESTION fails.

To use the profiler, insert it into your controller:

 $this->output->enable_profiler(TRUE); 

This will lead to a good output of all database calls, all POST-vars, etc.

Link here: http://codeigniter.com/user_guide/libraries/output.html

UPDATE

To fully accomplish what you need, you need a query that returns the following columns:

 user_id, username, topic_id, topic_name, quote_id, quote_name 

Here is the active query that you want (you can also use a chain of methods if this is clear enough):

 $this->db->select('u.user_id, u.username, t.topic_id, t.topic_name, q.quote_id, q.quote_name'); $this->db->from('users u'); $this->db->join('topics t', 't.user_id = u.user_id'); // this joins the user table to topics $this->db->join('quotes q', 'q.topic_id = t.topic_id'); // this joins the quote table to the topics table $query = $this->db->get(); 

Then your result will be as follows:

 user_id | username | topic_id | topic_name | quote_id | quote_name 1 |Thomas |1 |Whatever |1 |One quote, anot... 2 |Ryan |4 |Another... |6 |To be or not to... 

Once you have this set of results, just skip the data for its output and check if you have several quotes from the same person (say, sort by user_id and run the test in the second cycle if its the same person otherwise print the name of the new user).

Answer 2

If you need all quotes for a specific user:

 $this->db->join('TOPICS t', 'u.user_id on t.user_id') ->join('QUOTES q', 't.topic_id on q.topic_id') ->where('u.user_id', $userId) ->get('USERS u'); // I always echo my queries when developing to make sure they are what i'm expecting echo $this->db->last_query(); 

If you want all quotes for all users

 $this->db->join('TOPICS t', 'u.user_id on t.user_id') ->join('QUOTES q', 't.topic_id on q.topic_id') ->get('USERS u'); echo $this->db->last_query();