Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Email Results of a Query 1

Status
Not open for further replies.
likelylad

likelylad

IS-IT--Management
Jul 4, 2002
388
GB
I have a simple query that I need to email the results to particular people.

select * from data where username="mmouse"
The results can be a number of lines.
I have output the results to a table and I know it works fine.

The mail function also works for me using the following.
$to = "mmouse@disney.com";
$subject = "I'm going quackers";
$Msg = "quack quack quack";
$from = "dduck@disney.com";
mail($to, $subject, $Msg, "From: $from\r\n");
The 'mail to' address will be a variable but I will work on this another time.

So the problem I have is how to marry the 2.

Thanking in advance for any help received
 
Sort by date Sort by votes
Simply build up the $Msg string in the recordset loop until it contains all the data you need. (in same the way you outputted the table). You can add line breaks by adding vbcrlf such as:

While...

$msg = $msg & rs("Data") & vbcrlf

Loop Nick (Software Developer)


nick@retrographics.fsnet.co.uk
nick.price@myenable.com
 
Upvote 0 Downvote
This is the code I have so far (I have excluded the rs part as it declares it an undefined function).The email sent is blank.

$result = query etc....;

if ($myrow = mysql_fetch_array($result)) {

do {
$upsellingagent=$myrow["Upselling_Agent"];
$ordernumber=$myrow["Order_Number"];
$originalqty=$myrow["Original_Qty"];
$currency=$myrow["Currency"];
$upsoldqty=$myrow["Upsold_Qty"];
$upsoldvalue=$myrow["Upsold_Value"];
$recorddate=$myrow["Record_Date"];

} while ($myrow = mysql_fetch_array($result));

$Msg = $Msg & $upsellingagent & $ordernumber & etc;

} else {

echo "Sorry, no records were found!";

}

$to = "mmouse@disney.com";
$subject = "I'm going quackers";
$from = "dduck@disney.com";
$headers = "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
$headers .= &quot;From: Information <dduck@disney.com>\r\n&quot;;
mail($to, $subject, $Msg, $headers);


 
Upvote 0 Downvote
If I might suggest, try this... Note I haven't tested it.

Code: 
if (mysql_num_rows($result) > 0) {
  $msg=&quot;&quot;;
  while ($myrow = mysql_fetch_array($result)) {
    $msg.=$myrow[&quot;Upselling_Agent&quot;].&quot; &quot;;
    $msg.=$myrow[&quot;Order_Number&quot;].&quot; &quot;;
    $msg.=$myrow[&quot;Original_Qty&quot;].&quot; &quot;;
    $msg.=$myrow[&quot;Currency&quot;].&quot; &quot;;
    $msg.=$myrow[&quot;Upsold_Qty&quot;].&quot; &quot;;
    $msg.=$myrow[&quot;Upsold_Value&quot;].&quot; &quot;;
    $msg.=$myrow[&quot;Record_Date&quot;].&quot;\r\n&quot;;
  }
  $to = &quot;mmouse@disney.com&quot;;
  $subject = &quot;I'm going quackers&quot;;
  $from = &quot;dduck@disney.com&quot;;
  $headers  = &quot;MIME-Version: 1.0\r\n&quot;;
  $headers .= &quot;Content-type: text/html; charset=iso-8859-1\r\n&quot;;
  $headers .= &quot;From: Information <dduck@disney.com>\r\n&quot;;
  mail($to, $subject, $Msg, $headers);
} else {
  echo &quot;No records found&quot;;
}
--
How can you be in two places at once when you're not anywhere at all?
 
Upvote 0 Downvote
Hi mweinstock

The code you provided sends me a blank email.

However I altered my original code and I can get one record emailed (which turns out to be the last record in the query).

if ($myrow = mysql_fetch_array($result)) {

do {
$upsellingagent=$myrow[&quot;Upselling_Agent&quot;];
$ordernumber=$myrow[&quot;Order_Number&quot;];
$originalqty=$myrow[&quot;Original_Qty&quot;];
$currency=$myrow[&quot;Currency&quot;];
$upsoldqty=$myrow[&quot;Upsold_Qty&quot;];
$upsoldvalue=$myrow[&quot;Upsold_Value&quot;];
$recorddate=$myrow[&quot;Record_Date&quot;];

} while ($myrow = mysql_fetch_array($result));

$Msg = $upsellingagent;
$Msg .=&quot; &quot;;
$Msg .= $ordernumber;
$Msg .=&quot; &quot;;
$Msg .= $originalqty;
$Msg .=&quot; &quot;;
$Msg .= $currency;
$Msg .=&quot; &quot;;
$Msg .= $upsoldqty;
$Msg .=&quot; &quot;;
$Msg .= $upsoldvalue;
$Msg .=&quot; &quot;;
$Msg .= $recorddate;

} else {

echo &quot;Sorry, no records were found!&quot;;

}

