PHP and MySQL Issue

PHP
1

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:

$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:

<!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:

<?php
$mailto = "";
$mail_subject = "Hi, " . $clerk_name . " Found A Missing Charge.";
$headers  = 'From:' . "\\r\
";
$headers .= 'Reply-To: ' . "\\r\
";
$headers .= 'CC:' . "\\r\
";
$headers = "MIME-Version: 1.0" . "\\r\
";
$headers .= "Content-type:text/html;charset=iso-8859-1" . "\\r\
";
$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);
?>
2

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.

3

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

4

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

5

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

6

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

7

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

8

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; ?!?!

9 

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

becomes


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

,


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

becomes


<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:


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

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

10

What does my headers become?

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.