Embed ODBC query in email

[kitfox69]

I am having trouble getting a result set to send via email. The email sends when I run the script through firefox and it displays the data contained in the result set but the email message does not show the data... just the number of rows in each result set.

Pass and Login edited out

<?php

$conn = odbc_connect("HOMES", "", "");

$subject = "Hourly Sales";

$headers = "From: reporting@XXXXX.com" . "\r\n" .
    "Reply-To: reporting@XXXXX.com" . "\r\n" .
    "X-Mailer: PHP/" . phpversion();

$str_q_1 = "SELECT * FROM hrly_sls WHERE hrly_sls_str = 1 ORDER BY hrly_sls_id";

$res_q_1 = odbc_exec($conn, $str_q_1 );

$res_1 = ODBC_RESULT_ALL($res_q_1);

$str_q_3 = "SELECT * FROM hrly_sls WHERE hrly_sls_str = 3 ORDER BY hrly_sls_id";

$res_q_3 = odbc_exec($conn, $str_q_3 );

$res_3 = ODBC_RESULT_ALL($res_q_3);

$message = "$res_1 /n/r

$res_3 /n/r";

$result = mail('bdavis@ashleyjax.com', $subject, $message, $headers ); 

if ( $result );

echo('Messages were sent to the mail server for processing.' );

?>

Also this is the paste of the email result:

2 /n/r

2 /n/r


ANY help is appreciated!

 

[vacunita]

Nothing is wrong, that's just the way ODBC_RESULT_ALL works.

It Prints out the results into an html table, but the function itslef onyl retunrs the number of rows returned by the query.

http://www.php.net/manual/en/function.odbc-result-all.php.

You could use output buffering, and get all the printed out stuff into a variable, and put that into your $message variable.

$str_q_1 = "SELECT * FROM hrly_sls WHERE hrly_sls_str = 1 ORDER BY hrly_sls_id";

$res_q_1 = odbc_exec($conn, $str_q_1 );
[red]ob_start();[/red]
$res_1 = ODBC_RESULT_ALL($res_q_1);

$str_q_3 = "SELECT * FROM hrly_sls WHERE hrly_sls_str = 3 ORDER BY hrly_sls_id";

$res_q_3 = odbc_exec($conn, $str_q_3 );

$res_3 = ODBC_RESULT_ALL($res_q_3);
[red]$message = ob_get_contents();[/red]
[green]\\use ob_end_flush to print out eveyrtihng to screen, or ob_end_clean, to just discard it, and send nothing to screen[/green] 
ob_end_flush();

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[kitfox69]

Thanks for the help... I have not used Output Buffering yet so I will give this a try.

I was also kicking around the idea of having the Output act as it's own module and instead of embedding the results in the email text, output the results into an HTML cache page and use a seperate email script to send the HTML page as an attachment.

How can I create the cache page to display just the two odbc_recult_all strings?

 

[kitfox69]

UPDATE!

I think I got it figured out using the Output Buffer you suggested Vac. Nice!!!

Below is the code I have now... modified slightly to include the Content Type and Mime Boundary needed for the email to be interpreted in HTML (just using the Output buffer showed the raw HTML code in the email... tables look really ugly in code LOL!)

<?php

$conn = odbc_connect("HOMES", "", "");

$random_hash = md5(date('r', time())); 

$subject = "Hourly Sales";

$headers = "From: reporting@XXXX.com" . "\r\n" .
    "Reply-To: reporting@XXXX.com" . "\r\n" .
    "X-Mailer: PHP/" . phpversion();
    
$headers .= "\r\nContent-Type: multipart/alternative; boundary=\"PHP-alt-".$random_hash."\"";

$str_q_1 = "SELECT * FROM hrly_sls WHERE hrly_sls_str = 1 ORDER BY hrly_sls_id";

$res_q_1 = odbc_exec($conn, $str_q_1 );

ob_start();

?>

--PHP-alt-<?php echo $random_hash; ?> 
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

<?php

$res_1 = odbc_result_all($res_q_1);

$str_q_3 = "SELECT * FROM hrly_sls WHERE hrly_sls_str = 3 ORDER BY hrly_sls_id";

$res_q_3 = odbc_exec($conn, $str_q_3 );

$res_3 = odbc_result_all($res_q_3);

?>

--PHP-alt-<?php echo $random_hash; ?>--

<?php

$message = ob_get_contents();

$result = mail('ME.Myself@UpYoursNews.com', $subject, $message, $headers ); 

if ( $result );

echo(' messages were sent to the mail server for processing.' );

?>

Now working on fixing the queries to show English column titles and to label the two sets of data.

THANKS AGAIN!!!!!!

 

[vacunita]

Glad I could help.
Sorry I didn't get back to this before. I think the output buffer is the easiest way to do this.




----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.