How to fetch columns data from a table using the foreign key in CodeIgniter 3-CodePudding

Hi first of all I should tell you that English is not my first language so excuse any misunderstandings. What I'm trying here is to access to the "donors" table data using the foreign key in the "packets" table and I need to get that data in the controller to use. I refer PacketID in my view and in the staff controller I need to get the specific DonorMobile and DonorName to send an SMS to that donor. I have tried creating multiple model functions but didn't work. Can it be done by that way or is there any other way? TIA!

Screenshots of donors and packets tables

donors table and packets table

Staff controller - I know you can't access data like $donorData->DonorID in the controller

public function markAsUsed($packet)
    {
            $this->load->Model('Donor_Model');
            $donorData = $this->Donor_Model->getDonors($packet);

            $this->load->Model('Donor_Model');
            $data = $this->Donor_Model->markAsUsed($donorData->DonorID);

            $sid = 'twilio sid';
            $token = 'twilio token';
            $donorMobile = ' 94' . $donorData->DonorMobile;
            $twilioNumber = 'twilio number';

            $client = new Twilio\Rest\Client($sid, $token);
            $message = $client->messages->create(
                $donorMobile, array(
                    'from' => $twilioNumber,
                    'body' => 'Thank you ' . $donorData->DonorName . ' for your blood donation. Your donation has just saved a life.'
                )
            );

            if ($message->sid) {
                $this->load->Model('Donor_Model');
                $this->Donor_Model->changeStatus($data->isAvailable);
                redirect('staff/viewpackets');
}
}

Model

function getDonors($packet) {
        $data = $this->db->get_where('packets', array('PacketID' => $packet));
        return $data->row();
    }

function markAsUsed($donor)
    {
        $data = $this->db->get_where('donors', array('DonorID' => $donor));
        return $data->row();
    }

function changeStatus($packet)
    {

        $data = array(
            'isAvailable' => False,
        );

        return $this->db->update('packets', $data, ['PacketID' => $packet]);
    }

CodePudding user response：

Found the answer! You can do this only using the controller.

$packetData = $this->db->get_where('packets', array('PacketID' => $packet));

            foreach ($packetData->result() as $row) {
                $donorID = $row->DonorID;
                $packetDonatedDate = $row->DonatedDate;
            }

$donorData = $this->db->get_where('donors', array('DonorID' => $donorID));

            foreach ($donorData->result() as $row) {
                $donarName = $row->DonorName;
                $donorMobile = $row->DonorMobile;
            }

CodePudding user response：

What you did in the answer is not advisable, using loops to fetch from tables will slow the system by large when you have large datasets. What was expected is to use JOIN queries to join the donors and packets tables as shown below:

$this->db->select('donors_table.DonorName,donors_table.DonorMobile,packets_table.*')->from('packets_table'0;
$this->db->join('donors_table','donors_table.DonorID=packets_table.DonorID');
$query = $this->db->get();
$result = $this->db->result();

Then you can iterate through each donor as below:

foreach($result as $row){
   $donarName = $row->DonorName;
   $donorMobile = $row->DonorMobile;
}

NB: It is always advisable to perform any fetch, insert, delete or update operations from the model, that is why Codeigniter is an MVC. Stop fetching data directly from database within Controllers.