Insert in multiple tables with a single form

bioaroma · Post by **bioaroma** » Thu Aug 08, 2019 2:25 am

I need to insert in multiple tables with a single form
How can I build one form as fields are splitted in multiple tables?... in order to have database normalization
Please share your experience

arbei · Post by **arbei** » Thu Aug 08, 2019 9:27 am

You may create a database view to join the tables first so that you have one form with all field. Then you may create Add page for the view and use Row_Inserting server event (see the topic Server Events and Client Scripts in the help file) to build multiple INSERT statements and execute it, e.g. you may try (Assume v2019+)

Execute("INSERT INTO table1 (column1, column2, column3, ...) VALUES ($value1, $value2, $value3, ...)");
Execute("INSERT INTO table2 (column4, column5, column6, ...) VALUES ($value4, $value5, $value6, ...)");
$this->setSuccessMessage("Inserted successfully");
return FALSE; // so the script will not try to insert to the view

where $value1 = $rsnew["column1"]. If it is string field, you should do $value1 = AdjustSql($rsnew["column1"]).

mobhar · Post by **mobhar** » Thu Aug 08, 2019 9:32 am

I usually use a Database View that joins those multiple tables.

In "Row_Inserting" server event that belongs to the main table, I will define the main table by using this code, for example:

$this->UpdateTable = "orders"; // adjust to your main table name

The fields that are not belongs to that main table should be excluded in that "Row_Inserting" server event, for example:

unset($rsnew["Customer_Name"]);
unset($rsnew["Customer_Address"]);
// ... and so forth ...

Then in "Row_Inserted" server event, I run the INSERT INTO SQL by using Execute() global function by using the $rsnew recordset object variable, for example:

Execute("INSERT INTO another_table VALUES ('" . $rsnew["Customer_Name"] . "', '" . $rsnew["Customer_Address"] . "')");

knightro · Post by **knightro** » Thu Mar 05, 2020 2:52 am

I tried to use this technique for updating multiple tables. However, when I try to use the fields that were unset() in the Row_Updating event, those same fields are not available in the Row_Updated event since they have been unset.

mobhar · Post by **mobhar** » Fri Mar 06, 2020 9:29 am

Before you unset those variables, make sure you have already assigned them to some session variables, and then use those session variables in "Row_Updated" server event.

rvanore · Post by **rvanore** » Wed Mar 30, 2022 3:54 am

I am trying to update 3 tables with one view. I have followed the instructions in this post but I am unable to get it working. Upon submit I get the following.

Failed to execute SQL: INSERT INTO hytorc_torcsafe_hycare.equipment (serialNumber,assetEquipNo,customerID,locationID,manufacturerID,toolID,toolModelID,cycleCount,dateStamp,status,fieldCalibrationID,userID,businessID,businessLocationID,purchaseOrder,workOrder,siteID,dateStarted,dateEnded,calibrationInterval,calDueDate,userIDUpdated,certificateNumber,results,attachment) VALUES ('SN0001','AS0001',80,53,1,112,289,3,'2022-03-29 19:52:54',77,'FCID0001',24,28873,48,'PO0001','WO0001',NULL,'2022-03-29','2022-03-29',9,'2023-03-29',24,'CN0001','Pass','PDF Crowd Payment received.pdf'). Error: Unknown column 'fieldCalibrationID' in 'field list' (1054)

In the "Row_Inserting" server event of the view I have

	$this->UpdateTable = 'hytorc_torcsafe_hycare.equipment';
	$this->ValidateKey = FALSE;
	return TRUE;

In the "Row_Inserted" server event of the view I have

	Execute("INSERT INTO hytorc_torcsafe_hycare.calibrations VALUES ('" . $rsnew["fieldCalibrationID"] . "', '" . $rsnew["userID"] . "', '" . $rsnew["businessLocationID"] . "', '" . $rsnew["businessID"] . "', '" . $rsnew["purchaseOrder"] . "', '" . $rsnew["workOrder"] . "', '" . $rsnew["calibrationInterval"] . "', '" . $rsnew["vanNumber"] . "', '" . $rsnew["dateStarted"] . "', '" . $rsnew["dateEnded"] . "', '" . $rsnew["userIDUpdated"] . "', '" . $rsnew["dueDate"] . "')");

	Execute("INSERT INTO hytorc_torcsafe_hycare.certificates VALUES ('" . $rsnew["certificateNumber"] . "', '" . $rsnew["attachment"] . "', '" . $rsnew["results"] . "')");

$this->setSuccessMessage("Equipment - Calibration/Certificate Inserted successfully");
return FALSE; // so the script will not try to insert to the view

In the "Row_Updating" server event of the view I have

