How to update SQL View that contains 2 tables

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
DeanAtWork
User
Posts: 32

How to update SQL View that contains 2 tables

Post by DeanAtWork »

Im trying to update a view that contains 2 base tables and just need to be pointed in the right direction. I know i cant just update the view as i will get an SQL error.

So im guessing what i need to do is

Get the updated data
Stop the normal update statement from being performed
Write my own update statements to update the 2 base tables

Just not sure on which server events i should be using and how

Cheers


arbei
User
Posts: 9284

Post by arbei »

Change the "UpdateTable" to the 1st table in Row_Inserting/Row_Updating Server Event, and update the 2nd table with SQL.

For example:
$this->UpdateTable = "TABLE1";
$2ndTblField1 = $rsnew["<FIELD>"];
$2ndTblField2 = $rsnew["<FIELD>"];
$2ndTblField3 = $rsnew["<FIELD>"];
$SQL = "Update <2nd Table> SET <VALUES> WHERE <Condition>";
ew_Execute($SQL);


kirondedshem
User
Posts: 642

Post by kirondedshem »

send the schema of the two tables and the schema of the view that has both tables.
Also specifcy to us which fields in each of the two tbales you want to update and what the user fills in on the all in one form to get the two tables updated, with clear examples of some possible values in both tables both beofre and after the required update if possible. BUt what i would do is.

  1. create an editable view linking to ONLY one of the tables say table_1, this view should only contain the fields you want to update in table_1, as well as an extra link to the foreign key or whatever connects tbale_1 to table_2 if available. NOTE: Ensure to not update the link to table_2 record during update of table_1 as we only need it to get the table_2 record duirng edit and update of tbale_1 record
  2. connect this view_1 to phpmaker, NOW you should be able to edit values for table_1
  3. Next: add custom fields to the view_1, where each of those fields represents a given field in table_2 you want to update in relation to the specific table_1 record.
  4. Allow all these cutsom fileds onto the edit form of the editble view of table_1 in field settings.
  5. Use page_datarendering to auto fill values for the custom fileds to show previous values of the table_2 record on the table_1 form. Hence Users can see how the specifc fields of the table_2 record looks like in relation to the current tbale_1 record.
    NOTE:since they are custom fileds they wont be included in the update query for table_1 eitable view, BUT all table_1 records in the editable view will be updated.
    NOTE 2: All the edited values of the extra customfields will still be avialbel in row_updating and row_updated event. SO you can use these events to firmulate a query to make your update into table_2.

HINT: if the two table can be setup as master & detail, then you can use master/detail relationship to use master/detail edit form to make your update


mobhar
User
Posts: 11660

Post by mobhar »

arbei wrote:
For example:
$this->UpdateTable = "TABLE1";

When you are using that way for a Database View that involve more than one table in "Row_Inserting" or "Row_Updating" server event, then make sure also you have already unset the unnecessary field(s) by using PHP unset() function, for example:

unset($rsnew["YourFieldName"]); // adjust "YourFieldName" to your actual field name


Post Reply