Sending mail with info from database

climberusa

Can anyone explain the best way to retireive info from mysql and then send it in an email? I'm completely lost on this. I can run a query of the database just fine, and I know how to use the mail function. The problem is I cannot figure out how to use them together. Anyone?

leatherback

You spit the info from the database into a variale:

while($row=mysql_fetch_array($result))
{
$output .= $row[0];
}

and you use the variable in the mail function as message.

climberusa

so simple...i think i'm going to cry

leatherback

Need a shoulder to cry on? We PhPbuilders are having a little dinner party.. wanna join

climberusa

ok, well i really may cry, but i already ate.

So I thought I had it figured out but alas, i still haven't got it. So far I have this for my code:

<?php
$result = mysql_query("SELECT * FROM tickets WHERE user_id = 4 ORDER by ticket_from"); 

?>
<table border='0' width='80%' cellpadding='0' cellspacing='0'>
 <tr>
   <td width='35%' bgcolor="#CECECE" align='left'><b>From</b></td>
   <td width='35%' bgcolor="#CECECE" align='left'><b>To</b></td>
   <td width='30%' bgcolor="#CECECE" align='center'><b>Price</b></td>

 </tr> 
<?php
while($row=mysql_fetch_array($result))
{
$from    = $row[ticket_from];
$to      = $row[ticket_to];
$price   = $row[ticket_price];
$num++;
  if ($c == "#FFFFFF") { $c = "#F3F3F3"; } else { $c = "#FFFFFF"; }//alternates the row color in the table

?>
<tr>
   <td width='35%' bgcolor='<? echo $c ?>' align='left'><? echo $from; ?></td>
   <td width='35%' bgcolor='<? echo $c ?>' align='left'><? echo $to; ?></td>
   <td width='30%' bgcolor='<? echo $c ?>' align='center'><? echo $price; ?></td>
</tr>
<?php
} 
echo "</table><p>&nbsp;</p>";
?>

This gives me my data in a useable format but how do I email it?

leatherback

mail($to, $subject, $message_body, $headers);

climberusa

Excuse me for my lack of understanding... 😕
I guess where I'm stuck is how to turn what I have into a variable that I can reference as $message_body. My first attempt had me trying to turn the code I pasted into a variable itself, but that proved wrong.

climberusa

you spit the info from the database into a variale:

while($row=mysql_fetch_array($result))
{
$output .= $row[0];
}

and you use the variable in the mail function as message.

What if I have more than one variable?

