Welcome Guest, Not a member yet? Register   Sign In
Array in an array, I think
#1

[eluser]mdcode[/eluser]
To start off with the situation, I have a dropdown select menu, which when the selection changes, it grabs a list of names for whoever is in that group (the dropdown lists the group names). This is all very well and I have this working.

However the problem comes when someone belongs to multiple groups.

In the 'person' table I have a comma delimited numerical list of group numbers (pertaining to the id in the groups table, such as 1,2,4,5,6,12.

When I use the select 'Group' dropdown to select on of these listed groups, the only names that appear on the list are those where they are only a member of one group, so in other words....

I have 3 people, Jack, Mary and Clive.
Jack is a member of group 1.
Mary is in group 2.
Clive is a member of both groups, 1 and 2 (using the method above).
I select group 1 from the list, but only Jack's name appears when Clive should appear as well.

Here's my code:

CONTROLLER:
Code:
function div_names_list()
    {
       /* set the POST variable */
        $division = $this->input->post('division', TRUE);
        
        /* grab the division details */
        $names = $this->complaint->get_division($division);
        
        /* check the existence of a names list and print */
        if ($names !== FALSE)
        {
            foreach ($names as $key=>$value)
            echo $value['first_name'] . ' ' . $value['last_name'] . ', ';
        }
    }

MODEL:
Code:
function get_division($division = '')
        {
            $this->db->select('first_name, last_name');
            $this->db->from('users');
            $this->db->where('division',$division);
            
            $query = $this->db->get();
            
            return ($query->num_rows) ? $query->result_array() : FALSE;
        }

I'd appreciate anyone shedding some light on this please.
#2

[eluser]verynewtothis[/eluser]
I see normalization at the back end resolving this..

DIVISION - TABLE
DID,DNAME

USERS - TABLE
UID,FNAME,LNAME

USER_TO_DIVISION -TABLE (Basically holds user to division relationship 1-many)
UID,DID
#3

[eluser]mdcode[/eluser]
Thanks for your reply on this, but unfortunately it's something that is not acceptable in this situation. Part of the system is locked down by security whereby if a record is inserted into the database for the person's group, only people in that group are then able to edit or close the record.

This happens by this code (which is immaterial, just fyi):
Code:
$user_state = $this->session->userdata('home_state');
$user_division = $this->session->userdata('home_division');
$div_list = explode(',', $user_division);
$uid = $this->session->userdata('user_id');

if ( ( (in_array($complnt->div_id, $div_list) ) OR ( $user_division == 0 ) OR ($complnt->recorded_by == $uid) ) ) {

// display edit form

}
else
{

// display security error message

}

Any other ideas on the primary issue would also be greatfully received. Thanks.
#4

[eluser]verynewtothis[/eluser]
may be you could try doing
WHERE division LIKE $DIVSION%; (pseudocode)..
#5

[eluser]mdcode[/eluser]
I'm not sure that would actually work as wouldn't it also pick out the people from the groups going higher than 9 as well... such as say the drop-down selected 1, wouldn't that also pick out 10, 11, 12 etc as well as there are about 23 groups altogether.
#6

[eluser]verynewtothis[/eluser]
I think you will find your answer here..

Code:
http://forums.mysql.com/read.php?52,48678,48678
#7

[eluser]davidbehler[/eluser]
Try something like this:
Code:
function get_division($division = '')
{
    $this->db->select('first_name, last_name');
    $this->db->from('users');
    $this->db->like('division',$division.',');
    $this->db->or_like('division',','.$division.',');

    $query = $this->db->get();

    $temp = array();

    if($query->num_rows() > 0)
    {
        foreach($query->result_array() as $user)
        {
            $groups = explode(',', $user['division'];
            foreach($groups as $group)
            {
                if($group == $division)
                {
                    $temp[] = $user;
                }
            }
        }

        if(count($temp) > 0)
        {
            return $temp;
        }
        else
        {
            return FALSE;
        }
    }
    else
    {
        return FALSE;
    }
}
You might have to take into account that $division might be empty. With my solution that will return FALSE.

The better option would be to change your database layout, but as far as I understood that's not an option at all ^^

€dit:
Damn you verynewtothis, that's a nice one Smile
#8

[eluser]mdcode[/eluser]
Sorry about the delay on this one, damn time zones... but thanks very much guys, the solution from verynewtothis worked a treat. Thanks also to waldmeister, I'm not quite sure if your solution would have worked in my case as well, I just want to thank you for taking the time to try to help and write it up - the link to the mysql forums was just a quicker, neater solution.

Thanks again guys.

For those visiting this thread with the same issue, the following is my working code:

MODEL
Code:
function get_division($division = '')
{
   $query = $this->db->query("SELECT first_name, last_name FROM cfb_users WHERE FIND_IN_SET('$division', division)");
   $results = $query->result_array();

   return $results;
}




Theme © iAndrew 2016 - Forum software by © MyBB