andy
Posts: 183
|
| Posted: 09/25/2008, 2:26 AM |
|
Hi
I am trying to copy
a) one record first from a temporary master table to a master table with the same structure and then b) multiple records from a temporary child table to a child table with the same structure
Here's a simplified version of my code which is executed
Validation checks on BeforeExecuteUpdate
This code in After ExecuteUpdate of master form:
This is the simple version but it doesn't, of course, obtain the PrimaryKey from the newly created record to pass it as the ForeignKey in the newly created child tables so they aren't linked.
global $masterform;
$db = new clsDBConnection1();
$PID = $masterform->PID->GetValue(); //Gets the primary key field from the form
$result = $db->query("INSERT INTO mastertable (field1, field2, field3) SELECT field1, field2, field3 FROM mastertable_temp WHERE PRIMARYKEY = $PID");
// so far so good. That works fine. The autoincrement ID field is automatically created by the way
$LastID = CCDLookUp("LAST_INSERT_ID()", "mastertable","LAST_INSERT_ID()", $db); // Gets the newly created primary key to pass as the foreign key
$result2 = $db->query("INSERT INTO childtable (foreignkey, fieldA, fieldB, fieldC)
SELECT fieldA, fieldB, fieldC FROM childtable_temp WHERE foreignkey = $PID");
$db->close();
Building on the above this is where I am stuck in trying to obtain the newly created primary key ("LAST_INSERT_ID()") and passing it to the child table with a subquery.
global $masterform;
$db = new clsDBConnection1();
$PID = $masterform->PID->GetValue(); //Gets the primary key field from the form
$result = $db->query("INSERT INTO mastertable (field1, field2, field3) SELECT field1, field2, field3 FROM mastertable_temp WHERE PRIMARYKEY = $PID");
// so far so good. That works fine. The autoincrement ID field is automatically created by the way
$LastID = CCDLookUp("LAST_INSERT_ID()", "mastertable","LAST_INSERT_ID()", $db); // Gets the newly created primary key to pass as the foreign key
$result2 = $db->query("INSERT INTO childtable (foreignkey, fieldA, fieldB, fieldC)
VALUES($LastID ,(SELECT fieldA, fieldB, fieldC FROM childtable_temp WHERE foreignkey = $PID)");
$db->close();
Not sure whether my syntax is incorrect or whether it is something to do with the fact that multiple rows are selected in the subquery mixed with a single value for the $LastID, which causes an error.
Can anyone help? Thanks!
May be there is an easier way of doing this
_________________
Andy
RAD tools for rich UI controls:
http://www.koolphptools.com |
mentecky
Posts: 321
|
| Posted: 09/25/2008, 8:22 AM |
|
andy,
Try:
INSERT INTO childtable (foreignkey, fieldA, fieldB, fieldC)
SELECT $LastID, fieldA, fieldB, fieldC FROM childtable_temp WHERE foreignkey = $PID;
I think that's right.
Rick
_________________
http://www.ccselite.com |
