2. PHPRunner General questions
  3. Inserting into 2nd Joined Table
This topic is locked

Inserting into 2nd Joined Table

 		8/19/2017 4:43:16 PM
PHPRunner General questions
W
 wheathcock author

My objective:

I would sincerely appreciate some insight with my issue using PHPRunner 9.8
I want one form for adding records that will do an insert on both tables (add to the Master and then to the Detail). I have two tables that are Master - Detail in relationship and are inner joined on key fields of id. I understand that MYSQL won't allow insert two tables at the same time. I'm wanting to perform this function in PHPRUNNER rather than having to write a Trigger in Mysql or write two queries using LAST_INSERT_ID ().
To keep it simple I'm just working with one field on the inner join table (call_notes_c), which is in the detail table. I have many more; however if someone can point me in the right direction I'll be able to add more fields after I understand the logic.
My Issue:

When I do an add record and fill in the inner joined fields and then save. I can't get the inner joined field (call_notes_c) from Details table (prospectcstm_crm) to do an insert into the table.
MY Tables:

Master is prospects_crm and detail is prospectscstm_crm. Key fields are prospects_crm = id and foreign key in prospectscstm_crm = id_c. Both tables (prospect_crm and prospectcstm_crm have Auto Increment on the Key fields = id.
My Query:

SELECT

prospects_crm.id,

prospects_crm.FirstName,

prospects_crm.LastName,

prospects_crm.FullName,

prospects_crm.MobilePhone,

prospects_crm.Address,

prospects_crm.City,

prospects_crm.State,

prospects_crm.Zip,

prospectscstm_crm.call_notes_c

FROM prospects_crm

INNER JOIN prospectscstm_crm ON prospects_crm.id = prospectscstm_crm.id
//////////////////////
My Event Code:

Prospects_Crm/Add Page /Before Record Added

global $dal;

$tblDetail = $dal->Table("prospectscstm_crm");

$tblDetail->Value["call_notes_c"] = $values["call_notes_c"];

$tblDetail->Value["id_c"] = $keys["id"];

$tblEvents->Add();

unset($values["call_notes_c"]);

unset($values["id_c"]);

return true;
/////////////////
In addition, does PHPRunner support LAST_INSERT_ID() in the events code?
Thanks in advance for help.
L
 		Lawrence Nyaga 8/20/2017

Check this Out, it might help
[url="https://xlinesoft.com/phprunner/docs/update_multiple_tables.htm"]Update two joined tables[/url]
W
 		wheathcock author 8/20/2017



Check this Out, it might help
[url="https://xlinesoft.com/phprunner/docs/update_multiple_tables.htm"]Update two joined tables[/url]


Thanks, this is referring to updating tables via join. I'm needing to do an insert - Add function. I've got the code working on the Edit function on the joined tables.
Admin 8/21/2017

You can use LAST_INSERT_ID() in events but you cannot use DAL and need to construct the whole INSERT query manually. That query can be later executed via CustomQuery function.
W
 		wheathcock author 8/21/2017



You can use LAST_INSERT_ID() in events but you cannot use DAL and need to construct the whole INSERT query manually. That query can be later executed via CustomQuery function.



Thank you. Any suggestions on my inner join Add code? I would sincerely appreciate any insight or documentation references.
Admin 8/21/2017

Your INSERT won't be different from many other INSERT queries. You need to build a proper SQL Query and execute using CustomQuery function. You can find more examples of INSERT statements at https://www.tutorialspoint.com/sql/sql-insert-query.htm
W
 		wheathcock author 8/21/2017



Your INSERT won't be different from many other INSERT queries. You need to build a proper SQL Query and execute using CustomQuery function. You can find more examples of INSERT statements at https://www.tutorialspoint.com/sql/sql-insert-query.htm



Thank you, I was wanting to use the DAL and the event process in PHPRUNNER.

My Event Code:

Prospects_Crm/Add Page /Before Record Added

global $dal;

$tblDetail = $dal->Table("prospectscstm_crm");

$tblDetail->Value["call_notes_c"] = $values["call_notes_c"];

$tblDetail->Value["id_c"] = $keys["id"];

$tblEvents->Add();

unset($values["call_notes_c"]);

unset($values["id_c"]);

return true;
The documentation enabled me to get it to work on the edit function but I can't get it to work on the Add function.

https://xlinesoft.com/phprunner/docs/update_multiple_tables.htm
Can you point me to any PHPRUNNER documentation to assist me? If not I'll just build an sql insert query.
Admin 8/21/2017

Like I said, the only way to use LAST_INSERT_ID() is to build your own SQL Query.
W
 		wheathcock author 8/21/2017



Like I said, the only way to use LAST_INSERT_ID() is to build your own SQL Query.



Sorry for my lack of communication. How can this be accomplished - forget using LAST_INSERT_ID() - using the PHPRUNNER event code? Are you saying the only possible way to accomplish this is a custom INSERT query?
Admin 8/22/2017

If you do not need to use LAST_INSERT_ID() function you can use PHPRunner's DAL function and the structure of your code looks correct in general.
There is one thing though - since record is not created yet $keys array may not be populated if key column is an autonumber field.