multiple inserts into the db

[eluser]bugboy[/eluser]
Hello all

I've got a pretty heavy system working at the minute that uses linking tables.

Now when i'm inserting data into the database i have to then add data into the linking tables.

Say for example i have this set up

MySQL tables

Code:

cms_colour
id (pk)
title
swatch

Code:

cms_item
id (pk)
title
dscpn

and the linking table

Code:

cms_item_colour
item_id (fk)
colour_id (fk)

Everytime i add an item i have to loop through post array and insert the keys into the linking table (cms_item_colour)

I use to do it like this using a custom function in my old none CI system

bare in mind that this is old code but it worked really well

Code:

function sqlMulitInsertTwo($table_name, $fk1, $fk2, $primaryKey, $array){
    global $connector;
    $insert = 'INSERT INTO '.$table_name.' ('.$fk1.', '.$fk2.') VALUES ';
    $numberOf = count($array)-1;
    for ($count = 0; $count <= $numberOf; $count++ ){
        $insert .= '(\''.$primaryKey.'\', \''.$array[$count].'\')';
        if($count != ($numberOf)){
            $insert .=',';
        }
    }
    //return true if success false if failure.
    if ($result = $connector->query($insert)){
        return true;
    }else{
        return false;
    }
}

Can this be done using active records or is it best to make it a normal query?

instead of

Code:

function addColourToItem($id, $colour)
    {
    $this->db->where('item_id', $id);
    $this->db->delete($this->_item_colour);
    if(!empty($colour))
    {
    foreach($colour as $key=>$value)
    {
    $update = array('colour_id' => $value, 'item_id' => $id);
    $this->db->insert($this->_item_colour, $update);
    }
    }
    }

From what i have read its best to let SQL do the work as thats what its best for.

Any ideas or advice as i really need to clean up my system bit by bit

Cheers