Query for Multiple Tables

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
jkingston
Posts: 8
Joined: 2020-03-12 11:04

Query for Multiple Tables

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
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Query for Multiple Tables

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

Re: Query for Multiple Tables

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
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Query for Multiple Tables

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.

Post Reply