Welcome Guest, Not a member yet? Register   Sign In
Check if a value from array exists in database not working
#1

(This post was last modified: 10-31-2015, 09:10 AM by Taylor.)

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.
Reply
#2

(10-28-2015, 11:08 AM)Taylor Wrote: 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.

order_by only takes two parameters not three.
PHP Code:
$this->db->order_by('title''DESC');

$this->db->order_by('title DESC, name ASC'); 
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#3

(10-28-2015, 11:08 AM)Taylor Wrote:
PHP Code:
   //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;
   

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.

When you build the LIKE as above it will be joined together with AND, for example:

Code:
SELECT * FROM `announcements`
WHERE `active` = 1
 AND `date_created` < '2015-10-28 20:10:28'
 AND `sub_teams` LIKE '%Construction%'
 AND `sub_teams` LIKE '%Programming%'
JOIN ...;

So only when the announcements table has rows with sub_teams containing both Construction and Programming will be returned.
You could give following example a try:
   
PHP Code:
    if (!empty($sub_teams)) {
            $this->db->group_start();
            for ($i 0$i count($sub_teams); ++$i) {
                if ($i 1) {
                    $this->db->like('sub_teams'$sub_teams[$i]);
                } else {
                    $this->db->or_like('sub_teams'$sub_teams[$i]);
                }
            }
            $this->db->group_end();
        

Hope I can help you.
Reply
#4

(10-28-2015, 01:31 PM)pdthinh Wrote:
(10-28-2015, 11:08 AM)Taylor Wrote:
PHP Code:
   //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;
   

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.

When you build the LIKE as above it will be joined together with AND, for example:


Code:
SELECT * FROM `announcements`
WHERE `active` = 1
 AND `date_created` < '2015-10-28 20:10:28'
 AND `sub_teams` LIKE '%Construction%'
 AND `sub_teams` LIKE '%Programming%'
JOIN ...;

So only when the announcements table has rows with sub_teams containing both Construction and Programming will be returned.
You could give following example a try:
   

PHP Code:
    if (!empty($sub_teams)) {
            $this->db->group_start();
            for ($i 0$i count($sub_teams); ++$i) {
                if ($i 1) {
                    $this->db->like('sub_teams'$sub_teams[$i]);
                } else {
                    $this->db->or_like('sub_teams'$sub_teams[$i]);
                }
            }
            $this->db->group_end();
        

Hope I can help you.

OMG Thank you so much it works. I really appreciate it!
Reply




Theme © iAndrew 2016 - Forum software by © MyBB