2. PHPRunner General questions
  3. Update another table
This topic is locked

Update another table

 		9/1/2011 11:25:52 AM
PHPRunner General questions
J
 jasonfrew author

I have a table that copy's quote information into an order. I need to update the quote table with the urn of the new job that is about to be created.
Im thinking that the before record added event when adding the job will provide the solution however it looks to me that it will just add a record to the table, when what im looking to do is update the specific record

global $conn;

$strSQLInsert = "Update dbo.enquiries (Job_no) values (Job_no)";

db_exec($strSQLInsert,$conn);


Looking at the above i know this is not going to work.
Would it be an after record added. Any help would be greatly appreciated
Regards
Jason
C
 		cgphp 9/1/2011

Check here for the right UPDATE syntax: http://www.w3schools.com/Sql/sql_update.asp
J
 		jasonfrew author 9/2/2011

Hi Thanks for the response.
If i use a direct update query that will update the full database table every time the query runs.
While this is not a problem it is not the best solution to my problem
When adding a new record to the jobs table the database creates a job URN . Only after the record is added is this number created. So i need to query the database pulling only the new job number and its associated Quote number. I then need the update query to run to update the Enquirers table with the new job number where the quote number in the jobs table is the same as the quote number in the new record added only.
C
 		cgphp 9/2/2011

After new record was added, you can access the values of the new record using the $values array or $keys array in the "After record added" event. To access specific field value use $values["FieldName"] or $keys["KeyFieldName"].
J
 		jasonfrew author 9/2/2011

Thanks for the response. however i dont think this will work either as its the database is creating the URN not the application
The urn is not part of the add/insert statement. so the application does not hold it as a value. I need to retrieve the value from the database after the record has been saved. then query and update the enquirers table.
What do you think. ?
Regards
C
 		cgphp 9/2/2011

What's the primary key field of the table ?
J
 		jasonfrew author 9/2/2011

The primary key of the field im looking to extract the informtation from is Job_no

the primary key of the table im looking to update is Enquiry_no the field in this table im looking to update is job_no
C
 		cgphp 9/2/2011

What is the field name you want to extract from the table ?
J
 		jasonfrew author 9/2/2011

Its the primary key
If i was doing it as a query the query would be

Update dbo.enquirers,



Set enquirers.Job_no = (Select job_no from dbo.jobs

Where dbo.jobs.enquirer_number = dbo.enquirers_enquirer_number)


so field to be updated is enquirers.Job_no in table enquirers
to be updated with the Job_no (primary key) form dbo.jobs
C
 		cgphp 9/2/2011

You don't need a nested query. PHPr does the job for you. In the "After record added" event of the dbo.jobs table:



global $conn;

db_exec("UPDATE dbo.enquirers SET Job_no=".$keys['job_no']." WHERE Enquiry_no=".$values['enquirer_number'],$conn);
J
 		jasonfrew author 9/2/2011

Thanks for that.
Unfortunately i did not work.
I added a test enquiry then made the enquiry into a job

the enquiry table was not update with the job number created in the job table
The primary key value is not generated by the SQL database until the record in the jobs table is added
Should the code not look more like



db_exec("UPDATE dbo.enquiries SET Job_no=".$keys['job_no']." WHERE dbo.jobs.Quote_no=".$values['Quote_no']);
C
 		cgphp 9/2/2011

In the "After record added" event enter this code:

echo $keys['job_no'];

die();



and check in firebug the Response from the server.
J
 		jasonfrew author 9/2/2011

Hi This is what i have in the After record added on the add record page

global $conn;

db_exec("UPDATE dbo.enquiries SET Job_no=".$keys['job_no']." WHERE Quote_no=".$values['Quote_no'],$conn);
echo $keys['job_no'];

die();


I have Firebug installed but have never used it so have no idea where to start to get you the information your looking for
Regards
C
 		cgphp 9/2/2011

Please, post a demo link (you can also send me a PM) and enter the following code in the "After Record Added" event:



//global $conn;

//db_exec("UPDATE dbo.enquiries SET Job_no=".$keys['job_no']." WHERE Quote_no=".$values['Quote_no'],$conn);
echo $keys['job_no'];

die();


Please, leave the first two lines commented out.
J
 		jasonfrew author 9/2/2011

Details sent Via PM
I am placing this code in the after record added on the jobs table
J
 		jasonfrew author 9/15/2011

So i have now additional code in the after record added event

global $conn;

db_exec("UPDATE dbo.Enquiries SET Job_no=".$keys['Job_no']." WHERE Quote_no=".$values['Quote_no'],$conn);
$email="My email Addresses";

$from="my email address";

$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"];


The email is sent without issue yet the update still does not run. Does any one have any thoughts..?
The SQL database is creating the job number when the job is saved not the application. this i think is my issue is this corect?