SQL query --> email multiple results...help plz?

[MikeWire]

Hi all - looks like this is a great forum.

I need a little help here...I have an SQL query and I am making arrays and I need to email the results. The problem here is that I have multiple rows returned, which results in multiple arrays, but only the last array gets sent via email to the user. Everything works but I can't for the life of me figure out how to store multiple results from the SQL query and push that into an email.

Here's some code:

$sql1 = "This is my query";//changed to protect the innocent ;)
$query = mssql_query($sql1)
		or die("<br>I couldn't run the  query: ".$sql1."<br><br><a href=\"../index.php\">Back</a>");	


echo "<ul>";//Add a little formatting...
//While the query returns results echo out each incident
while ($result = mssql_fetch_array($query, MSSQL_ASSOC)) {  		

	foreach ($result as $key => $value) {//echo each key->value for each row found
		echo $key.": ".$value."\n<br>";
		}

}//End While

echo "</ul>";

//obviously this is fake info filled out for this post
       $to = "[e-mail]";
	$subject = "Batch Report";
	$message = "\n";//info from above query results
	$headers = "From: [e-mail]";
	//If there are any results for today mail it to the below
	//$send = "";//TEST
	$send = mail($to, $subject, $message, $headers);

if(!$send) { echo "<br>Did not send";}
else { echo "<br>Mail sent!";}		

echo "<br><br><a href=\"../index.php\">Back</a><br>";

The trick is that I need to have this PHP script run via scheduled task in Windows so it must be able to run on it's own, with no user intervention. So that means no POST or REQUESTs. Can anyone give me some direction here? TIA!

[MikeWire]

Nothing? Am I missing some detail or some info?

[laserlight]

Well, it does not look like you are actually sending anything related to what you retrieved from the database since you merely print what you retrieve from the database. What is missing is your actual code that is not working.

[djjjozsi]

while ($result = mssql_fetch_array($query, MSSQL_ASSOC)) 
{          
$to.=$result["email"].",";
}

if(!empty($to))
{
    $to= substr($to, 0, -1); //cut the last , from this string 

    $subject = "Batch Report";
    $message = "\n"   ;//info from above query results
    $headers = "From: [e-mail]";

    mail($to, $subject, $message, $headers) or die("Could not send message"); 
} 

[MikeWire]

Thanks djjjozsi - I was thinking of that solution, but prefer to send all results in one single email, which I may or may not be able to so. Because the SQL query returns multiple results, I also was thinking of temp tables in MSSSQL but have never used them - any thoughts there?

@ - my code works fine, but my problem is the while loop and emailing multiple query results - I cant seem to figure out how to store the results and email all of them. In a browser my results echo fine. If you need more info I would be glad to give what I can.

[laserlight]

MikeWire wrote:

my code works fine, but my problem is the while loop and emailing multiple query results - I cant seem to figure out how to store the results and email all of them. In a browser my results echo fine. If you need more info I would be glad to give what I can.

Oh, I see. So basically there is one recipient, but you want to combine the messages into one? A solution is to initialise an empty string before the loop, concatenate to the string on each iteration, then send the email with the string as the message after the loop.

[djjjozsi]

see my previous post, you can make a string from the emails from the results.

[MikeWire]

Thanks guys - that should take care of the problem. Great idea!

[MikeWire]

Little update: That code worked great for an email summary to a group of managers.

Here's my next challenge, which is a continuation of the first part - My company works with support tickets (a.ka. work orders) - each ticket has detail(s) (the results from my query) attached to it. So for example, I have 5 results from my query above. Three of those details are refer to ticket 1 and the other two details refer to ticket 2. I need to loop through the results and mail out a notice to the staff email on the ticket - assume different emails on each ticket.

The results should echo this:

Ticket#1
   ->Detail1
   ->Detail2
   ->Detail3
            --mail ticket & detail to staff#1
Ticket#2
   ->Detail4
   ->Detail5
            --mail ticket & detail to staff#2

So I understand I will need a couple of loops, but I'm a little unsure how to structure the loops so that each detail groups itself into each ticket.

I have adjusted my query to GROUP BY ticket # but I still can't seem to get my head wrapped around how to build the loops.

Can anyone give me a nudge in the right direction please?

[djjjozsi]

it's not necessery to use loop in a loop.

you can query the database, using the tickets and the ticket_details table and JOIN intogether.

select * from tickets t LEFT join ticket_details td ON td.ticketid=t.ticketid
ORDER BY t.ticketid , td.details_datetime

then you can use the while cycle to retrieve the records.

In the whils cycle you can watch the $row["ticket_id"] if is has changed you can send the mail in other case you can build the mail text.

you should never send mail in a loop if you have not tested the code!