We help IT Professionals succeed at work.

Information going into the database to late...

techdesigns
techdesigns asked
on
247 Views
Last Modified: 2010-03-04
Ok, I have a script where a user adds in comments to a request and sends it.  When they send it, any comments entered goes into the database as well as if it was approved or not and then it sends a email out with all of the request information including the comments the user added.

What happens is the email is sent but does not include the comments the user added.  If i send it twice, the second email will show the comments.  If i send it three times, the third email will show the comments from the second time and so on and so on....

So from what i know, the information goes into the database but the email sends the data that is already stored in the database, not the new information in it... how do i fix this?
Comment
Watch Question

Lee WadwellProject Architect
CERTIFIED EXPERT

Commented:
Without seeing your script...it would help...it could be a few things.  Are you sure that the comments are being writen to the database prior to the retrieval of the information to build the email?  Do you have multiple connections and a commit scope issue?

Cannot be sure without seeing the relevent part(s) of the script.

Author

Commented:
Here is the source for the page where a admin would post their comments.  I took out most of the lines are irrelevant, hope this helps!

$cgi=new CGI;
$session=new CGI::Session (undef,$cgi,{Directory=>'D:/intranet/temp'});
my $idu=$session->param('id');
my $tip=$session->param('tip');

$idr=$cgi->param('idr');
$act=$cgi->param('act');
$defaultv=$cgi->param('defaultv');
$ycomm=$cgi->param('ycomm');

if ($defaultv==0) {$texts="Deny";$action="deny";}
if ($defaultv==1) {$texts="Approve";$action="approve";}
if ($defaultv==2) {$texts="Place On Hold";$action="poh";}

require 'D:/intranet/cgi-bin/cms/connect.inc';
$res=$dbh->prepare("select * from request where idr=$idr");
$res->execute;
if (@myr=$res->fetchrow())
 {
  $comments=$myr[19];
  $icomments=$myr[23];
 }
 
$res=$dbh->prepare("select * from users where id=$idreq");
$res->execute;
if (@myr=$res->fetchrow())
 {
  $requestor=$myr[3]." ".$myr[4];
  $email=$myr[5];
  $tip=$myr[6];
 }
if ($tip==0) {$urlr=$url;}
if ($tip==1) {$urlr=$url."admin/";}
if ($severity eq "3") {$nseverity="Low";}
if ($severity eq "2") {$nseverity="Medium";}
if ($severity eq "1") {$nseverity="High";}
$datas = strftime( "%B %d, %Y", localtime(time()) );
$startdate=strftime("%B %d, %Y",0,0,0,$startd,$startm-1,$starty-1900,0,0,0);
$enddate=strftime("%B %d, %Y",0,0,0,$endd,$endm-1,$endy-1900,0,0,0);
$ycomm=~s/'/\\'/i;

if ($act eq "approve")
 {
  $res=$dbh->prepare("update request set icomments='$ycomm', status=1 where idr=$idr");
  $res->execute;
  $actiont="approved";

require 'D:/intranet/cgi-bin/cms/admin/messforra.inc';
     
      my $from_address = 'changemanagement@airgatepcsa.com';
      my $cc_address = 'SEIT@airgatepcsa.com';
      my $subject = 'COMET: Request Approved';
      my $mime_type = 'text/html';
      my $mime_msg = MIME::Lite->new(
         From => $from_address,
         To   => $email,
         Cc   => $cc_address,
         Subject => $subject,
         Type => $mime_type,
         Data => $messforra
         );
  MIME::Lite->send('smtp', $ServerName);
 $mime_msg->send();
  print $cgi->header('text/html');
  printf "
   <form name=form1 action=\"requestpending.cgi\" method=post>
   </form>
   <script language=javascript>
   document.form1.submit();
   </script>
 ";
 }

print $cgi->header('text/html');
printf "
<html>
<head>
<title>CMS: Pending Request</title>
<script language=\"javascript\">
function doback()
{
 document.form2.action='requestpending.cgi';
 document.form2.submit();
}
function doback2()
{
 document.form2.action='index.cgi';
 document.form2.submit();
}

</script>
</head>
<PAGE DETAILS HERE>
</body>
</html>
";
Lee WadwellProject Architect
CERTIFIED EXPERT

Commented:
OK...

I assume that you are expecting the new comments to be in the $messforra variable that will form the email body.  I cannot see how this variable is being built/populated from the script you posted...

I assume that the comments you are refering to are stored in $icomments as retreived by the piece of code...
$res=$dbh->prepare("select * from request where idr=$idr");
$res->execute;
if (@myr=$res->fetchrow())
 {
  $comments=$myr[19];
  $icomments=$myr[23];
 }

I assume the update of the database is being done by the peice of code...
  $res=$dbh->prepare("update request set icomments='$ycomm', status=1 where idr=$idr");
  $res->execute;

Given that the select of the comments is PRIOR to the update, you possibly need to either perform the select from the request table a second time to refresh the $icomments variable AFTER the update or use just use $ycomm to overwrite $icomments (ie $icomments = $ycomm;)

am I on the right track?

Author

Commented:
Here is how it is being called in the email template...
".nl2br($comments)." for requestors comments
".nl2br($icomments)." for implementors comments

You are on the right track, i will see what it does when I try this...

Author

Commented:
Ok, where would I place the the second select of the comments at? You said after the update but like where at in the code i posted above?
Project Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
It worked, thanks!  
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.