CodeIgniter Forums
multiple inserts into the db - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: multiple inserts into the db (/showthread.php?tid=8303)

multiple inserts into the db - El Forum - 05-13-2008

[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

multiple inserts into the db - El Forum - 05-13-2008

[eluser]bugboy[/eluser]
after working out what to search for i found ou that multiple inserts aren't supported with active recored

So to answer my own question its a normal query for me.

multiple inserts into the db - El Forum - 07-19-2008

[eluser]srajibtechno[/eluser]
I want to generate multiple insert operation(2 different table) in one controller.How can i do this???

its urgent...i am looking forward to hear form you...


SRR

multiple inserts into the db - El Forum - 07-20-2008

[eluser]srajibtechno[/eluser]
my code is here...

&lt;?php
class Client_signup extends Controller
{
function Client_signup()
{
parent::Controller();
$this->load->helper(array('form', 'url'));
$this->load->library(array('email', 'session'));
$this->load->model(array('check_model', 'insert_model', 'edit_model'));
}
function index()
{
$normal_acc = 0;
$business_acc = 0;
$data=array();
$data['title'] = ':: Client Signing - Step1 ::';

if($this->input->post('go'))
{
$account_type =$this->input->post('account_type') ;
$sql_data_set1= array(
'profile_id' => '',
'first_name' => trim($this->input->post('first_name')),
'middle_name' => trim($this->input->post('middle_name')),
'last_name' => trim($this->input->post('last_name')),
'business_name' => trim($this->input->post('business_name')),
'user_id' => trim($this->input->post('user_id')),
'user_password' => md5(trim($this->input->post('user_password'))),
'user_email' => trim($this->input->post('user_email')),
'user_photograph' => trim($this->input->post('user_photograph')),
'contact_number' => trim($this->input->post('contact_number')),
'birth_date' => trim($this->input->post('birth_date')),
'country' => trim($this->input->post('country')),
'state' => trim($this->input->post('state')),
'city' => trim($this->input->post('city')),
'zipcode' => trim($this->input->post('zipcode')),
'location' => trim($this->input->post('location')),
'user_ip' => getenv("REMOTE_ADDR"),
'security_question' => trim($this->input->post('security_question')),
'security_answer' => trim($this->input->post('security_answer')),
'registration_date' => date('Y-m-d'),
'other_info' => trim($this->input->post('other_info')) );

$sql_data_set2 = array(
'source_id' => '',
'company_name' => trim($this->input->post('company_name')),
'company_details' => trim($this->input->post('company_details')),
'company_business' => trim($this->input->post('company_business')),
'company_address' => trim($this->input->post('company_address')),
'company_contact_number' => trim($this->input->post('company_contact_number')),
'company_email' => trim($this->input->post('company_email'))
);

if( $this->insert_model->add_data('inbook_user_profile',$sql_data_set1)==true )
{
$normal_acc++;
if ($account_type == 'business')
{
if( $this->insert_model->add_data('inbook_user_profile',$sql_data_set2)==true )
{
$business_acc++;
}
}//end checking account type
}//end if

if( $normal_acc > 0 && $business_acc > 0 )
{
$data['conf_msg'] = 'Company Registration completed....';
$this->load->view('client_signup_process',$data);
}
else if( $normal_acc > 0 && $business_acc < 0 )
{
$data['conf_msg'] = 'Registration completed....';
$this->load->view('client_signup_process',$data);
}
else
{
$this->load->view('client_signup',$data);
}
}
}