Hi,
I am pretty new to MySQL and need help in setting up my script to write into a database. I wrote a simple alert message script that allows users to enter data into a form, which is then parsed into an email and sent to a distribution list. What I want to achieve now is:
- have the form data parsed into both the email body but also into a MySQL database
- add a link to the bottom of my email message that allows the user to change the status of the case from OPEN to CLOSED ($case_status from “no” to “yes”)
My code below:
<html>
<head>
<title>DADHA - Guest Problem Alert</title>
<link rel="shortcut icon" href="./images/favicon.ico">
<meta name="viewport" content="width=device-width, user-scalable=no">
</head>
<body>
<?php
require("../PHPMailer-master/class.phpmailer.php"); // load the PHPmailer script
date_default_timezone_set('Etc/UTC'); // set timezone in PHP.ini,required by SMTP
ini_set('display_errors', 'On'); // show all errors
error_reporting(E_ALL);
// VARIABLES HANDED OVER FROM GPA FORM
$staff_name = $_POST['staff_name']; // required
$staff_department = $_POST['staff_department']; // not required
$problem = $_POST['problem']; // required
$priority = $_POST['priority']; // not required
$gst_name = $_POST['gst_name']; // not required
$gst_room = $_POST['gst_room']; // not required
$incident = $_POST['incident']; // required
$action = $_POST['action']; // not required
$followup = $_POST['followup']; // not required
$gst_temp = $_POST['gst_temp']; // not required
$case_status = $_POST['case_status']; // required
$gst_history = $_POST['gst_history']; // not required
$time = date("Y-m-d H:i:s", strtotime('+7 hours')); // Add 7 hours to time
$email_message = "<img src='cid:ic_logo' width='400' height='66' alt='IC Logo' /><br/>
<p>A guest problem alert has been raised for your property, please see details below:</p>
<table>
<tr><th>Item</th>
<th>Details</th></tr>
<tr><td>Date & Time</td><td>$time</td></tr>
<tr><td>Staff Name</td><td>$staff_name</td></tr>
<tr><td>Staff Department</td><td>$staff_department</td></tr>
<tr><td>Problem</td><td>$problem</td></tr>
<tr><td>Priority</td><td>$priority</td></tr>
<tr><td>Guest Name</td><td>$gst_name</td></tr>
<tr><td>Guest Room</td><td>$gst_room</td></tr>
<tr><td>Incident</td><td>$incident</td></tr>
<tr><td>Action</td><td>$action</td></tr>
<tr><td>Follow up</td><td>$followup</td></tr>
<tr><td>Guest Manner</td><td>$gst_temp</td></tr>
<tr><td>Case closed?</td><td><b>$case_status</b></td></tr>
<tr><td>Guest history update by:</td><td>$gst_history</td></tr>
</table>
<p><b>All OPEN CASES must be follow up within 1 hour from now</b>. Please consult with the Resort Assistant Manager on-duty for more details.<br/>
<br/><br/>
Kind regards";
if ($staff_name == "" OR $problem == "" OR $incident == "")
{
echo "<img src=\"../../images/iclogo_small.jpg\" style=\"width: 300px\"><br/><font size=\"3\"><b><u>ERROR</u></b><br/></font>
You did not enter a valid Staff Name, Guest Problem or Incident Message.<br/><br/>Please <a href=\"javascript:history.go(-1)\">[Go Back]</a> and fix this.<br/><br />
<input action=\"action\" type=\"button\" value=\"Back\" onclick=\"history.go(-1);\" />";
exit;
}
if ( isset($staff_name) && isset($problem) && isset($incident) && isset($case_status) )
{
$data = $time . '|' . $staff_name . '|' . $staff_department . '|' . $problem . '|' . $priority . '|' . $gst_name . '|' . $gst_room . '|' . $incident . '|' . $action . '|' . $followup . '|' . $gst_temp . '|' . $case_status . '|' . $gst_history . "\n";
$ret = file_put_contents('GPAdata.txt', $data, FILE_APPEND | LOCK_EX);
if ($ret === false)
{
die('There was an error writing this file');
}
}
// PHPMAILER START
$mail = new PHPMailer();
$mail->isSMTP(); // Set connection type to SMTP
$mail->SMTPDebug = 0; // Enable SMTP debugging, 0 = off (for production use), 1 = client messages, 2 = client and server messages
$mail->Debugoutput = 'html'; // Set the output format for SMTP Debug
$mail->Host = "mail.server.com"; // Mail Host Server
$mail->Port = 25; // SMTP Port (usually 25)
$mail->SMTPAuth = true; // Authentication needed? true/false
$mail->Username = "[email protected]"; // Login Username
$mail->Password = "password"; // Login Password
$mail->setFrom('[email protected]', 'My Name'); // Sent FROM
//$mail->addReplyTo('[email protected]', 'Not Needed'); // Add REPLY-TO address
$mail->addAddress('[email protected]', 'My Name'); // Send TO
$mail->Subject = 'Guest Problem Alert';
$mail->Body = $email_message;
$mail->isHTML(TRUE); // HTML body? true/false
$mail->AddEmbeddedImage('../../images/iclogo_small2.jpg', 'ic_logo');
//$mail->msgHTML("<table><tr><th>cat1</th><th>cat2</th></tr><td>Date & Time</td><td>".$time_GMT11."</td></table>"); // Message body - HTML
//$mail->body = $email_message; // Message body - regular
//$mail->AltBody = $email_message; // Message body - plain text
//$mail->addAttachment('../../images/iclogo_small.jpg'); // Attachment if necessary
//send the message, check for errors
if (!$mail->send())
{
echo "Mailer Error: " . $mail->ErrorInfo;
}
else {
echo "<img src=\"../../images/iclogo_small2.jpg\" style=\"width: 300px\"><br/><font size=\"3\"><b><u>MESSAGE SENT!</u></b><br/></font>
Thank you very much for submitting the Guest Problem Alert. All OPEN CASES must be follow up within 1 hour, please consult with the Resort Assistant Manager on-duty for further follow up.<br/>
<br/>
<input action=\"action\" type=\"button\" value=\"Back\" onclick=\"history.go(-1);\" />";
}
?>I have already created an empty database named “b24_15096338_GPA” but now am struggling on getting the data to be written in there.
If SQL is to bothersome, is there maybe a way to write the data into my .txt file instead and then have an email link to change the $case_status variable in there?
Thanks for the advice!
A2k