Comparing query results and assigning the ID..?

[eluser]rvent[/eluser]
Hello,

i have 10 tables the main table has a bunch of FK that link the tables together.

In order to insert a record in the main table i have to insert the PK's value that relates to the especific record on the other table.

i have this for one of the values and so far it works:

Code:

function fill_WorkOrder()
    {
        $wos = $this->Import->old_db();
        
        foreach ($wos as $old_row)
        {
            $partn = $old_row->part_number;
            $worko = $old_row->work_order;

                        // query to get the primary id of $partn in PartNumber table
                        $this->db->select('PartNumberID, PartNumber');
            $this->db->from('smt_dev.PartNumber');
            $this->db->where('PartNumber', $partn);
            
            $que = $this->db->get();
            
            foreach ($que->result() as $par_id)
            {
                $parid = $par_id->PartNumberID;

                $this->db->set('WOrder', $worko);
                ... more here sets here
                  $this->db->set('WOrderID', NULL);
                $this->db->set('PartNumberID', $parid); <-works
                $this->db->set('PartLocationID', $ploc);  <-----
                $this->db->set('PartPriorityID', $pprio);              |
                $this->db->set('PartStateID', $psta);                   |
                $this->db->set('PartStatusID', $pstatus);             --- Doesnt work since values are string and not the IDs on the tables
                $this->db->set('MessageID', NULL);                    |
                $this->db->insert('WorkOrder');              <-----|
            
            }

i could have as many as i need of:

Code:

$this->db->select('PartNumberID, PartNumber');
            $this->db->from('smt_dev.PartNumber');
            $this->db->where('PartNumber', $partn);
            
            $que = $this->db->get();
            
            foreach ($que->result() as $par_id)
            {
                $parid = $par_id->PartNumberID;

But:
1- Would i be bale to assing several of $this->db..... to different variables like the one above..? It seems that every time the get() is used, you could then query for something else and assign it to a new variable..
2- Is there an easier way of doing it..? since i would have to repeat a lot of code with only different queries..

i have a model that gets all the data from the old DB. i assign one of those old values to a variable and then use it to get the primary ID of a table where priority = 'highest'

Any thoughts...

[eluser]rvent[/eluser]
Is this legal..?

Code:

$this->db->select('PartNumberID, PartNumber');
            $this->db->from('smt_dev.PartNumber');
            $this->db->where('PartNumber', $partn);
            
            $que = $this->db->get();
            
            foreach ($que->result() as $par_id)
            {
                $parid = $par_id->PartNumberID;
                
                $this->db->select('PartLocationID, PartLocation');
                $this->db->from('smt_dev.PartLocation');
                $this->db->where('PartLocation', $ploc);
                $pl = $this->db->get();
                
                foreach ($pl->result() as $loc_id)
                {
                    $locid = $loc_id->PartLocationID;
                    
                    $this->db->select('PartPriorityID, PartPriority');
                    $this->db->from('smt_dev.PartPriority');
                    $this->db->where('PartPriority', $pprio);
                    $priori = $this->db->get();
                    
                    foreach ($priori->result() as $pri_id)
                    {
                        $prid = $pri_id->PartPriorityID;
                        
                        $this->db->select('PartStateID, PartState');
                        $this->db->from('smt_dev.PartState');
                        $this->db->where('PartState', $psta);
                        $ps = $this->db->get();
                        
                        foreach ($ps->result() as $psta_id)
                        {
                            $pstaid = $psta_id->PartStateID;
                            
                            $this->db->select('PartStatusID, PartStatus');
                            $this->db->from('smt_dev.PartStatus');
                            $this->db->where('PartStatus', $pstatus);
                            $psu = $this->db->get();
                            
                            foreach ($psu->result() as $pstatu_id)
                            {
                                $pstatuid = $pstatu_id->PartStatusID;
                                
                                $this->db->set('WOrder', $worko);
                                $this->db->set('WODateIn', $datein);
                                $this->db->set('WODateOut', $dateout);
                                $this->db->set('WOCreatedAt', $createdat);
                                $this->db->set('WOUpdatedAt', $updatedat);
                                $this->db->set('WOCompletedAt', $updatedat);
                                $this->db->set('WOPartQuantity', $quantity);
                                $this->db->set('WOrderID', NULL);
                                $this->db->set('PartNumberID', $parid);
                                $this->db->set('PartLocationID', $locid);
                                $this->db->set('PartPriorityID', $prid);
                                $this->db->set('PartStateID', $pstaid);
                                $this->db->set('PartStatusID', $pstatuid);
                                $this->db->set('MessageID', NULL);
                                $this->db->insert('WorkOrder');
                            }
                        }
                    }
                }
            }

[eluser]Gavin Vickery[/eluser]
Well, first off I don't think CI Active Record methods are the best solution for this kind of query. I would write a custom query using JOIN methods. This allows you to retrieve multiple IDs from separate tables in one query.

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

UPDATE

Apparently CI does support JOIN in the Active Record. Heres the link.

http://ellislab.com/codeigniter/user-gui...ecord.html

[eluser]rvent[/eluser]
I found another way of doing it... I wanted to get the data from the old DB assign it to a variable that would be used to query a different DB which will return the record ID depending on the variable's value.

So i wrote a few elseif in order to assign values that meet a criteria. The export of data from the old DB to new normilzed table worked with no problems at all.


Thanks