2. PHPRunner General questions
  3. [SOLVED]  update multiple tables after insert record
This topic is locked
[SOLVED]

 update multiple tables after insert record

 		10/1/2010 10:48:20 AM
PHPRunner General questions
J
 jasonfrew author

Hi
I have 2 queries that i would like my application to run after a record is added.
The process is the user creates a quote in the system some time in the past, with products attached to it in a child table.
At some point after that, if the quote is accepted by the client, the order will then be progressed to a job and an order raised

The job is stored in a separate table from the quote info however they are both tied to the products table (the products table is a child table)
the queries are as follows:
update Enquiries

set Enquiries.job_no = (select jobs.Job_no

from jobs

where enquiries.quote_no = jobs.quote_no and jobs.date_added = GETDATE())
Update products

Set Products.job_no = (select jobs.job_no

from Jobs

where products.quote_no = jobs.Quote_no and jobs.date_added = getdate())
how can i action these queries after the job record is added. or is there an easier way to update these tables with the information required
Thanks in advance
Regards
Jason
Sergey Kornilov admin 10/1/2010

Jason,
you can add PHP code that executes those queries to Jobs AfterAdd event. Is that what you asking?
J
 		jasonfrew author 10/1/2010



Jason,
you can add PHP code that executes those queries to Jobs AfterAdd event. Is that what you asking?


Hi yeah
Im looking for a bit of sample code to get me going please
Regards
Jason
Sergey Kornilov admin 10/1/2010

I recommend to use Data Access Layer:

http://xlinesoft.com/phprunner/docs/data_access_layer.htm
Check CustomQuery() function.
J
 		jasonfrew author 10/1/2010

CustomQuery($sql)
$sql = "update Enquiries set Enquiries.job_no = (select jobs.Job_no from jobs where enquiries.quote_no = jobs.quote_no and jobs.date_added = GETDATE())";

CustomQuery($sql);
does this look correct ?
if so do i put it in the after record added event?
Sergey Kornilov admin 10/1/2010

Yes and yes.
J
 		jasonfrew author 10/4/2010

Hi Thanks for the info

ive added the code to the event but i get an error when i check the syntax
the error is "syntax error, unexpected T_VARIABLE in line 29"
the code in the event looks like this:
//** Send email with new data ****
$email="email.addresses@mydomain.com";

$from="my.application@mydomain.com";

$msg="";

$subject="New Job Added";
foreach($values as $field=>$value)

{

if(!IsBinaryType(GetFieldType($field)))

$msg.= $field." : ".$value."\r\n";

}
$ret=runner_mail(array('to' => $email, 'subject' => $subject, 'body' => $msg, 'from'=>$from));

if(!$ret["mailed"])

echo $ret["message"];
CustomQuery($sql)
$sql = "update Enquiries set Enquiries.job_no = (select jobs.Job_no from jobs where enquiries.quote_no = jobs.quote_no and jobs.date_added = GETDATE())";

CustomQuery($sql);
// Place event code here.

// Use "Add Action" button to add code snippets.
Can you please advise?
Regards
Jason
A
 		ann 10/4/2010

Jason,
you've missed semicolon at the end of the following line:

CustomQuery($sql)
J
 		jasonfrew author 10/4/2010



Jason,
you've missed semicolon at the end of the following line:

CustomQuery($sql)



Thanks Ann
J
 		jasonfrew author 10/15/2010

hi
I know this is marked as solved however the query is not updating the required tables after the record is submitted
i think it may be down the the date part of the query. however this is the only way i can think of running the query on the tables without it querying every record in the tables
can you advise another way to achieve the same result
Regards
Jason