How can I e-mail MSSQL results via PHP?

Hi all,
I have a simply select script that I run in the mornings to find failed jobs for SQL Server that have run/failed overnight.  I want to email this over but can't figure out how to email the table/results over.

The subject/body bit works fine but I assumed that if I just stuck $sql_result in the body, it would email results over?

But instead, I got odbc_result_all(Resource id #2)

Appreciate any help

I'm more used to MySQL and forms when it comes to emailing so it's fairly new to me all this

Thanks in advance

(Note: Code in the body area for the email has a number of possible tries I've had)
<?php



?>
<html>
<p><b>Failed Jobs</b></p>
</html>
<?php
header('Refresh: 10');
// create connection
$connection = odbc_connect("ODBC_Prod","user","pass");

// test connection
if (!$connection) {
echo "Couldn't make a connection!";
exit;
}


// create SQL statement
//$sql = "sp_testrunjob";
$sql="

-- Variable Declarations 
DECLARE @PreviousDate datetime 
DECLARE @Year VARCHAR(4) 
DECLARE @Month VARCHAR(2) 
DECLARE @MonthPre VARCHAR(2) 
DECLARE @Day VARCHAR(2) 
DECLARE @DayPre VARCHAR(2) 
DECLARE @FinalDate INT 

-- Initialize Variables 
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Today
SET @Year = DATEPART(yyyy, @PreviousDate)  
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate)) 
SELECT @Month = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2) 
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate)) 
SELECT @Day = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2) 
SET @FinalDate = CAST(@Year + @Month + @Day AS INT) 

-- Final Logic 
SELECT   j.[name], 
         s.step_name, 
         h.step_id, 
         h.step_name, 
         h.run_date, 
         h.run_time, 
         h.sql_severity, 
         h.message, 
         h.server 
FROM     msdb.dbo.sysjobhistory h 
         INNER JOIN msdb.dbo.sysjobs j 
           ON h.job_id = j.job_id 
         INNER JOIN msdb.dbo.sysjobsteps s 
           ON j.job_id = s.job_id
           AND h.step_id = s.step_id
WHERE   -- h.run_status = 0 -- Failure 
          h.run_date < @FinalDate 
ORDER BY h.instance_id DESC
";

// prepare SQL statement
$sql_result = odbc_prepare($connection,$sql);

// execute SQL statement and get results
odbc_execute($sql_result);

// format result in HTML table
//odbc_result_all($sql_result,"border=1");

$to = "user@company.com";
$subject = "Job Status";
//$body = "Hi,\n\nDoes this work?";
$body = "odbc_result_all($sql_result)"; $sql_result;
odbc_result_all($sql_result,"border=1");

if (mail($to, $subject, $body))
{
   echo("<p>Message successfully sent!</p>");
}
else
{
   echo("<p>Message delivery failed...</p>");
}





// free resources and close connection
odbc_free_result($sql_result);
odbc_close($connection);

?>

Open in new window

LVL 5
akashjAsked:
Who is Participating?
 
bevhostConnect With a Mentor Commented:
Just had a look at the manual http://php.net/manual/en/function.odbc-result-all.php

This function prints the html result and returns the number of rows which is in turn put into the body of the email.

To get the actual results the http://www.php.net/manual/en/function.odbc-result.php page says that this funtion returns the actual result instead.
so.

try this...

$body = var_export(odbc_result($sql_result));
0
 
bevhostCommented:


try something like this...

$body = var_export(odbc_result_all($sql_result));
0
 
Steve BinkCommented:
It is the same concept as MySQL.  Your query returns a result set.  Go through the result set to retrieve the rows, adding each row to the body of your email with whatever formatting you need.  bevhost has given you a handy shortcut to that.  See here for more explanation:

http://www.php.net/manual/en/function.odbc-result-all.php
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
akashjAuthor Commented:
Thanks both
I tried simply putting in bevhost's line to expect a result on-screen to which I get the number 2
This was correct in showing that I had 2 failed jobs overnight however, despite being part of the e-mail body, it did not send this via email and instead, sent a blank email

Am I putting this is in the wrong area or will I need to re-run the results query within the email body?
0
 
SwafnilConnect With a Mentor Commented:
You first have to fetch the rows from the recordset through using odbc_fetch_row and odbc_result and then send your mail with the generated table holding the result rows:
<?php
$fields = odbc_num_fields($sql_result);
$mailBody = "<table border='1' width='100%'><tr>";
// Column Headers
for ($i=1; $i <= $fields; $i++){
	$mailBody .= "<th bgcolor='silver'>".odbc_field_name($sql_result,$i)."</th>";
}
// Rows
while( odbc_fetch_row($sql_result)){
	$mailBody .= "<tr>";
	for($i=1; $i <= $fields; $i++){
		$mailBody .= "<td>".odbc_result($sql_result, $i )."</td>";
	}
	$mailBody .= "</tr>";
}
$mailBody .= "</table>";

Open in new window

0
 
akashjAuthor Commented:
Thans both
I was under the impression it would just post to email the results so to speak

I'll try and get this in today and report back

Thanks again
0
 
bevhostCommented:
How did you go?
0
 
akashjAuthor Commented:
Thanks bevhost for chasing this up - I am getting forgetful in my old age! :- )

It worked perfectly with your advice previously with a bit of modification of Swafnil's script too

Now I just need to create a new thread on how I can/should allocate the points :- )

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.