Welcome Guest, Not a member yet? Register   Sign In
Need Help With Selecting Data
#1

[eluser]Jay Logan[/eluser]
I have a page that displays all the athletes for a coach in a drop down menu. The coach then chooses 6 athletes to run a relay event. What I want to do now is remove those 6 athletes from the list so that the coach can choose 6 more athletes without accidentally choosing duplicates. But I just can't seem to get it to work. Could it be all those WHERE statements?

Code:
function get_relay_roster($meet_id, $user_id, $gender, $meet_events_id)
    {
        $this->db->select('rosters.user_id, rosters.athlete_id, athletes.gender, athletes.fname, athletes.lname');
        $this->db->from('rosters');
        $this->db->where('rosters.user_id', $user_id);
        if ($gender != 'X') {
            $this->db->where('athletes.gender', $gender);
        }
        $this->db->join('athletes', 'athletes.id = rosters.athlete_id');
        $get_roster = $this->db->get();
        $roster = $get_roster->result_array();

        $good_roster[''] = "Choose Athlete";

        foreach ($roster as $athlete) {
        
            $this->db->select();
            $this->db->from('meet_entries');
            $this->db->where('meet_event_id', $meet_events_id);
            $this->db->where('athlete_id !=', $athlete['athlete_id']);
            $this->db->where('athlete_id_2 !=', $athlete['athlete_id']);
            $this->db->where('athlete_id_3 !=', $athlete['athlete_id']);
            $this->db->where('athlete_id_4 !=', $athlete['athlete_id']);
            $this->db->where('athlete_id_5 !=', $athlete['athlete_id']);
            $this->db->where('athlete_id_6 !=', $athlete['athlete_id']);
            $athlete_check = $this->db->get();
            
            if ($athlete_check->num_rows() > 0) {
            
                $good_roster[$athlete['athlete_id']] = $athlete['fname'].' '.$athlete['lname'];
            }
        }

        return $good_roster;
    }
#2

[eluser]cwt137[/eluser]
I would use the $this->db->where_not_in(); AR method. First you run the query that gets the six athletes. Don't worry about the exclude part yet. Put the athlete IDs in an array like $athlete_id, then run another query that selects all the athletes that meet your requirements, but this time you put in something like
Code:
$this->db->where_not_in('rosters.athlete_id', $athlete_id);
. This will exclude those six athletes based on their athlete id.
#3

[eluser]Mike Ryan[/eluser]
Hi,

I can't think of a clean way to get rid of those WHEREs with your current data structure. As your field names (athlete_id_X) are hard-coded, at some point you will need to specify them by name. How about normalising your data? If you had a separate table containing meet_event_id and athlete_id it would be possible to get the list of athletes in a single query, without looping on $roster.
#4

[eluser]Jay Logan[/eluser]
Thanks cwt137. Seems to be working now with this.
Code:
function get_relay_roster($meet_id, $user_id, $gender, $meet_events_id)
    {
        $this->db->select();
        $this->db->from('meet_entries');
        $this->db->where('meet_event_id', $meet_events_id);
        $get_entries = $this->db->get();
        if ($get_entries->num_rows() > 0) {
        
            $entries = $get_entries->result_array();

            foreach ($entries as $entry) {
            
                $athletes[$entry['athlete_id']] = $entry['athlete_id'];
                $athletes[$entry['athlete_id_2']] = $entry['athlete_id_2'];
                $athletes[$entry['athlete_id_3']] = $entry['athlete_id_3'];
                $athletes[$entry['athlete_id_4']] = $entry['athlete_id_4'];
                $athletes[$entry['athlete_id_5']] = $entry['athlete_id_5'];
                $athletes[$entry['athlete_id_6']] = $entry['athlete_id_6'];
                
            }
        }

        $this->db->select('rosters.user_id, rosters.athlete_id, athletes.gender, athletes.fname, athletes.lname');
        $this->db->from('rosters');
        $this->db->where('rosters.user_id', $user_id);
        if ($gender != 'X') {
            $this->db->where('athletes.gender', $gender);
        }
        if ($get_entries->num_rows() > 0) {
            $this->db->where_not_in('rosters.athlete_id', $athletes);
        }
        $this->db->join('athletes', 'athletes.id = rosters.athlete_id');
        $get_roster = $this->db->get();
        $roster = $get_roster->result_array();

        $good_roster[''] = "Choose Athlete";

        foreach ($roster as $athlete) {
        
            $good_roster[$athlete['athlete_id']] = $athlete['fname'].' '.$athlete['lname'];
        }

        return $good_roster;
    }




Theme © iAndrew 2016 - Forum software by © MyBB