Function that will Send an Email and update a MSSL table on update

QuestionsFunction that will Send an Email and update a MSSL table on update
Rheal asked 4 years ago

Hopping that this will help someone who may want to Send a Email when updating a record and or wanting to use a custom Update Query to update a SQL table at the same time.

Please feel free to make any updates to the Code or corrections.

First off, I found that when you use a function it does not use your SQL connection information from your config file.
I think there is a way to get around this with GLOBALS, but I cheated and included my connection String with in the function. 🙂

To start you need to add the following just above the $g->table = "Your Table Name";
May not have to be exactly there, but it works for me.

$e["on_update"] = array("update_request", null, false);
$g->set_events($e);

As for the function I put it at the top of my PHP page just so I didn't have to do much scrolling when editing it. You can place your function anywhere between the PHP Tags.

function update_request($data)
{
//Database connection information.
//Because you are running a SQL query within a Function, The values for your connection //string wont be pulled from your Config.php file.
//I am sure there is a way around this, but I was fighting with this script long enough and //oped to cheat a bit.

$serverName = "SERVER IP";
$usr="USERNAME";
$pwd="PASSWORD";
$db="DATABASE_NAME";

$connectionInfo = array("UID" => $usr, "PWD" => $pwd, "Database" => $db);
$conn2 = sqlsrv_connect($serverName, $connectionInfo);

//Variable needed for the PHP mail function

$to = "EMAIL ADDRESS HERE"; // this is the Email address you are sending this out too
$from = "WHO FROM EMAIL ADDRESS";//$_POST['email']; // this is the sender's Email address
$subject = "PLACE THE SUBJECT OF THE EMAIL HERE";

//Below are the values from your PHPGrid Update form
// Of course your $data value names will be different from mine
//and also you Variable names.

$request_ID = $data["params"]["Request_ID"];
$begins = $data["params"]["Request_Approved"];
$location = $data["params"]["fk_Approved_Location"];
$instructor = $data["params"]["Prime_Instructor1"];
$instructor2 = $data["params"]["Second_Instructor1"];
$student = $data["params"]["StuName"];
$stuemail = $data["params"]["email_Address"];
$dennied_date = $data["params"]["Request_Denied"];
$notes = $data["params"]["Req_Notes"];

//I am sending these values in HTML format within the Body of my email
// so the message below is the BODY of the email. Format this as you want.
$message = "
<html>
<head>
<title>TRWEB Approval/Dennied</title>
</head>
<body>
Request Information: nn<br /><br />
Request_ID: $request_IDnn<br />
Student: $student nn<br />
<br />
Location Information: nn<br /><br />
Approved Location: $location nn<br />
<br />
Date Information: nn<br /><br />
Approved_Date: $begins nn<br />
Dennied_Date: $dennied_date nn<br />
Notes: $notes nn<br />
<br />
Inst1_ID: $instructor nn<br />
Inst2_ID: $instructor2 nn<br />
Student_Email: $stuemail nn
</body>
</html>
";

//If you have any date values within your update form, you will need to use the code below
//to convert the dates to something the MSSQL Server will understand.
//I have 2 Date fields in my grid Update Form.

if(($begins =='NULL')){
$begins = 'NULL';
}else{$begins = "CONVERT(DATETIME, '".$begins."')";}

if(($dennied_date =='NULL')){
$dennied_date = 'NULL';
}else{$dennied_date = "CONVERT(DATETIME, '".$dennied_date."')";}

// This is the PHPMail code what actually sends out the email you created above.

$headers = 'MIME-Version: 1.0' . "rn";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "rn";
$headers .= "From:" . $from;
mail($to,$subject,$message,$headers);

//This section is where you can update a SQL table.

$query2 = "UPDATE dbo.[Request] SET
Request_Approved = ".$begins.",
Request_Denied = ".$dennied_date.",
fk_Approved_Location = '".$location."',
Req_Notes = '".$notes."'
WHERE (Request_ID = '".$request_ID."')";

$result = sqlsrv_query( $conn2, $query2);
if( $result === false ) {
die( print_r( sqlsrv_errors(), true)
);

}

sqlsrv_close($conn2);
};

I hope that this can help you with mail when you Update a Colum from your PHP Grid.

1 Answers
Abu Ghufran answered 4 years ago

Thanks for sharing

Your Answer

4 + 9 =

Login with your Social Id:

OR, enter

Attach code here and paste link in question.
Attach screenshot here and paste link in question.



How useful was this discussion?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate it.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?