I've got 2 tables in database called users and announcements.
In the users table, I have a column called sub_teams where each user is assigned one or more "sub_teams" like construction,programming.
In the announcements table, I also have a column called sub_teams where each announcement is assigned one or more "sub_teams" like programming,team work.
So what I want is that if the user is assigned to the sub_team that exists in the announcements table, then they can view the announcement. For instance, a user may be assigned as programming in the sub_team column in the users table. Therefore, the user can view an announcement that also has the text programming in it's sub_teams column in the announcements table.
All the sub_teams values are separated by a comma and treated like an array when they are called from the database.
My code so far to do this:
PHP Code:
//check the sub_teams that the user is assigned
$uid = $this->session->userdata('uid');
$this->db->select('sub_teams');
$this->db->from('users');
$this->db->where('uid', $uid);
$sub_teams_query = $this->db->get();
$sub_teams_result = $sub_teams_query->row();
$sub_teams = explode(',', $sub_teams_result->sub_teams);
check announcements that are active
$this->db->limit($limit, $start);
$this->db->select('*');
$this->db->from('announcements');
$this->db->where('active', 1);
$this->db->where('date_created <', date('Y-m-d H:m:s')); //hide the announcements that are new and not published yet.
//use foreach to use $this->$db->like with every sub_team that the user is assigned to
foreach ($sub_teams as $sub_team) {
$this->db->like('sub_teams', $sub_team);
echo $sub_team;
}
//users.id and //announcements.uid is joined to see who created the announcement and to get the user details of the person who created the announcement
$this->db->join('users', 'users.uid = announcements.uid');
$this->db->order_by('date_created', 'desc', 'after');
$query = $this->db->get();
if ($query->num_rows() > 0) {
foreach ($query->result() as $row) {
$data[] = $row;
}
return $data;
}
return false;
This code works but only works in an order. For instance, if the user is assigned "Programming,Construction" then he will see all assigned that are shared with either Programming or Construction. This is good so far. The problem happens when the values of sub_teams in the user's table is not in order. It won't show me announcements that are either Programming or Construction if the order is "Construction,Programming".
Please help guys. Thanks.
