Updating multiple tables in SQL using an Array

Question

Currently I had a table called crm_leads for my Leads page where I already have many records. Now for the same page I have created few new fields which is now going to be stored in another table called crm_leads_details. Now I'm storing all my POST values in an array format like so:

    $main=array( 
        'priority'=>$this->input->post('priority'),
        'lead_status'=>$this->input->post('lead_status'), 
        'comment'=>$this->input->post('comment'),
        'sub_status'=>$this->input->post('sub_status'),
        'lead_agent'=>$this->input->post('lead_agent'), 
        'emailopt'=>$this->input->post('emailopt')=='Y' ?'Y':'N',
        // 'kitchen'=>$this->input->post('kitchen')
    );

   $loginid=$this->leads_model->update_lead($main,$id);

Now in my model I'm trying to use this statement to update both my tables but it does not work:

 function update_lead($maindata,$id,$w=false)
{
    if($w==true){
        $cond=$id;
    }
    else{$cond['id']=$id;}
    $sql = "UPDATE `crm_leads` LEFT JOIN `crm_leads_details` ON `crm_leads`.`id`=`crm_leads_details`.`leads_id` 
    SET ".$maindata." WHERE ".$cond;
    $query = $this->db->query($sql);  
      print_r($this->db->last_query()); die();
      return $query;
}

This gives the following output UPDATE crm_leads LEFT JOIN crm_leads_details ON crm_leads.id=crm_leads_details.leads_id SET Array WHERE Array

Additionally, since crm_leads_details is a new table there are currently no records in it so not sure if LEFT JOIN crm_leads_details ON crm_leads.id=crm_leads_details.leads_id will work here

maybe you can check this answer https://stackoverflow.com/a/1293347/9145012 — Ozal Zarbaliyev, Jan 20 '22 at 05:59

score 0 · Accepted Answer · answered Jan 20 '22 at 06:02

Write the query like this

$sql = "UPDATE `crm_leads` LEFT JOIN `crm_leads_details` ON `crm_leads`.`id`=`crm_leads_details`.`leads_id` 
    SET ";
$i = 0;
foreach($maindata as $key=>$value) {
    $i++;
    $sql .= $key." = ".$value;
    $sql .= ($i == count($maindata)) ? "" : " , " ;
}
$sql .= " WHERE ".$cond;

Updating multiple tables in SQL using an Array

1 Answers1