PHP and MySQL Issue

**Wahoo92583** · Aug 17, 2011 06:02

Hello,
I am a little stumped on how to achieve my goal. I have a script that queries a MySQL database. Here is the SQL for my query:

Code:

$sql = "SELECT first_name, last_name, email_address FROM clerks WHERE 1 ORDER BY last_name, first_name";

Upon querying the database, the information is used to populate a HTML drop down menu with the subjects' first name and last name, on a HTML email form. Upon the user clicking the send e-mail button, an HTML email is sent. The issue I am encountering is, how would I create a FROM: header with the correct person's information.

Here is my HTML form:

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title></title>
<link rel="stylesheet" type="text/css" href="statute.css" />
</head>
<body>

<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post" name="">

<fieldset>
<legend>Email | Missing Statute Information:</legend>
<font color="#043c68" face="Arial, Helvetica, sans-serif"><b>There is not a matching statute and charge description for the charge you entered: </font><font color="#990000" face="Arial, Helvetica, sans-serif">"<?php echo htmlspecialchars($value, ENT_QUOTES, 'UTF-8'); ?>"</font><font color="#043c68" face="Arial, Helvetica, sans-serif">.<br /> Please use &quot;Florida Statute Violation&quot; and also, please use the form below to email Sue Owens.</b></font><br /><br />

<div><label>Please select your name:</label>
<select style="width: 300px" name="full_name" id="full_name">
<?php while($row = mysql_fetch_array($result)){
?>
<option value="<?php echo  $row['first_name'] . " " . $row['last_name']; ?>" ><?php echo $row['last_name'] . ", ". $row['first_name']; ?></option>
<?php
}
?>
</select>
</div><br />

<div><label for="missingStatute">Missing Statute (i.e. "784.03"):</label>
<input type="text" class="width" name="missingStatute" id="missingStatute" /></div><br />

<div><label for="chargeTitle">Charge Title (i.e. "Domestic Battery"):</label>
<input type="text" class="width" name="chargeTitle" id="chargeTitle" /></div><br />

<div><label for="chargeDesc">Charge Description<br /> (What does the charge pertain to?):</label>
<textarea name="chargeDesc" id="chargeDesc"></textarea></div><br />
<input type="hidden" name="statute" value="<?php echo htmlspecialchars($value, ENT_QUOTES, 'UTF-8'); ?>">
<div><input type="submit" name="send_email" class="send_email" value="Send Email" /></div>

</fieldset>

</form>


</body>
</html>

Here is the code for my HTML email:

Code:

<?php
$mailto = "";
$mail_subject = "Hi, " . $clerk_name . " Found A Missing Charge.";
$headers  = 'From:' . "\r\n";
$headers .= 'Reply-To: ' . "\r\n";
$headers .= 'CC:' . "\r\n";
$headers = "MIME-Version: 1.0" . "\r\n";
$headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n";
$message = '
<html>
<head>
<title>Missing Charges</title>
<style type="text/css">
<!-- 
br{
	clear: left;
}

font{
	font-family:Arial, Helvetica, sans-serif;
}

td{
	font-family:Arial, Helvetica, sans-serif;
}

th{
	color:#3f8fd2;
	font-family:Arial, Helvetica, sans-serif;
}

tr:nth-child(even){
	background:#66a1d2;
}

tr:nth-child(odd){
	background:#ffffff;
}

-->
</style>
</head>
<body> 
<table width="492" border="1" bordercolor="#000000">
  <tr>
    <td>A.R.M.S. Clerk\'s Name:</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>Missing Statute:</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>Charge Name:</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>Charge Description:</td>
    <td>&nbsp;</td>
  </tr>
</table>
</body>
</html>

';
mail($mailto, $mail_subject, $message, $headers);
?>

**Salathe** · Aug 17, 2011 07:00

Just add the appropriate values into the $headers string, as you have with $clerk_name for the $mail_subject string. We don't have enough information to tell you exactly which variables/values to use.

**StarLion** · Aug 17, 2011 07:20

S'a poor choice for select value. What's the key on the table?

**Wahoo92583** · Aug 17, 2011 07:28

I do not know what you mean by "the key" on the table. Sorry. I am relatively new to PHP and HTML.

**StarLion** · Aug 17, 2011 07:31

Can you give us the full structure of the database table?

**Wahoo92583** · Aug 17, 2011 07:49

Sure. The table is structured as follows: id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, first_name, last_name, and email_address are all varchar(100).

**StarLion** · Aug 17, 2011 07:50

retrieve id as well in your query. Use that as the value in the options. Then when the form is submitted, use the value submitted to look up the individual's email.

PS: See where it says in your schema "PRIMARY KEY"? That's what I was referring to as 'the key on the table'.

**Wahoo92583** · Aug 17, 2011 07:59

I am pretty green to this entire thing. How do I do this? I do not want to echo the id out and how would I retrieve the email address associated with that id and insert it into the headers of the html file? Should I have another mysql query which queries the database using the id, such as SELECT email_address FROM clerks WHERE id == 1; ?!?!

**StarLion** · Aug 17, 2011 08:04

Code:

$sql = "SELECT first_name, last_name, email_address FROM clerks WHERE 1 ORDER BY last_name, first_name";

becomes

Code:

$sql = "SELECT id,first_name, last_name, email_address FROM clerks WHERE 1 ORDER BY last_name, first_name";

,

Code:

<option value="<?php echo  $row['first_name'] . " " . $row['last_name']; ?>" ><?php echo $row['last_name'] . ", ". $row['first_name']; ?></option>

becomes

Code:

<option value="<?php echo  $row['id']; ?>" ><?php echo $row['last_name'] . ", ". $row['first_name']; ?></option>

inside your handler you'll need another query that queries:

Code:

$sql = "SELECT email_address FROM clerks WHERE id = ".$_POST['full_name'];

(You may need/want to select firstname and lastname there too)

**Wahoo92583** · Aug 17, 2011 08:08

What does my headers become?

**StarLion** · Aug 17, 2011 08:11

Well, however you retrieve the query will define that - if you follow the previous example, then $row['email_address'] will hold your from address.

User Tag List

Thread: PHP and MySQL Issue

Thread Tools

Display

PHP and MySQL Issue

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions