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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SwafnilCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Email Servers

From novice to tech pro — start learning today.