Copy Records from one table to another

[webnestors]

Hi all
I have searched but did not find any solution
My problem is when I insert a record in a table (Table A) then after saving it how can I copy some of the fields on Table B?
Thank you

[pbottcher]

Hi,

you can use the _after_insert hook and create a sql statement to put your needed data into another table.

[soewandi]

To Mr pbottcher,
I have table1 with fieldname as name1 and table2 with fieldname as name2.
In table1_after_insert hook, how can I use $data['name1'] also insert to table2.
I've been tried "INSERT INTO `table2`(`name2`) VALUES ($data['name2']";
But without success. Please help me. Many thanks.

My regards,
Soewandi

[jsetzer]

When inserting records using custom code + sql, don't forget to set_record_owner('tablename', $new_pk, $owner_memberID); or you may not see new records in Table View due to permissions defined.

[soewandi]

Thank you Mr Jsetzer for your advise. But I'm not to expert implementing tablename's hook, especially for after_insert and after_update.
I can not understanding, even I've read from the example in the AppGini documentation online. So, I'm very appreciate if you guide me with more example how to insert a record to table B at the same time with record in table A (assuming add new record on table A).

My regards,
Soewandi

[pbottcher]

@Soewandi,
even though this forum is very helpful, you will need to learn on how to use php, mysql and javascript/jquery in order to use more advance funcions and also what appgini does in processing the requests.
The forum is not here to code your application, but to help you out if you get stuck.

To you code

Code: Select all

INSERT INTO `table2`(`name2`) VALUES ($data['name2']

you could rather try:

Code: Select all

insert ('table2',['name2' => $data['name2']]);

after inserting the record you would need to retrieve the PK id and use Jan's code to set the record owner (if needed).

[soewandi]

I’m sorry if I’ve been demanded too far. Actually I’ve been tried my self using INSERT INTO command in tablename_after_insert and tablename_after_update, but without success. After using your code, I get the right result. Now I know the right syntax must without ‘INTO’. Thanks so much for your help.

My regards,
Soewandi

[jsetzer]

Now I know the right syntax must without ‘INTO’

Note, there is a difference between SQL's INSERT INTO tablename(col) VALUES('value')-command and AppGini's built-in insert('tablename', ['col'=>'value'], $err)-function.

---

Boilerplate code as a starting point
for using AppGini's insert()-function including setting the new record owner.

Assert, your table contains a (single column) primary key like id!

Code: Select all

// file: hooks/TABLENAME.php
// ...

function TABLENAME_after_insert($data, $memberInfo, &$args)
{
  $tn = "OTHER_TABLENAME";
  $new_data = [
    'column1' => $data['some_value_from_TABLENAME'],
    'column2' => 'Value2',
    'column3' => date('c')
  ];
  
  // insert into OTHER_TABLENAME
  if (!insert($tn, $new_data, $err))
    throw new \Exception("INSERT failed: {$err}", 1);

  // get primary key of inserted record
  $new_pk = db_insert_id(db_link());
  
  // define record-owner of new record
  if (!set_record_owner($tn, $new_pk, getLoggedMemberID()))
    throw new \Exception("Record inserted, but setting the record-owner failed", 1);
    
  return TRUE;
}
// ...

Not tested, should work.

[soewandi]

@ Mr. Jsetzer, many thanks for the insight. Previously, I don't understand why the code from pbottcher didn't use 'INTO' and can get the right result.
Once again, You always a good teacher for this forum.

My regards,
Soewandi

[elgaucho]

Just spotted this in the email bulletin, and funnily enough, it's what I have been trying to learn myself in the last few days!

In my use case, I am creating a checklist for users to verify an account opening.
When a new company is created in my "Companies" table, I insert a number of checklist documents to be verified in the "Documents_Checklist" table, by using the Companies.php file.

The record goes into "Companies_after_insert" as follows:

Code: Select all

$id= $data["id"]; /* this gets the record i just inserted, which i need to link the checklist to the parent table */
$checklist=sql("INSERT INTO Documents_Checklist set
Company_ID= $id, /* this is my linking record for the parent table in the Documents_Checklist table */
Type= 'Document A',
Received = 'No',
Status = 'Pending Review' /* these are the additional columns in my Documents_Checklist table that i want to insert. I still need to learn how to insert current date(!) */
", $eo);

if(!$checklist) {
error_log("Failed to insert data into table.");
} ELSE {
set_record_owner('Documents_Checklist', $id); /* this bit makes sure that the record is visible and linked to the parent record - very important and I'm fairly sure it was jsetzer who pointed this out to us in the other posts too - what a legend! ^^ */
}

I can then copy and paste this as many times as I need, and adjust the document name. This creates a checklist of 10 items or so for checking.
I can also pre-load records into other tables at the same time, so that each sub-group of my company has a checklist.

It's pretty cool, and I learned a lot from searching forum posts like this one!

Hopefully this additional example may help others too!

My next goal is to make the creation of records in the companies_after_insert conditional based on whether a flag in my current created record is set to Yes or No... my brain which knows zero of php and jscript hasn't gotten it's head around the syntax of variables etc... yet

[jsetzer]

Great! Anyway, you should check your set_record_owner code, because $id is your Company ID and not your (newly created) Documents_Checklist ID.

[elgaucho]

Great! Anyway, you should check your set_record_owner code, because $id is your Company ID and not your (newly created) Documents_Checklist ID.

Thank you for this feedback - I confess I'm struggling a bit to understand this function as it does not link directly to a value in my table, and the examples I'd studied to date provided the function, but not necessarily usage.

I've updated my scripts after comparing my own with the one you drafted above, which is (unsurprisingly) cleaner than my own!