$to = &quot;mmouse@disney.com&quot;;
$subject = &quot;I'm going quackers&quot;;
$from = &quot;dduck@disney.com&quot;;
$headers = &quot;MIME-Version: 1.0\r\n&quot;;
$headers .= &quot;Content-type: text/html; charset=iso-8859-1\r\n&quot;;
$headers .= &quot;From: Information <dduck@disney.com>\r\n&quot;;
mail($to, $subject, $Msg, $headers);


 
Upvote 0 Downvote
You are building the message Outside of the loop.... So the last record is exactly what you should expect.


I would just add a string of - or something with

$Msg = $upsellingagent;
$Msg .=&quot; &quot;;
$Msg .= $ordernumber;
$Msg .=&quot; &quot;;
$Msg .= $originalqty;
$Msg .=&quot; &quot;;
$Msg .= $currency;
$Msg .=&quot; &quot;;
$Msg .= $upsoldqty;
$Msg .=&quot; &quot;;
$Msg .= $upsoldvalue;
$Msg .=&quot; &quot;;
$Msg .= $recorddate;
$Msg .=&quot;---------------------------------------\n&quot;;

Then move the whole section up into the While loop.
 
Upvote 0 Downvote
Hi blanius

I changed it to the following and I still only received one record

if ($myrow = mysql_fetch_array($result)) {

do {
$upsellingagent=$myrow[&quot;Upselling_Agent&quot;];
$ordernumber=$myrow[&quot;Order_Number&quot;];
$originalqty=$myrow[&quot;Original_Qty&quot;];
$currency=$myrow[&quot;Currency&quot;];
$upsoldqty=$myrow[&quot;Upsold_Qty&quot;];
$upsoldvalue=$myrow[&quot;Upsold_Value&quot;];
$recorddate=$myrow[&quot;Record_Date&quot;];

$Msg = $upsellingagent;
$Msg .=&quot; &quot;;
$Msg .= $ordernumber;
$Msg .=&quot; &quot;;
$Msg .= $originalqty;
$Msg .=&quot; &quot;;
$Msg .= $currency;
$Msg .=&quot; &quot;;
$Msg .= $upsoldqty;
$Msg .=&quot; &quot;;
$Msg .= $upsoldvalue;
$Msg .=&quot; &quot;;
$Msg .= $recorddate;
$Msg .=&quot;---------------------------------------\n&quot;;

} while ($myrow = mysql_fetch_array($result));



} else {

echo &quot;Sorry, no records were found!&quot;;

}
 
Upvote 0 Downvote
Got it at last!!!!!!!!!
blanius you got me thinking about the way the information was being processed. Your a star!!!!!
I have highlighted the changes from the code posted above


if ($myrow = mysql_fetch_array($result)) {
$Msg = &quot;&quot;;
do {
$upsellingagent=$myrow[&quot;Upselling_Agent&quot;];
$ordernumber=$myrow[&quot;Order_Number&quot;];
$originalqty=$myrow[&quot;Original_Qty&quot;];
$currency=$myrow[&quot;Currency&quot;];
$upsoldqty=$myrow[&quot;Upsold_Qty&quot;];
$upsoldvalue=$myrow[&quot;Upsold_Value&quot;];
$recorddate=$myrow[&quot;Record_Date&quot;];

$Msg .= $upsellingagent;
$Msg .=&quot; &quot;;
$Msg .= $ordernumber;
$Msg .=&quot; &quot;;
$Msg .= $originalqty;
$Msg .=&quot; &quot;;
$Msg .= $currency;
$Msg .=&quot; &quot;;
$Msg .= $upsoldqty;
$Msg .=&quot; &quot;;
$Msg .= $upsoldvalue;
$Msg .=&quot; &quot;;
$Msg .= $recorddate;
$Msg .=&quot;---------------------------------------\n&quot;;

} while ($myrow = mysql_fetch_array($result));



} else {

echo &quot;Sorry, no records were found!&quot;;

}
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

R
  • Question
How Can I Use MySQL To Send Data To An Email
Replies
1
Views
266
Chris Miller
Chris Miller
EdLentz
  • Locked
  • Question
Partial link in email
Replies
1
Views
209
spamjim
spamjim
EdLentz
  • Locked
  • Question
I need a simple example of how to send a table of info .
Replies
8
Views
427
EdLentz
EdLentz
P
  • Locked
  • Question
JPgraph - How to stop a line graph from dropping to zero?
Replies
2
Views
378
Chris Miller
Chris Miller
ersatz
  • Locked
  • Question
How to send 2 select from mysql database in email like tables
Replies
5
Views
477
ersatz
ersatz

Part and Inventory Search

Sponsor

Back
Top