Welcome Guest, Not a member yet? Register   Sign In
Insert into multiple tables.
#1

[eluser]Ralanyo[/eluser]
I'm new to ci and this is probably an easy question.
I am building an app with a relational MySQL database.

I've been able to display data from related tables via the use of an active record join, but how do I insert into into related tables?

Do I need to learn to use an orm, can it be done with joins, etc...can it be done with active record or do I need to use SQL? Any help would be appreciated. Sorry for the newbie question.
#2

[eluser]Aken[/eluser]
Regarding the query itself - if all of the information you are inserting is brand new (non-existent in the DB anywhere), then you cannot insert into multiple tables at the same time through a single query. You can, however, use transactions if your DB supports them, to prevent any partial inserts or most weird happenings should any part fail.

See the first few StackOverflow article results here for some good information.

An ORM might have some built-in solution that helps keep your code clean, but essentially does either multiple queries or a transaction. That's an option, but I don't know of any specific ORMs that do this off the top of my head (I am unfamiliar with most of them).
#3

[eluser]Ralanyo[/eluser]
Thanks for the reply. I've been doing some research and there are a bunch of different opinions. Transactions is one that i found. I guess an orm makes it easier to work with table relationships.

My database is myisam right now and that doesn't support foreign keys. I know it's not that difficult to change the database to innodb, but I was wondering if others are developing without foreign key relationships somehow. easy to display with a join, but not so easy with inserts , updates and deletes.

I have one form that needs to insert data into a company table and a sales rep table. Eventually i want to be able to query data later to see all companies associates to a specific sales rep. (pretty sure I can do this with a join and where)

I also wanted to use active record. Some have said to write two separate insert statement in the model for each table. Is this standard practice?
#4

[eluser]srpurdy[/eluser]
[quote author="Ralanyo" date="1344549571"]Thanks for the reply. I've been doing some research and there are a bunch of different opinions. Transactions is one that i found. I guess an orm makes it easier to work with table relationships.

My database is myisam right now and that doesn't support foreign keys. I know it's not that difficult to change the database to innodb, but I was wondering if others are developing without foreign key relationships somehow. easy to display with a join, but not so easy with inserts , updates and deletes.

I have one form that needs to insert data into a company table and a sales rep table. Eventually i want to be able to query data later to see all companies associates to a specific sales rep. (pretty sure I can do this with a join and where)

I also wanted to use active record. Some have said to write two separate insert statement in the model for each table. Is this standard practice?[/quote]

Yeah 2 inserts. I use it all the time frankly, and I've never had a row gone missing. Even in cases of 10,000 plus users actively using the systems in question. (not all at once obviously)

You can use $this->db->insert_id() if you need to extract the id of the first insert.

I've done as much as 5 or more at once depending on how complex the read queries would be. Never had an issue doing it this way so I don't see why not too.
#5

[eluser]Ralanyo[/eluser]
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

#6

[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.
#7

[eluser]Ralanyo[/eluser]
That is great. Thanks for the example
#8

[eluser]srpurdy[/eluser]
[quote author="Ralanyo" date="1344571795"]That is great. Thanks for the example[/quote]

No Problem, and yeah to anwser your question. the insert_id() would be the id of the auto_increment field in that table. (or primary key)




Theme © iAndrew 2016 - Forum software by © MyBB