• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Querying 2 tables in 1 functon

#1
[eluser]comyou[/eluser]
Hello all,
I don't know if this is possible because I've tried but keep getting errors.

Basically my view file needs some information:
my actual function needs more than these but this is just an example.
$username
$email
$role


My function needs to query a table first 'agents'
and pull the data from 'id', 'user_id' and 'role'.

I then need to query a second table 'users'
and pull the data 'email' and 'username' where 'id' is equal to 'user_id' from the first table.

I tried having 2 queries in the function but was getting errors.
Any ideas? Thanks.

CODE SNIPPETS
controller
Code:
function index() {
            $query_freeagent = $this->get('id')->result_array();
            foreach($query_freeagent as $row) :
                $user_id = $row->user_id;
                $data = array (
                    $query_user = $this->get_where($user_id),
                );
            endforeach;
            $data = array (
                'freeagent_records' => $query_freeagent,
                  'user_records' =>$query_user,
            );
           $this->load->view('freeagents/index', $data);
        }
    function get($order_by) {
        $table = "freeagents";
        $this->db->order_by($order_by, 'desc');
        $query=$this->db->get($table);
        return $query;
    }
    
    function get_where($id) {
        $table = "users";
        $this->db->where("id", $id);
        $query=$this->db->get($table);
        return $query;
    }

view file
Code:
<ul id="the-articles" class="-list">

    &lt;?php foreach($freeagent_records as $row):
                                    $user_id = $row['user_id'];
                                    $gt= $row['gamertag'];
                                    $console = $row['console'];
                                    $role = $row['role'];
                                    $game = $row['game'];
                                    $twitter = $row['twitter'];
                                foreach($user_records as $row_2):
                                    $user_id = $row_2->user_id;  
                                ?&gt;
                <li>
                    <h3>&lt;?php echo $gt; ?&gt; </h3>
                                            <span class="meta">
                                                ID: <b>&lt;?php echo $user_id; ?&gt;</b>
                                                Console: <b>&lt;?php echo $console; ?&gt;</b>
                                                Role: <b>&lt;?php echo $role; ?&gt;</b>
                                                Game: <b>&lt;?php echo $game; ?&gt; </b>
                                            </span>
                                            
     </li>
    &lt;?php endforeach;  
                                endforeach; ?&gt;
                
            </ul>

#2
[eluser]Tpojka[/eluser]
[quote author="comyou" date="1389996342"]
I tried having 2 queries in the function but was getting errors.
[/quote]
Just try to put yourself in our position: there is insufficient data provided.
Quote:Any ideas?
Post your code snippet in code tags and maybe someone notice what is wrong.
Quote: Thanks.
Np.

#3
[eluser]comyou[/eluser]
Apologies, I thought I had edited the original post with code snippets but apparently I didn't click post :p
Edited original with the code which is obviously completely wrong.

#4
[eluser]Tpojka[/eluser]
[off_topic]Why would you give some (semi-) reserved names to your functions and variables (ie get, get_where...)?[/off_topic]

Seems your get() function needs one parameter and you give two parameter to it.

#5
[eluser]comyou[/eluser]
[quote author="Tpojka" date="1390044158"][off_topic]Why would you give some (semi-) reserved names to your functions and variables (ie get, get_where...)?[/off_topic][/quote]

I downloaded some models for a HMVC structure and that's what they were called so I haven't changed them lol. No specific reason.

[quote author="Tpojka" date="1390044158"]Seems your get() function needs one parameter and you give two parameter to it.[/quote]

You are correct, I put it in the wrong place.

It was supposed to be in get()
Code:
function get($order_by) {
        $table = "freeagents";
        $this->db->order_by($order_by, 'desc');
        $query=$this->db->get($table);
        return $query;
    }

#6
[eluser]Tpojka[/eluser]
What says the error message you're getting?

#7
[eluser]CroNiX[/eluser]
You would use a join. I'll show the concept and you can go from there...You can fine tune with db::where(), and an additional join on your agent table, grab additional fields, etc

Users
-id
-role_id
-email
-name

Roles
-id
-name

Code:
//get all user names and email from user table, and their role_name from roles table
//since users and roles table both have a "name" column, alias the role.name as role_name to avoid collision
$users = $this->db
  ->select('users.email, users.name, roles.name as role_name')
  ->join('roles', 'roles.id = users.role_id', 'left') //get the role where the role.id = the users.role_id
  ->get('users')
  ->result();

#8
[eluser]comyou[/eluser]
[quote author="CroNiX" date="1390069688"]You would use a join. I'll show the concept and you can go from there...You can fine tune with db::where(), and an additional join on your agent table, grab additional fields, etc

Users
-id
-role_id
-email
-name

Roles
-id
-name

Code:
//get all user names and email from user table, and their role_name from roles table
//since users and roles table both have a "name" column, alias the role.name as role_name to avoid collision
$users = $this->db
  ->select('users.email, users.name, roles.name as role_name')
  ->join('roles', 'roles.id = users.role_id', 'left') //get the role where the role.id = the users.role_id
  ->get('users')
  ->result();
[/quote]

Thanks, never thought of using join.
Sorted it right out, had to use $this->db>query though because it wasn't working using the ->select->join->get, but that's no issue.
+1 internet points for you sir.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.