⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

how to update data in 2nd table after insert into 1st table?



Peter Rath
  • profile picture
  • Member

Posted 08 December 2012 - 10:22 AM

Hi All,

I'm new to CodeIgniter and GroceryCrud. I just wonder how could I insert into 1st table, then after that want to update data in 2nd table. below is my scenario.

I got 2 tables. Inventory(id, name, price) and Price(id, inventory_id, price, date).

Because price always change and want to keep track of the price so table [Price] is used to set the price, then update price in [inventory] table base on field [inventory_id] of [Price] table.

Note: I'm able to add/delete/update price in table [Price]. just cannot update data in table [Inventory] after I add price in [Price] table.

Waiting reply and many thanks,

victor
  • profile picture
  • Member

Posted 10 December 2012 - 06:21 AM

Hi Peter Rath and welcome to the forum. You can use the callback_after_insert.
All documentations are here

jjwdesign
  • profile picture
  • Member

Posted 11 December 2012 - 02:02 AM

I did something similar with a callback_after_insert() for a product audit table. It uses CodeIgniters Active Record "shorthand" for the DB calls. I also used Ion Auth for the user credentials. These would be a methods in your controller.


function _callback_after_update($post_array, $id) {

if (empty($post_array['approved'])) {
$post_array['approved'] = 'No';
}
$user = $this->ion_auth->user()->row();
$data = array(
'approved' => $post_array['approved'],
'last_mod_by' => $user->id,
'last_mod_date_time' => date('Y-m-d H:i:s'),
);
// Calculate the time spent on the record and add to time_spent
if (preg_match('/([0-9]{2})\:([0-9]{2})\:([0-9]{2})/', $post_array['time_spent_readonly'], $parts)) {
$data['time_spent'] = $parts[1]*3600 + $parts[2]*60 + $parts[3];
}
$this->db->where('product_id', $id);
$this->db->update('products', $data);

$this->_create_audit($id);
}

function _callback_before_delete($id) {
$this->_create_audit($id);
}


// Get product and create audit record
function _create_audit($id) {

$product_data = $this->db->get_where('products', array('product_id' => $id), 1)->row_array();
if (!empty($product_data)) {
$this->db->insert('products_audit', $product_data);
}
}

tlc033
  • profile picture
  • Member

Posted 11 December 2012 - 08:48 AM

You can use MYSQL views as a solution.
http://dev.mysql.com/doc/refman/5.0/en/create-view.html