I have done up a query builder using join. I would like to show table 2, 3, 4, 5, 6 and so on based on the user id on table 1. I tried to query the result, it is showing like this :
My Tables
Table users user_id | username | email 1 | userA | userA@email.com 2 | userB | userB@gmail.com Table add_game game_id | user_id | ign | acc_id 1 | 1 | ignA | accA 2 | 1 | ignB | accB 1 | 2 | ignB | accB 3 | 2 | ignD | accD
I will be using foreach loop and I believe it will display out multiple times based on the records in the database. What should I do if I only want to display the information highlighted in the red box (which is from users table) just 1 time and all the records associated with user id in add_game table?
This is my current code :
Controller
public function login() { $data = []; helper(['form']); $validation = ConfigServices::validation(); $db = db_connect(); $model = new LoginModel($db); $user = $model->login($this->request->getVar('userlogin')); $this->setUserSession($user[0]); echo view('templates/header', $data, $user); echo view('account/login', $data, $user); echo view('templates/footer', $data, $user); } private function setUserSession($user){ $data = [ 'user_id' => $user['user_id'], 'username' => $user['username'], 'email' => $user['email'], 'firstname' => $user['firstname'], 'lastname' => $user['lastname'], 'dob' => $user['dob'], 'country' => $user['country'], 'country_code' => $user['c_code'], 'contact' => $user['contact'], 'game_id' => $user['game_id'], 'ign' => $user['ign'], 'acc_id' => $user['acc_id'], 'isLoggedIn' => true ]; session()->set($data); return true; }
Model:
return $this->db->table('users') ->groupStart() ->where('username', $str) ->orWhere('email', $str) ->groupEnd() ->join('add_game', 'add_game.user_id = users.user_id') ->get() ->getResultArray();
I have a few more tables but not yet created for now so I have only joined 1 table for the time being. What am I missing? Or do I have to loop twice? Is there a way that I just need to loop 1 time? Hope someone can help me out here. Thanks in advance guys!
Advertisement
Answer
the easiest way to achieve this (display 2 records from add_game table and 1 record from users table) you need to create a foreach loop in your view, and exclude duplicated data from users table to be shown.
controller:
$data['my_data']=$this->Your_model->your_method(); // your query example $this->load->view('your_view',$data)
view:
<?php $my_id=0;foreach($my_data as $row):?> <?php if($my_id!=$row->user_id):?> <div><?=$row->username?></div> <!--data from table user--> <div><?=$row->created_at?></div> <!--data from table add_game--> <?php else:?> <div><?=$row->created_at?></div> <!--only data from table add_game--> <?php endif;?> <?php $my_id=$row->user_id;endforeach;?>