2. PHPRunner General questions
  3. [SOLVED]  Add Child records based on SQL query when parent record is added
This topic is locked
[SOLVED]

 Add Child records based on SQL query when parent record is added

 		9/24/2019 10:31:12 AM
PHPRunner General questions
D
 druck281 author

Hello. I'm working on an application to fill staff overtime that includes equalization. When a shift of OT is created, it is recorded in the OT_Shift table with a PK of Shift_ID. When that shift is created, I want the application to run a query to get all of the eligible staff (based upon the selected Job type) and the number of shifts that they have been offered. The results of that query need to be inserted into the child table OT_Shift_Roster. I have the MySQL query working (in MySQL Workbench) when I hard code the 3 variables that would be coming in from the web form but I don't know how to write the Event code in PHPRunner to grab those variables and insert the results of the query into the table. The MySQL query is as follows...



INSERT INTO OT_Shift_RosterRoster (RosterID,ShiftID,ShiftCount)

SELECT

s.Staff_ID,

'[Need the newly created Shift_ID here]',

COALESCE(SUM(ResponseValue), 0) AS Shift_Count

FROM Staff s

LEFT JOIN OT_Shift_Roster sr

ON s.Staff_ID=sr.RosterID

LEFT JOIN OT_Response otr

ON sr.Response=otr.OT_Response_ID

LEFT JOIN OT_Shift ots

ON sr.ShiftID=ots.Shift_ID AND ots.OT_PeriodID='[Value from drop-down field OT_PeriodID]'

WHERE

s.OT_Eligibility = '[Value from drop-down field JobType]'

GROUP BY s.Staff_ID


I assume this belongs in the After Record Added Event on the OT_Shift Add Page.
Here is what I tried but I am obviously missing something in there.



//********** Save new data in another table ************

// note: text field values need to be wrapped by single quotes

$sql = "INSERT INTO OT_Shift_Roster (RosterID, ShiftID, ShiftCount) ";

$sql .= "SELECT s.Staff_ID, ".$keys[Shift_ID].", COALESCE(SUM(ResponseValue), 0) AS Shift_Count FROM Staff s LEFT JOIN OT_Shift_Roster sr ON s.Staff_ID=sr.RosterID LEFT JOIN OT_Response otr ON sr.Response=otr.OT_Response_ID LEFT JOIN OT_Shift ots ON sr.ShiftID=ots.Shift_ID AND ots.OT_PeriodID=".$values[OT_PeriodID]." WHERE s.OT_Eligibility =".$values[JobType]." GROUP BY s.Staff_ID

DB::Query($sql);


Thanks!
admin 9/24/2019

Wrong:

$keys[Shift_ID]


Right:

$keys["Shift_ID"]


etc
D
 		druck281 author 9/24/2019



Wrong:

$keys[Shift_ID]


Right:

$keys["Shift_ID"]


etc


Still getting



Parse error: syntax error, unexpected 'Add' (T_STRING) in I:\My Documents\PHPRunnerProjects\OREPOL3\output\include\ot_shift_events.php on line 123


The values in all three variables are integers. Do they still need the quotes if they're integers? Also notice that when I look at the code in the events area, most of it is grey. Only the words sql, $keys and $values are colored. Do I not need the quotes around the sql expression?
admin 9/24/2019

This error is coming from somewhere else. Yes, you do need quotes around SQL query and you already have those.
I recommend starting by using Syntax Check in the Event Editor to find which line of code is not correct.
D
 		druck281 author 9/24/2019

You were right...At the end of that long line, I was missing the last " and the ;.

Working now. Thank you!



$sql = "INSERT INTO OT_Shift_Roster (RosterID, ShiftID, ShiftCount) ";

$sql .= "SELECT s.Staff_ID, ".$keys["Shift_ID"].", COALESCE(SUM(ResponseValue), 0) AS Shift_Count FROM Staff s LEFT JOIN OT_Shift_Roster sr ON s.Staff_ID=sr.RosterID LEFT JOIN OT_Response otr ON sr.Response=otr.OT_Response_ID LEFT JOIN OT_Shift ots ON sr.ShiftID=ots.Shift_ID AND ots.OT_PeriodID=".$values[OT_PeriodID]." WHERE s.OT_Eligibility =".$values[JobType]." GROUP BY s.Staff_ID";

DB::Query($sql);