I'm new to codeigniter i have Department,student, and user table in my database. in student table Dep_ID references departments where the student belongs and in user table Dep_ID, Stud_ID are used as foreign key here is my table structure:
CREATE TABLE `tbl_users` (
`userId` int(11) NOT NULL,
`email` varchar(128) NOT NULL,
`password` varchar(128) NOT NULL,
`username` varchar(128) DEFAULT NULL,
`mobile` varchar(20) DEFAULT NULL,
`roleId` tinyint(4)DEFAULT NULL,
`Dep_ID` tinyint(4)DEFAULT NULL,
`ID_No` tinyint(4) DEFAULT NULL,
`avatar` varchar(255) DEFAULT 'default.jpg',
`isDeleted` tinyint(4) NOT NULL DEFAULT '0',
`createdBy` int(11) NOT NULL,
`createdDtm` datetime NOT NULL,
`updatedBy` int(11) DEFAULT NULL,
`updatedDtm` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;*
CREATE TABLE `student` (
`ID` int(11) NOT NULL,
`Full_Name` varchar(50) NOT NULL,
`Gender` varchar(50) NOT NULL,
`ID_No` varchar(50) NOT NULL,
`Dep_ID` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;*
CREATE TABLE `department` (
`Dep_ID` int(11) NOT NULL,
`DepName` varchar(50) NOT NULL,
`Head` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;*
Each department has an account so that they can logged in and perform their task. What i want is loading those students who belong to logged in department. for example when computer science department is logged in those students who belongs to computer science department will load for that department user.here is the code that i have tried.
in my model:
function Populate_data()
{
$this->db->select('BaseTbl.Dep_ID, Students.ID,Students.Full_Name,Students.ID_No,Students.Dep_ID,Students.Gender');
$this->db->from('department as BaseTbl');
$this->db->join('student as Students', 'Students.Dep_ID = BaseTbl.Dep_ID');
$query = $this->db->get();
$result = $query->result();
return $result;
}
***
In my controller
function Populate_Data()
{
if($this->isDepartment() == TRUE)
{
$this->loadThis();
}
else
{
$searchText = $this->security->xss_clean($this->input->post('searchText'));
$data['searchText'] = $searchText;
$this->load->library('pagination');
$count = $this->department_model->userListingCount($searchText);
$returns = $this->paginationCompress ( "userListing/", $count, 10 );
$data['userRecords'] = $this->department_model->Populate_data($searchText, $returns["page"], $returns["segment"]);
$this->global['pageTitle'] = 'HU : User Listing';
$this->loadViews("department/Students", $this->global, $data, NULL);
}
}
in my view
<div class="page-content-wrapper">
<!-- BEGIN CONTENT BODY -->
<div class="page-content">
<div class="page-bar">
<ul class="page-breadcrumb">
<li>
<a href="index.html">Dashboard</a>
<i class="fa fa-circle"></i>
</li>
<li>
<a href="#">My students lists</a>
<i class="fa fa-circle"></i>
</li>
</ul>
</div>
<div class="row">
<div class="col-md-12">
<!-- BEGIN EXAMPLE TABLE PORTLET-->
<div class="portlet light bordered">
<div class="portlet-title">
<div class="tools"> </div>
</div>
<div class="portlet-body">
<table class="table table-striped table-bordered table-hover" id="sample_1">
<thead>
<tr>
<th>ID </th>
<th>Full Name </th>
<th>Gender </th>
<th>ID</th>
<th class="text-center">Actions</th>
</tr>
</thead>
<?php
if(!empty($userRecords))
{
foreach($userRecords as $record)
{
?>
<tr>
<td><?php echo $record->ID ?></td>
<td><?php echo $record->Full_Name ?></td>
<td><?php echo $record->Gender ?></td>
<td><?php echo $record->ID_No ?></td>
<td class="text-center">
<a class="btn btn-sm btn-primary" href="<?= base_url().'login-history/'.$record->ID; ?>" title="Login history"><i class="fa fa-history"></i></a> |
<a class="btn btn-sm btn-info" href="<?php echo base_url().'editOld/'.$record->ID; ?>" title="Edit"><i class="fa fa-pencil"></i></a>
<a class="btn btn-sm btn-danger deleteUser" href="#" data-userid="<?php echo $record->ID; ?>" title="Delete"><i class="fa fa-trash"></i></a>
</td>
</tr>
<?php
}
}
?>
</table>
</div>
</div>
The problem is i get all departments students. Thank you for your help