Insert into multiple tables.

[eluser]srpurdy[/eluser]
[quote author="Ralanyo" date="1344571274"]Thanks srpurdy,

just to clarify. I'm assuming i would use the insert_id() to extract the id from the first query/ table to insert it into my second tables relationship column. Is this correct? I think i said that right.

One more question. Does this scale with more than two tables? Just trying to figure out the best practices moving foraward or if i'm just going to make the db innodb and create the foreign keys. Thanks in advance

[/quote]

Well I'm in the process of writing a forum in codeigniter. I do things like this below. I don't see any performance issues with this going on. Infact it's very fast. I guess it just depends on how many inserts you will have going on, on a regular basis. You might choose innodb for the sake of table locking of tables. Where in innodb only rows get locked. So it might be useful to use innodb anyway depending on how many people are using that system.

Code:

function insert_reply_post_temp()
  {
  #create today's date
  $datestring = "Y-m-d H:i:s";
  $time = time();
  $date = gmdate($datestring, $time);
  
  #get forum id
  $forum_id = $this->db
   ->where('forum_url_name', $this->uri->segment(3))
   ->select('forum_id')
   ->from('forums')
   ->get();
  $fid = $forum_id->row();
  
  $temp_topic = $this->db
   ->where('post_id', $this->input->post('temp_post_id'))
   ->select('*')
   ->from('forum_temp_posts')
   ->get();
  
  $temp_files = $this->db
   ->where('post_id', $this->input->post('temp_post_id'))
   ->select('*')
   ->from('forum_temp_attachments')
   ->get();
  
  foreach($temp_topic->result() as $tt)
   {
   $post_date = $tt->post_date;
   $post_title = $tt->post_title;
   $post_text = $tt->post_text;
   $forum_id = $tt->forum_id;
   $topic_id = $tt->topic_id;
   $user_id = $tt->user_id;
   $sticky = $tt->sticky;
   }
  
  $reply_array = array(
   'post_date' => $post_date,
   'post_title' => $post_title,
   'post_text' => $post_text,
   'forum_id' => $forum_id,
   'topic_id' => $topic_id,
   'user_id' => $user_id,
   'has_attachment' => 'Y'
   );
  $this->db->set($reply_array);
  $this->db->insert('forum_posts');
  
  $post_id = $this->db->insert_id();
  
  //Delete Temp Post after import
  $this->db->where('post_id', $this->input->post('temp_post_id'));
  $this->db->delete('forum_temp_posts');
  
  $total_reply = array(
   'forum_id' => $fid->forum_id,
   'topic_id' => $this->uri->segment(4),
   'post_id' => $post_id
   );
  $this->db->set($total_reply);
  $this->db->insert('forum_is_post');
  
  foreach($temp_files->result() as $tf)
   {
   $file_array = array(
     'user_id' => $tf->user_id,
     'topic_id' => $topic_id,
     'post_id' => $post_id,
     'userfile' => $tf->userfile
    );
   $this->db->set($file_array);
   $this->db->insert('forum_post_attachments');
  
   //Delete Temp Files After Import
   $this->db->where('post_id', $this->input->post('temp_post_id'));
   $this->db->delete('forum_temp_attachments');
   }
  
  $this->db->where('user_id', $this->session->userdata('user_id'));
  $this->db->select('total_posts');
  $get_posts = $this->db->get('profile_fields');
  
  foreach($get_posts->result() as $gp)
   {
   $total_posts = $gp->total_posts;
   }
  
  $array_post_count = array(
   'total_posts' => $total_posts +1
  );
  $this->db->set($array_post_count);
  $this->db->where('user_id', $this->session->userdata('user_id'));
  $this->db->update('profile_fields');
  $this->load->dbutil();
  $this->dbutil->optimize_table('profile_fields');
  return $post_id;
  }

That's 3 inserts.