MichaelMcDonald
Posts: 640
|
| Posted: 10/03/2009, 5:32 PM |
|
I have 2 tables. They are named (workorders) and (invoices).
After the workorders table is updated I wish to copy the most recent idworkorder, vendorid, and industrycategoryid into invoices.
I have tried using after insert with limited success. The following produces part of the result whereby the idworkorder is retrieved from workorders as $max_workorder_id and inserted into invoices.
However, I would also like for vendorid and industrycategoryid as part of that idworkorder record to be retrieved and inserted into invoices next to $max_workorder_id.
$db = new clsDBBuild21032009();
$SQL = "SELECT MAX(idworkorder) FROM workorders";
$db->query($SQL);
if ($db->next_record()) {
$max_workorder_id = $db->f(0);
CCSetSession("workorder_id", $max_workorder_id);
}
$SQL = "SELECT (vendorid, industrycategoryid) FROM workorders" ." WHERE idworkorder =
" .$db->ToSQL(CCGetSession($max_workorder_id),ccsInteger);
{
CCSetSession("vendorid", $vendor_id);
CCSetSession("industrycategoryid", $industrycategory_id);
}
$SQL = "INSERT INTO invoices (workorderid, vendorid, industrycategoryid) " .
" VALUES (".$db->ToSQL($max_workorder_id,ccsInteger).",
" .$db->ToSQL($db->f($vendor_id),ccsInteger).",
" .$db->ToSQL($db->f($industrycategory_id),ccsInteger).")";
$db->query($SQL);
$db->close();
_________________
Central Coast, NSW, Australia.
|
 |
 |
jjrjr1
Posts: 942
|
| Posted: 10/03/2009, 6:38 PM |
|
Hi
Try This and put it in After Execute Insert Event
$db = new clsDBBuild21032009();
$SQL = "SELECT MAX(idworkorder) FROM workorders";
$db->query($SQL);
if ($db->next_record()) {
$max_workorder_id = $db->f(0);
CCSetSession("workorder_id", $max_workorder_id);
$SQL = "SELECT vendorid, industrycategoryid FROM workorders WHERE idworkorder= " .$db->ToSQL(CCGetSession($max_workorder_id),ccsInteger))";
$db->query($SQL);
$vendor_id=$db->f("vendorid");
$industrycategory_id=$db->f("industrycategoryid");
CCSetSession("vendorid", $vendor_id);
CCSetSession("industrycategoryid", $industrycategory_id);
$SQL = "INSERT INTO invoices (workorderid, vendorid, industrycategoryid) " .
" VALUES (".$db->ToSQL($max_workorder_id,ccsInteger).",
" .$db->ToSQL($vendor_id,ccsInteger).",
" .$db->ToSQL($industrycategory_id,ccsInteger).")";
$db->query($SQL);
$db->close();
}
Let me know if this works ok.
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
jjrjr1
Posts: 942
|
| Posted: 10/03/2009, 6:51 PM |
|
Hi Again
Try This instead. I missed some changes.
$db = new clsDBBuild21032009();
$SQL = "SELECT MAX(idworkorder) FROM workorders";
$db->query($SQL);
if ($db->next_record()) {
$max_workorder_id = $db->f(0);
CCSetSession("workorder_id", $max_workorder_id);
$SQL = "SELECT vendorid, industrycategoryid FROM workorders WHERE idworkorder= " .$db->ToSQL($max_workorder_id,ccsInteger)";
$db->query($SQL);
$vendor_id=$db->f("vendorid");
$industrycategory_id=$db->f("industrycategoryid");
CCSetSession("vendorid", $vendor_id);
CCSetSession("industrycategoryid", $industrycategory_id);
$SQL = "INSERT INTO invoices (workorderid, vendorid, industrycategoryid) " .
" VALUES (".$db->ToSQL($max_workorder_id,ccsInteger).",
" .$db->ToSQL($vendor_id,ccsInteger).",
" .$db->ToSQL($industrycategory_id,ccsInteger).")";
$db->query($SQL);
$db->close();
}
Let me know if this is better
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 10/03/2009, 9:43 PM |
|
Hi,
I made a few minor syntactial corrections to remove red code, and while the code structure looks correct, for some reason it still does not enter vendorid and industrycategoryid into the invoices table.
$db = new clsDBBuild21032009();
$SQL = "SELECT MAX(idworkorder) FROM workorders";
$db->query($SQL);
if ($db->next_record()) {
$max_workorder_id = $db->f(0);
CCSetSession("workorder_id", $max_workorder_id);
$SQL = "SELECT vendorid, industrycategoryid FROM workorders WHERE idworkorder=
" .$db->ToSQL(CCGetSession($max_workorder_id),ccsInteger);
$db->query($SQL);
$vendor_id=$db->f("vendorid");
$industrycategory_id=$db->f("industrycategoryid");
CCSetSession("vendorid", $vendor_id);
CCSetSession("industrycategoryid", $industrycategory_id);
$SQL = "INSERT INTO invoices (workorderid, vendorid, industrycategoryid) " .
" VALUES (".$db->ToSQL($max_workorder_id,ccsInteger).",
" .$db->ToSQL($vendor_id,ccsInteger).",
" .$db->ToSQL($industrycategory_id,ccsInteger).")";
$db->query($SQL);
$db->close();
_________________
Central Coast, NSW, Australia.
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 10/04/2009, 1:02 AM |
|
Couple things I would try.;
Do a little debuging by echoing the values you retirve from the table.
Try this for the insert SQL
$SQL = 'INSERT INTO invoices (workorderid, vendorid, industrycategoryid) VALUES("'.$db>ToSQL($max_workorder_id,ccsInteger).'", "'.$db->ToSQL($vendor_id,ccsInteger).'", "' .$db->ToSQL($industrycategory_id,ccsInteger).'")" ';
(BTW. I thought the original might have a prob. Sorry I was just a little lazy and did not look too close)
Also try echoing the SQL for the insert. See if every thing looks ok.
Also be sure the field names in the SQL are spelled correctly,
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
|