// Row Updating event
function Row_Updating($rsold, &$rsnew) {
	// Enter your code here
	// To cancel, set return value to FALSE
	$this->UpdateTable = 'hytorc_torcsafe_hycare.equipment';
	$this->ValidateKey = FALSE;
	return TRUE;
}

The fieldCalibrationID is there so I am not sure why its unknown. I would appreciate any assistance. Thank you.

arbei · Post by **arbei** » Wed Mar 30, 2022 8:18 am

According to the error message from your database, there is no fieldCalibrationID in hytorc_torcsafe_hycare.equipment, if that field is in the view (not in the table hytorc_torcsafe_hycare.equipment), you cannot update it because you have set the UpdateTable as the table hytorc_torcsafe_hycare.equipment. You better re-read the answer above carefully and update the tables ONE BY ONE.

You may post your table schema (CREATE TABLE statements) and your revised code for discussion.

rvanore · Post by **rvanore** » Fri Apr 01, 2022 5:07 am

I have worked this out with the exception of getting the IDs of the master table before inserting into the child table. I have tried using this method

Execute("INSERT INTO another_table VALUES ('" . $rsnew["Customer_Name"] . "', '" . $rsnew["Customer_Address"] . "')");

and the one below. If you could please enlighten me??

$eqid = $rsnew["equipmentID"];	
		$fcID = $this->fieldCalibrationID->CurrentValue;
		$userID = $rsnew["userID"];
		$bID = $this->businessID->CurrentValue;
		$blID = $this->businessLocationID->CurrentValue;
		$po = $this->purchaseOrder->CurrentValue;
		$wo = $this->workOrder->CurrentValue;
		$siteID = $this->siteID->CurrentValue;
		$ds = $this->dateStarted->CurrentValue;
		$de = $this->dateEnded->CurrentValue;
		$ci = $this->calibrationInterval->CurrentValue;
		$caldd = $this->calDueDate->CurrentValue;
		$uIDu = CurrentUserID();
		$cn = $this->certificateNumber->CurrentValue;
		$fcertID = $this->fieldCertificationID->CurrentValue;
		$att = $this->attachment->CurrentValue;
		$res = $this->results->CurrentValue;
		$caldate = $this->calDate->CurrentValue;
		$certdd = $this->certDueDate->CurrentValue;
		Execute("INSERT INTO hytorc_torcsafe_hycare.calibrations ( 
		equipmentID,
		fieldCalibrationID,
		userID,
		businessID,
		businessLocationID,
		purchaseOrder,
		workOrder,
		vanNumber,
		dateStarted,
		dateEnded,
		calibrationInterval,
		dueDate,
		userIDUpdated
		) VALUES (
		'$eqid',  
'$fcID',
		'$userid',
		'$bID',
		'$blID',
		'$po',
		'$wo',
		'$siteID',
		'$ds',
		'$de',
		'$ci',
		'$caldd',
		'$uIDu'	
		");
		$calid = $rsnew["calibrationID"];
		Execute("INSERT INTO hytorc_torcsafe_hycare.certificates ( 
		calibrationID,
		equipmentID,
		certificateNumber,
		fieldCertificateID,
		results,
		attachment,
		calDate,
		dueDate 
		) VALUES (
		'$calid', '$eqid', '$fcID', '$cn', '$fcertID', '$att', '$res', '$caldate', '$certdd'
		");
	$this->setSuccessMessage("Equipment, Calibration, and Certificate Inserted Successfully");
	return FALSE; // so the script will not try to insert to the view
	}

Either way I get the same 3 variable missing.

arbei · Post by **arbei** » Fri Apr 01, 2022 11:09 am

You better check what data you have and your SQL first, e.g.

var_dump($rsnew);
$sql = "INSERT INTO ..."; // Build your SQL with $rsnew["xxx"], not $this->xxx->CurrentValue
echo $sql;
die();

rvanore · Post by **rvanore** » Tue May 21, 2024 3:02 am

I have recently upgraded my application to PHPMaker 2023 from 2020 and this code no longer consistently works. Is there something in this newer version that causes this to break? We followed all the migration instructions for each version. Here are some relevant details:

The "attachment" field is configured to accept multiple files as input.
We are using a dynamic S3 path for our UploadPath, setting it in the Row_Inserting event for the "attachment" field.
We are setting the UpdateTable manually in Row_Inserting to our ecn table.
We are unsetting the excess fields in Row_Inserting, only leaving the fields which are present in the ecn table.
We are retrieving those excess fields in Row_Inserted to manually insert into the other related tables for the shared view, getting those values from $this->fieldname->CurrentValue (or $this->fieldname->DbValue in Row_Updated for related table primary key IDs)
We are receiving no error messages in our error.log file and the application is completing both INSERT and UPDATE flows successfully. However, sometimes the attachments are successfully uploaded to S3 and sometimes they are not. Sometimes we even see only a partial success, such as 5 out of 7 files being uploaded to S3 despite having similar names. The relevant records are always present in our database after the operation.
We are on the latest version of the AWS SDK.
We are on Ubuntu 22.04 and using PHP8.3 on an Apache server running php-fpm.

Here's a snippet of those two events. Any help would be truly appreciated.

// Row Inserting event
function Row_Inserting($rsold, &$rsnew) {
	// Enter your code here
    $ecnID = ExecuteScalar("SELECT MAX( ecnID )+1 FROM hytorc_torcsafe_eng.ecn");
    $this->attachment->UploadPath = "s3://hycare-uploads/ecn/" . $ecnID . "/drawings/";
	$this->UpdateTable = 'hytorc_torcsafe_eng.ecn';	
	$this->ValidateKey = FALSE;
	
    unset($rsnew["ecnDrawingsID"]);
    unset($rsnew["drawingNo"]);
    unset($rsnew["title"]);
    unset($rsnew["rev"]);
    unset($rsnew["attachment"]);
    unset($rsnew["epjID"]);
    unset($rsnew["purchasingID"]);
    unset($rsnew["supplierID"]);
    unset($rsnew["notifyVendor"]);
    unset($rsnew["hytorcStock"]);
    unset($rsnew["stockDate"]);
    unset($rsnew["impByDate"]);
    unset($rsnew["evjID"]);
    unset($rsnew["vendorUserID"]);
    unset($rsnew["inEffectDate"]);
    unset($rsnew["digitalSig"]);
    unset($rsnew["vendorStock"]);
    unset($rsnew["vendorStockDate"]);
    unset($rsnew["ecnAcknowledge"]);

	return TRUE;
}

// Row Inserted event
function Row_Inserted($rsold, &$rsnew) {
	//echo "Row Inserted"
	
    // ECN Drawings
    $drawNo = $this->drawingNo->CurrentValue;
    $title = $this->title->CurrentValue;
    $rev = $this->rev->CurrentValue;
    $attach = $this->attachment->CurrentValue;

    // ECN Purchasing Join 
    $purchID = $this->purchasingID->CurrentValue;
    $supID = $this->supplierID->CurrentValue;
    $noteVend = $this->notifyVendor->CurrentValue;
    $hyStock = $this->hytorcStock->CurrentValue;
    $stockD = $this->stockDate->CurrentValue;
    $dStamp = $this->impByDate->CurrentValue;

    // ECN Vendor Join
    $uID = $this->vendorUserID->CurrentValue;
    $vsupID = $this->supplierID->CurrentValue;
    $effectD = $this->inEffectDate->CurrentValue;
    $dSig = $this->digitalSig->CurrentValue;
    $vendStock = $this->vendorStock->CurrentValue;
    $vendStockDate = $this->vendorStockDate->CurrentValue;

	ExecuteStatement("INSERT INTO hytorc_torcsafe_eng.ecnDrawings (
        ecnID,
        drawingNo,
        title,
        rev,
        attachment
        ) SELECT (SELECT MAX( ecnID ) FROM hytorc_torcsafe_eng.ecn),
        '$drawNo',
        '$title',
        '$rev',
        '$attach'");

	ExecuteStatement("INSERT INTO hytorc_torcsafe_eng.ecnPurchasingJoin ( 
        ecnID,
        purchasingID,
        supplierID,
        notifyVendor,
        hytorcStock,
        stockDate,
        impByDate
        ) SELECT (SELECT MAX( ecnID ) FROM hytorc_torcsafe_eng.ecn),
        '$purchID',
        '$supID',
        '$noteVend',
        '$hyStock',
        '$stockD',
        '$dStamp'");

	ExecuteStatement("INSERT INTO hytorc_torcsafe_eng.ecnVendorJoin ( 
        ecnID,
        epjID,
        userID,
        supplierID,
        inEffectDate,
        digitalSig,
        vendorStock,
        stockDate
        ) SELECT (SELECT MAX( ecnID ) FROM hytorc_torcsafe_eng.ecn),
        (SELECT MAX( epjID ) FROM hytorc_torcsafe_eng.ecnPurchasingJoin),
        '$uID', 
        '$vsupID',
        '$effectD', 
        '$dSig', 
        '$vendStock',
        '$vendStockDate'");

    $this->setSuccessMessage("ECN Created Successfully");
	
    return FALSE; // so the script will not try to insert to the view
}

arbei · Post by **arbei** » Tue May 21, 2024 7:38 am

You may enable Debug, check your php.ini settings, run the scripts again, then check the log file (of PHPMaker, e.g. log-2024-05-21.log) for any server side errors caused by your server side event.
In your Row_Inserted server event, you should use data from $rsnew instead of CurrentValues. (Not related to your question though because you said INSERT/UPDATE was sucessful).
If you can change your PHP version (many hosting providers support), you may change to lower version of PHP (e.g. 8.2 or 8.1) to test if it is related to newer PHP version since aws/aws-sdk-php supports PHP 7 also.

rvanore · Post by **rvanore** » Wed May 22, 2024 11:59 pm

Thanks for the reply. We still experienced the issues in PHP8.2. Here is our server error log for that same event:

[Wed May 22 15:26:01.827959 2024] [proxy_fcgi:error] [pid 398158:tid 128671834625600] [client 10.201.20.23:39672] AH01071: Got error 'PHP message: PHP Warning: Undefined array key "attachment" in /var/www/html/models/EcnsAdd.php on line 3133; PHP message: PHP Warning: Undefined array key "ecnID" in /var/www/html/models/Ecns.php on line 3535', referer: https://engineering.hytorcuat.com/ecnsadd?showdetail=
We know the first warning refers to unsetting the attachment field in $rsnew within Row_Inserting(), since it references $rsnew['attachment'] in EcnsAdd.php outside of the server events we can edit.`

For the upload path of the record, we are setting it in two places:

In the Fields upload folder, we have set the value to "s3://hycare-uploads/ecn/{$this->ecnID->CurrentValue}/drawings/"
In Row_Inserting(), we manually set the value to $this->attachment->UploadPath = "s3://hycare-uploads/ecn/" . $ecnID . "/drawings/";

When checking the value of UploadPath using var_dump(), we can confirm it is being set correctly each time. However, the errors we experience are largely inconsistent. Sometimes all files go up as expected, sometimes they go up with a version suffix (like (1) even though the original file did not have it), sometimes only some files go up, and sometimes nothing goes up and the S3 folder is never created.

In our server error log, earlier today we saw several instances of the following credentials error for the AWS SDK:
[Wed May 22 13:33:21.609300 2024] [proxy_fcgi:error] [pid 398158:tid 128671943730752] [client 10.201.20.23:39538] AH01071: Got error 'PHP message: PHP Fatal error: Uncaught Error: Unknown named parameter $env in /var/www/html/vendor/aws/aws-sdk-php/src/Credentials/CredentialProvider.php:115\nStack trace:\n#0 /var/www/html/vendor/aws/aws-sdk-php/src/Credentials/CredentialProvider.php(115): call_user_func_array()\n#1 /var/www/html/vendor/aws/aws-sdk-php/src/ClientResolver.php(466): Aws\\Credentials\\CredentialProvider::defaultProvider()\n#2 /var/www/html/vendor/aws/aws-sdk-php/src/ClientResolver.php(288): Aws\\ClientResolver::_default_credential_provider()\n#3 /var/www/html/vendor/aws/aws-sdk-php/src/AwsClient.php(195): Aws\\ClientResolver->resolve()\n#4 /var/www/html/vendor/aws/aws-sdk-php/src/S3/S3Client.php(327): Aws\\AwsClient->__construct()\n#5 /var/www/html/src/userfn.php(179): Aws\\S3\\S3Client->__construct()\n#6 /var/www/html/index.php(29): require_once('...')\n#7 {main}\n thrown in /var/www/html/vendor/aws/aws-sdk-php/src/Credentials/CredentialProvider.php on line 115'

However, we think this must be separate as we have experienced both successful and unsuccessful S3 uploads since then with seemingly no correlation.

Maybe an unrelated issue, but maybe related, we also noticed now that we are experiencing errors when attempting to preview the uploaded files in Add or Edit. In cases where the upload to S3 was successful, we can preview from the list and the view page with no issue. However, in Add or Edit, even though we get the clickable link and can see the size of the file, the first time we click it we get back what looks like a binary file instead of a readable one.

However, when we try a second or third time, we get this error:
{"statusCode":200,"error":{"class":"text-danger","type":"Error","description":"\/var\/www\/html\/vendor\/nyholm\/psr7\/src\/MessageTrait.php(207): Header name must be an RFC 7230 compatible string"

Any advice or insight would be truly appreciated.

Thank you very much.

arbei · Post by **arbei** » Thu May 23, 2024 9:33 am

rvanore wrote:

We know the first warning refers to unsetting the attachment field in $rsnew within Row_Inserting()

Then you may set it to [] instead of unsetting it. (Save the value first.)