Insert in a table with a query referencing two joined tables

Check this article that explains how to update/insert data in joined tables:

http://xlinesoft.com/phprunner/docs/update_multiple_tables.htm

Dear admin,
Sorry but I think that you didn't understand the problem. I don´t need to add or update TWO tables at the same time, and this isn't a problem of a master and detail table, because I add/update ONLY the master table, i.e. carsmodels table. The other table, carsmake, is used only to SHOW the name of the make in the LIST page, because the make id and its name already exists (and must exist) in carsmake! Furthermore, in the ADD page, the make field (of carsmodels, of course) has a lookup wizard upon carsmake table, which obviously show EXISTING make id/names, so this ADD page doesn't need to add nor update carsmake table at all. Only the fields of carsmodels table are choosen to be shown in the ADD page. And only a new record in carsmodels table has to be inserted at a time.
I repeat that the main problem is that the make field in both tables has the same name (and this is a must), so I have to use aliases or table names in the list of fields and in the join clause to solve the ambiguous names, therefore PHPRunner internally builds the INSERT for carsmodels using the alias or table name before each field, which results in a sintax error. PHPRunner should avoid this alias or table name.
I hope you can understand it better now, thank you for your help.

Is there anything I can do?
Carlos

The article I have supplied explains how to remove aliased fields from INSERT or UPDATE queries.
If this doesn't help contact support directly sending your project directory zipped along with your database structure for investigation.

Dear admin,
I was trying all the dal stuff and, anwering to your reply, what I need is to remove the alias part of the name in the insert, not to remove the aliased field. Assuming that the original query is:
SELECT

emi.strorgtipo,

emi.strorgiden,

org.strorgdeno,

strorgsigl,

strorgtitu,

straratipo,

straraiden,

strinsiden,

strorgagen,

datorgalta,

strorgbaja,

datorgbaja,

strusucodi

FROM operaciones.tblorgemis AS emi,

operaciones.tblorganiz AS org

WHERE emi.strorgtipo = org.strorgtipo AND

emi.strorgiden = org.strorgiden

ORDER BY emi.strorgtipo, emi.strorgiden
The ADD page is to add a new record only in operaciones.tblorgemis table. And the result I get when I try to add a new record is:
ERROR: column "emi" of relation "tblorgemis" does not exist at character 137

STATEMENT:

insert into "operaciones"."tblorgemis" (strorgsigl, strorgtitu, straraiden, strinsiden, strorgagen, datorgalta, strorgbaja, datorgbaja, emi.strorgtipo, emi.strorgiden, straratipo, strusucodi) values ('SSSS', 'PRUEBITA', '001', '03', '202', '2012-05-24 19:58:30', '0', null, 'EM', '2638', 'RT', 'ccapriz')
What I need is to remove the text "emi." that you can see before the fields emi.strorgtipo and emi.strorgiden from the INSERT sentence.
Can you help me again?

Thanks in advance.

Carlos

Carlos,
sure we can help. Contact support directly sending your project directory zipped along with your database structure for investigation.

Dear admin,
I've tried the article about DAL but this wasn´t successful. Again, I think the problem is quite simple. I´ll try to explain in another words. Suppose you have two tables, carsmake and carsmodels and two people, each one can update one and only one table. Table carsmake is filled only by John and table carsmodels is filled only by David. Is it clear the situation to you? So, David can insert new records in carmodels of only the makes John had inserted before in carsmake. When doing this, David needs to know the make of a car, because you could have the same model name in two different makes, suppose a Honda Accord and a Lancia Accord. Of course, both Lancia and Honda were first inserted as makes in carsmake by John.
Besides of this, the field with the make of a car must have the same name in both tables, and this field is named make, which is the link between both tables, is it clear? Therefore, the query for David could be:
SELECT

mod.id,

mod.model,

mod.make,

mak.name

FROM carsmodels mod INNER JOIN carsmake mak ON mod.make = mak.make
Aliasses are a must on account of the ambiguous field make. The LIST page shows the four columns of this query, but the ADD page only needs the first three columns (mod.id, mod.model and mod.make). The mod.make column shows a lookup wizard over carsmake to help David to select a make.
So the soutation is quite simple. Only one table to be inserted, no master-child records at all. This is not an invoice or something related to a master and a detail tables updated both at the same time. Nothing to do with that stuff. What I need is simply to insert ONLY ONE record in ONLY ONE table, carsmodels.
The problem is in PHPRunner because when building the insert it puts something like INSERT INTO carsmodels (mod.id, mod.model, mod.make)... which ends in an error from Postgres engine like this: ERROR: column "mod" of relation "carsmodels" does not exist LINE 1: insert into "carsmodels" (mod.id, mod.... ^, because you can't put aliases nor table names in the fields list of an INSERT.
To solve the problem I tried your suggestion of using DAL to do the insert in the BeforeAdd() function of the Before record added event, like this:
global $dal;

$tblDetail = $dal->Table("carsmodels");

$tblDetail->Value['id'] = $values['id'];

$tblDetail->Value['model'] = $values['model'];

$tblDetail->Value['make'] = $values['make'];

$tblDetail->Add();

unset($values['id']);

unset($values['model']);

unset($values['make']);
This way the ADD() effectively inserts a proper new record in carsmodels, but at the same time you get an error from Postgres engine again, because the three unset() make PHPRunner to build an insert sentence like this: INSERT INTO carsmodels () VALUES (), which is syntactically invalid. And if I delete the three unset() after the Add(), I get the Postgres error again, because after this Add() PHPRunner tries to do its own INSERT in carsmodels with the syntaxis error I mentioned above.
And I've tried to do this, too:
unset($values['mod.id']);

unset($values['mod.model']);

unset($values['mod.make']);
Again, this ended in an error.
So, which is the way of cleaning the INSERT so as to get a valid list of fields? In this case, what I only want is to change the invalid

INSERT INTO carsmodels (mod.id, mod.model, mod.make) VALUES...
by the valid one:

INSERT INTO carsmodels (id, model, make) VALUES...
Is there a $_SESSION variable I can access or something related to DAL to solve the problem? Is there a way to tell PHPRunner not to use alias nor table names in the fields list of an INSERT or even an UPDATE sentence?
Which is the way you would use to solve this case in your own project?
Thank you in advance,

Carlos