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.

Answers


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.


Need Your Help

Variable undefined

javascript

role="manager" and its says that "manager" is undefined.please help.

Select list styled with image overlay not working in Opera

html css image forms select

We're using images in CSS to restyle our select fields and they display as required in all main browser except in Opera, where the images don't display.