If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
-
jkingston
- Posts: 8
- Joined: 2020-03-12 11:04
Post
by jkingston » 2021-10-16 05:24
Greetings community,
I am having a bit of trouble writing the code for a few tables that have a parent-child relationship. Consider the following from the tutorial series:
Parent Child
TABLE_N1 TABLE_N2
AND
Parent Child
TABLE_N2 TABLE_N3
Basically I am trying to construct an Invoice (N1) from Time sheets (N2) which consist of Time Records (N3
Code: Select all
/*Add files that allow the functions to connect
to the database*/
$currDir = dirname(__FILE__);
include("$currDir/lib.php");
include("$currDir/language.php");
include("$currDir/defaultLang.php");
include("$currDir/header.php");
/*Get Database Data*/
$report_from = get_sql_from('TABLE_N1');
if (!$report_from) exit(error_message('Access Denied'));
/*get report*/
$report_ID = intval($_REQUEST['ID']);
if (!$report_ID) exit(error_message('Invalid report ID', false));
/*Retrieve report information*/
$report_fields = get_sql_fields('TABLE_N1');
$res = sql("select {$report_fields} from {$report_from} AND `TABLE_N1`.`ID`={$report_ID}", $eo);
if(!($report = db_fetch_assoc($res))) exit(error_message('Report Not Found', false));
/*var dump on the variables for the report*/
var_dump($report);
/*get the timesheets associated with the Invoice
Iterate through the timesheets for a given Invoice*/
$timesheet = array(); //timesheet array
$timesheet_fields = get_sql_fields('TABLE_N2');
$timesheet_from = get_sql_from('TABLE_N2');
/*Match the timesheet 'invoice_no' field to TABLE_N1*/
$timesheet_res = sql("select {$timesheet_fields} from {$timesheet_from} AND TABLE_N2.invoice_no={$report_ID}", $eo);
/*use while loop to iterate the results since
the results will be an array*/
while ($row = db_fetch_assoc($timesheet_res)){
$timesheet[] = $row;
}
/*var dump to check the timesheets*/
var_dump($timesheet);
/* This is the last part that is not working for me, I can only see the results from the first timesheet*/
/*get the time records associated with the time sheet
Iterate through the time records for a given time sheet*/
$timerecord = array(); //findings array
$timerecord_fields = get_sql_fields('TABLE_N3');
$timerecord_from = get_sql_from('TABLE_N3');
$timerecord_res = sql("select {$timerecord_fields} from {$timerecord_from} AND TABLE_N3.ts_no = {$report_ID}", $eo);
/*use while loop to iterate the results since
the results will be an array*/
while ($row = db_fetch_assoc($timerecord_res)){
$timerecord[] = $row;
}
/*var dump to check the timerecords*/
var_dump($timerecord);
Can anyone help?
-
pbottcher
- AppGini Super Hero
- Posts: 1638
- Joined: 2018-04-01 10:12
Post
by pbottcher » 2021-10-16 08:01
Hi,
I guess that in your 3'rd part you need to iterate through the $timesheet array starting at the $timerecord_res sql statement and set the TABLE_N3.ts_no to the PK of TABLE_N2.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.
-
jkingston
- Posts: 8
- Joined: 2020-03-12 11:04
Post
by jkingston » 2021-10-28 15:22
pböttcher wrote: ↑2021-10-16 08:01
Hi,
I guess that in your 3'rd part you need to iterate through the $timesheet array starting at the $timerecord_res sql statement and set the TABLE_N3.ts_no to the PK of TABLE_N2.
Yes, what would the code be for that loop/iteration? That is where I am stuck
-
pbottcher
- AppGini Super Hero
- Posts: 1638
- Joined: 2018-04-01 10:12
Post
by pbottcher » 2021-10-30 07:41
Hi,
try
Code: Select all
/*Add files that allow the functions to connect
to the database*/
$currDir = dirname(__FILE__);
include("$currDir/lib.php");
include("$currDir/language.php");
include("$currDir/defaultLang.php");
include("$currDir/header.php");
/*Get Database Data*/
$report_from = get_sql_from('TABLE_N1');
if (!$report_from) exit(error_message('Access Denied'));
/*get report*/
$report_ID = intval($_REQUEST['ID']);
if (!$report_ID) exit(error_message('Invalid report ID', false));
/*Retrieve report information*/
$report_fields = get_sql_fields('TABLE_N1');
$res = sql("select {$report_fields} from {$report_from} AND `TABLE_N1`.`ID`={$report_ID}", $eo);
if(!($report = db_fetch_assoc($res))) exit(error_message('Report Not Found', false));
/*var dump on the variables for the report*/
var_dump($report);
/*get the timesheets associated with the Invoice
Iterate through the timesheets for a given Invoice*/
$timesheet = array(); //timesheet array
$timesheet_fields = get_sql_fields('TABLE_N2');
$timesheet_from = get_sql_from('TABLE_N2');
/*Match the timesheet 'invoice_no' field to TABLE_N1*/
$timesheet_res = sql("select {$timesheet_fields} from {$timesheet_from} AND TABLE_N2.invoice_no={$report['id']}", $eo);
/*use while loop to iterate the results since
the results will be an array*/
$pk=getPKFieldName('TABLE_N2');
$timerecord_fields = get_sql_fields('TABLE_N3');
$timerecord_from = get_sql_from('TABLE_N3');
$timerecord = array(); //findings array
while ($row = db_fetch_assoc($timesheet_res)){
$timesheet[] = $row;
/*get the time records associated with the time sheet
Iterate through the time records for a given time sheet*/
$timerecord_res = sql("select {$timerecord_fields} from {$timerecord_from} AND TABLE_N3.ts_no = {$row[$pk]}", $eo);
/*use while loop to iterate the results since
the results will be an array*/
while ($row = db_fetch_assoc($timerecord_res)){
$timerecord[] = $row;
}
}
/*var dump to check the timesheets*/
var_dump($timesheet);
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.