0 Replies - 2078 Views - Last Post: 21 June 2013 - 08:26 AM

#1 vicfws  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 21-June 13

SQL Question - How to use data from two tables in email form

Posted 21 June 2013 - 08:26 AM

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'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 INTO comments (bmail)
SELECT EMAIL FROM LISTINGS
WHERE sku='1';



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'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.

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>



and 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\">";
}
?>




Is This A Good Question/Topic? 0
  • +

Page 1 of 1