Get records of table if user exists in another table
I am using this codeigniter active record query in my model to get the top 10 users in my user table with the most points.
$this->db->order_by('points', 'desc'); $q=$this->db->get('user', 10); $data = $q->result_array(); return $data;
This works fine and returns information on the top 10 users with the most points. points being a column in my user tabel.
However I have been trying to get the top 10 users who also exist in another table entries.
In the entries table, the user_id og the current user is stored when a user makes an entry.
I want to get the top to users who have enetered. So I want to alter the query above to return the 10 users with most points, who are in the entries table.
User Table: id, name, points Entries Table: id, time_entered, image, user_id
How do I do this?
I tried a join and it just returned the single user with the most points.
Hope your entries table have user_id as foreign key.
So your code should be:
$this->db->select("user.*,entries.*"); $this->db->from("user"); $this->db->join("entries","user.id = entries.user_id"); $this->db->order_by('user.points', 'desc'); $this->db->limit(10); $q = $this->db->get(); $data = $q->result_array(); return $data;
I'm not familiar with CI but I'll try to give a MySQL query instead,
SELECT * from user AS u WHERE u.id IN ( SELECT e.user_id from entries AS e ) ORDER BY points DESC LIMIT 10
Just convert it to CI AR Query. I think CI has some ways to set the condition of an AR query. If there is that option, you can just set the condition to IN to determine if the u.id exists in your entries table.