PDA

View Full Version : How do I use data from two tables in email form?



vicfws
06-22-2013, 03:55 PM
I have two tables in my database, LISTINGS and comments which are linked by a 'sku' field, both collecting information from different sources.

When someone leaves a comment I need to send an email to that person via the email address submitted in 'comments' under the field 'email' but I also need to send a copy to the person the comment is about, which has been previously saved under 'EMAIL' in the LISTINGS table, which I want to copy into the 'bmail' field of the 'comments' table (I'm assuming I have to copy it to the comments table to send the email to them)

I've tried JOIN and INSERT, but I'm fairly new to this and I can't seem to get the data to copy over when the form is submitted.

I've worked out that


$insert = mysql_query("INSERT INTO comments (bmail)
SELECT EMAIL FROM LISTINGS
WHERE sku='3';");

copies over the info I want but it creates a new row when it does.

I'm using a simple mail.php set up where form.php features the main form and collects the comment info, which then goes to add.php to insert it into the database.

As I've not been doing this long I'm not really familiar with how to transfer a SQL query into PHP, or whether it should go in either the add.php or form.php file. I might have missed something somewhere.

I've been through numerous posts and tutorials but can't figure it out. I'm not getting any errors or anything and the person leaving the comment receives the email, but not the person who the comment is for as obviously I don't know how to get the form to draw this from the LISTINGS table.

Any assistance would be appreciated. Maybe there is a better method?

Thank you.

Here is my form.php




<?php
// Stores the sku as a session
$_SESSION['sku'] = $_GET['sku'];

echo "<form name='comment' method='post' action='add.php?sku=".$_SESSION['sku']."'>";
?>
<table width="530" border="0">
<tr>
<td><span class="style1">Name: </span></td>
<td><input type="text" name="name" maxlength="20" /></td>
</tr>
<tr>
<td><span class="style1">Email (Hidden): </span></td>
<td><input type="text" name="email" maxlength="40" /></td>
</tr>
<tr>
<td><span class="style1">Title: </span></td>
<td><input size="51" maxlength="40" type="text" name="title" value=""/></td>
</tr>
<tr>
<td><span class="style1">Rating: </span></td>
<td>
<select name="rating">
<option value="1">1 star</option>
<option value="2">2 stars</option>
<option value="3">3 stars</option>
<option value="4">4 stars</option>
<option value="5">5 stars</option>
</select>
</td>
</tr>
<tr>
<td><span class="style1">Pros: </span></td>
<td><textarea name="pros" cols="57" rows="6"></textarea></td>
</tr>
<tr>
<td><span class="style1">Cons: </span></td>
<td><textarea name="cons" cols="57" rows="6"></textarea></td>
</tr>
<tr>
<td><span class="style1">Comment: </span></td>
<td><textarea name="comment" cols="57" rows="6"></textarea></td>
</tr>
<tr>
<td></td>
<td>
<input type="submit" value="Submit Comment" />
</td>
</tr>
</table>
</form>


Here is the add.php


<?php
$sku = $_SESSION['sku'];
$name = $_POST['name'];
$title = $_POST['title'];
$rating = $_POST['rating'];
$comment = $_POST['comment'];
$pros = $_POST['pros'];
$cons = $_POST['cons'];
$email = $_POST['email'];
$bmail = $_POST['bmail'];



if(!empty($name) && !empty($title) && !empty($rating) && !empty($comment))
{

$time = date("Y-m-d H:i:s");

$comment = $_POST['comment'];

// Adds commet to the database
$insert = mysql_query("INSERT INTO $tablecomments(sku, name, email, title, rating, time, comment,pros,cons) VALUES('".$_SESSION['sku']."', '$name', '$email','$title', '$rating', '$time','$comment','$pros','$cons')");

if($insert)
{
echo "<p class='green'>Success</p>";
;
echo "<p>Please wait while we redirect you to the main page...</p>";

// Redirects user to the main page after 5 seconds
echo "<meta http-equiv='refresh' content='0;url=index.php?sku=".$_SESSION['sku']."' />";
mysql_close($con);
}
else
{
echo "<p class='red'>Error</p>";
echo "<p>Inserting review into mysql database was unsuccessful. <a href='addreview.php?sku=".$_SESSION['sku']."'>Click here to go back and try again.</p>";
}
}
// Returns error if user hasn't entered the required fields
else
{
echo "<p class='red'>Error</p>";
echo "<p>";

if(empty($_POST['name']))
echo "Name field is empty.<br>";

if(empty($_POST['reviewtitle']))
echo "Review title field is empty.<br>";

if(empty($_POST['rating']))
echo "Please rate the product.<br>";

if(empty($_POST['comment']))
echo "Comment field is empty.<br>";

echo "<a href='addreview.php?sku=".$_SESSION['sku']."'>Click here to go back and try again.</a>";
echo "</p>";
}

?>



<?php

$EmailFrom = "";
$EmailTo = $_POST['email']",";
$EmailTo .= $_POST['bmail'].",";


$Subject = "Comment received";
$name = Trim(stripslashes($_POST['name']));
$email = Trim(stripslashes($_POST['email']));
$comment = Trim(stripslashes($_POST['comment']));
$title = Trim(stripslashes($_POST['title']));
$pros = Trim(stripslashes($_POST['pros']));
$cons = Trim(stripslashes($_POST['cons']));
$rating = Trim(stripslashes($_POST['rating']));
$sku = $_SESSION['sku'];
$bmail = $_POST['bmail'];
// validation
$validationOK=true;
if (!$validationOK) {
print "<meta http-equiv=\"refresh\" content=\"0;URL=error.php\">";
exit;
}

// prepare email body text
$Body = "Comment received: ";
$Body .= $sku;
$Body .= "\n";
$Body .= "\n";
$Body .= "Name: ";
$Body .= $name;
$Body .= "\n";
$Body .= "Email: ";
$Body .= $email;
$Body .= "\n";
$Body .= "Rating: ";
$Body .= $rating ;
$Body .= " out of 5";
$Body .= "\n";
$Body .= "Feedback:";

$Body .= "\n";
$Body .= "Title:";
$Body .= " ";
$Body .= $title;
$Body .= "\n";


$Body .= "Pros:";
$Body .= " ";
$Body .= $pros;
$Body .= "\n";

$Body .= "Cons:";
$Body .= " ";
$Body .= $cons;
$Body .= "\n";
$Body .= "Comments:";
$Body .= " ";
$Body .= $comment;



// send email
$success = mail($EmailTo, $Subject, $Body, "From: xxxxxxxxxxxx");

// redirect to success page
if ($success){
print "<meta http-equiv=\"refresh\" content=\"0;URL=contactthanks.php\">";
}
else{
print "<meta http-equiv=\"refresh\" content=\"0;URL=error.php\">";
}
?>