while($row = mysql_fetch_array($sql)){ 
  $ticket_from =   	$row[\'ticket_from\'];
  $ticket_to =   	$row[\'ticket_to\'];
  $ticket_price =  	$row[\'ticket_price\'];

And each variable, which is the data from a row in my database, can have any number of items. How would I reference this in my mail function?
Basically I need the results of my query to be input into a table, which I already have, and then emailed the user. Am I making sense?

leatherback

As I said. you put everything into a variable.

So if you have n1, 2, 12, or 1000 items to send, you create the items, and put them together:

$mail_message = $var1.$var2.$vari;

wilku

I would use this (it's got a name for it, but I forget it all the time):

<?php
//do all your mysql thing

//now the output
$output = <<<END
<table><tr>
<td>Print here all the data you need, just like you do in the script above.</td>
<td>Instead of echoing your varibles, just write them lik $row[ticket_from] or $ticket_from. </td>
</tr></table>
END; //this must start from the first character in line.

//now you can send $output in a mail
?>

climberusa

Ok, well I appreciate all of your help so far. I am obviously new to php and mysql and I've been reading two books trying to work my way through this. understand how to use the mail funstion and how to place info from a database into a variable for email or whatever. This is what I've got so far:

<?php
require_once ('mysql_connect.php');
$query = "SELECT * FROM tickets WHERE user_id = 4 ORDER by ticket_from"; //query the database
$result = @mysql_query ($query);
if ($result) {//if everything is ok...

echo '<h4><font color="#003396">Current Fares for $company_name</font></h4>
<p class=\"alert\">***Fares are one-way non directional, and include all taxes and surcharges.***</p>
<table border="0" width="100%" cellpadding="0" cellspacing="0">
 <tr>
   <td width="18%" bgcolor="#CECECE" align="left"><b>From</b></td>
   <td width="18%" bgcolor="#CECECE" align="left"><b>To</b></td>
   <td width="18%" bgcolor="#CECECE" align="center"><b>Price</b></td>
   <td width="10%" bgcolor="#CECECE">&nbsp;</td>
 </tr> ';

 while($row = mysql_fetch_array($result, MYSQL_NUM)){//define variables
  $ticket_from =    $row[3];
  $ticket_to =   	$row[4];
  $ticket_price =  	$row[5];
  $num++;
  if ($c == "#FFFFFF") { $c = "#F3F3F3"; } else { $c = "#FFFFFF"; }//alternates table row colors

echo " <tr>
   <td width='18%' bgcolor='$c' align='left'>$ticket_from</td>
   <td width='18%' bgcolor='$c' align='left'>$ticket_to</td>
   <td width='18%' bgcolor='$c' align='center'>$ticket_price</td>
 </tr>\n";
 }  

	echo '</table>';
	mysql_free_result ($result);

} else {
	echo '<p>The info could not be displayed due to a system error.</p><p>' . mysql_error() . '</p>';
}
mysql_close();
?>

Where I'm stuck is how to get that information into a variable for email. The script currenty populates the table with as many tickets as there are in the database, which is what I want. So I'm not sure if this is how I need to pull the info from my DB or if there is a better way to do this.

As I said. you put everything into a variable.

So if you have n1, 2, 12, or 1000 items to send, you create the items, and put them together:

$mail_message = $var1.$var2.$vari;

How would this work with my code so far?

Thanks for all of your help so far. I feel like the answer is staring me in the face.

wilku

Well, try this (I don't like giving answers "on a plate" though):

<?php
require_once ('mysql_connect.php');
$query = "SELECT * FROM tickets WHERE user_id = 4 ORDER by ticket_from"; //query the database
$result = @mysql_query ($query);
$output="";
if ($result) {//if everything is ok...

$output.=<<<END
<h4><font color="#003396">Current Fares for $company_name</font></h4>
<p class=\"alert\">***Fares are one-way non directional, and include all taxes and surcharges.***</p>
<table border="0" width="100%" cellpadding="0" cellspacing="0">
<tr>
   <td width="18%" bgcolor="#CECECE" align="left"><b>From</b></td>
   <td width="18%" bgcolor="#CECECE" align="left"><b>To</b></td>
   <td width="18%" bgcolor="#CECECE" align="center"><b>Price</b></td>
   <td width="10%" bgcolor="#CECECE">&nbsp;</td>
</tr> 
END;

 while($row = mysql_fetch_array($result, MYSQL_NUM)){//define variables
  $ticket_from =    $row[3];
  $ticket_to =       $row[4];
  $ticket_price =      $row[5];
  $num++;
  if ($c == "#FFFFFF") { $c = "#F3F3F3"; } else { $c = "#FFFFFF"; }//alternates table row colors

$output.=<<<END
<tr>
   <td width='18%' bgcolor='$c' align='left'>$ticket_from</td>
   <td width='18%' bgcolor='$c' align='left'>$ticket_to</td>
   <td width='18%' bgcolor='$c' align='center'>$ticket_price</td>
</tr>\n
END;
 }  

    $output.='</table>';
    mysql_free_result ($result);

} else {
   $output.='<p>The info could not be displayed due to a system error.</p><p>' . mysql_error() . '</p>';
}
mysql_close();

//now send the email with $output as body
mail(...,$output,...);//I don't remember what arguments are needed, but you said you know this
?>

climberusa

Thanks Wilku! I don't like getting answers and not understanding them but I appreciate your help. this did work for me so now it's just a matter of reading the code and understanding what exactly and why it works. Thanks!