emailing contents of database

[jasonfrew]

Hi all

I have a script that will query a database table and email the results to a specific email address.

the script is as follows

<?php
include("include/dbcommon.php");
global $dal;
$dalTableName = $dal->Table("Enquiries");
$rstmp = $dalTableName->Query("DATEDIFF(day, GETDATE(), ExpiryDate)>0","");
while ($datatmp = db_fetch_array($rstmp))
{
        //send email for each record
        $email=$datatmp["EmailField"];
        //EmailField is your actual field name
        $msg="This event was expired\r\n";
        $msg.="Quote Number: ".$datatmp["Quote_no"]."\r\n";
        $msg.="Quote Required Date: ".$datatmp["Quote_req_date"]."\r\n";     

        $subject="Expired";
        runner_mail(array('to' => $email, 'subject' => $subject, 'body' => $message));   

}
//FieldName1, FieldName2 and ExpiryDate are your actual field names, TableName is your actual table name
?>

the issue i have is i want to hard code the email address into the script as the table im querying does not have email addresses stored in it. what in the script above do i need to change and can you sugest what i change it to

Kind Regards

Jason

[Krik]

I am guessing that the"runner_mail" function is a call to a function (defined in your code elsewhere) that runs the "mail" function (PHP function). As such the "$email" variable is were you can hard code an email address.

[jasonfrew]

thanks for the advice

Yeah the Runner_mail function is part of my application. i know this is working as other email requests are being passed to it and i am receiving emails from the application.

My script now looks like this

<?php
include("include/dbcommon.php");
global $dal;
$dalTableName = $dal->Table("Enquiries");


$rstmp = $dalTableName->Query("Quote_req_Date < Getdate()");

while ($datatmp = db_fetch_array($rstmp))
{

$email='[e-mail]'; 


$msg="Quote overdue";

$msg.="Quote Number: ".$datatmp["Quote_no"]."\r\n";

$msg.="Date Quote Required: ".$datatmp["Quote_req_date"]."\r\n"; 

$subject="Quote Overdue";

runner_mail(array('to' => $email, 'subject' => $subject, 'body' => $message)); 
}

i Receive a php error as follows
Error type 8
Error description Undefined variable: message
URL hamallcrm/repmail.php?
Error file C:\wamp\www\repmail.php
Error line 23
SQL query select * from [dbo].[Enquiries] where Quote_req_Date < Getdate()

I am using php 5.3 and wamp server

I am very new to PHP so am finding it difficult to resolve the problem

Any help you can offer would be greatly appreciated

regards

[jasonfrew]

hi again

script now working

functional script looks like this

<?php
include("include/dbcommon.php");
global $dal;
$dalTableName = $dal->Table("Enquiries");


$rstmp = $dalTableName->Query("DATEDIFF(day, GETDATE(), Quote_req_date)<0","");

while ($datatmp = db_fetch_array($rstmp))
{

$email='[e-mail]'; 


$msg="Quote overdue";

$msg.="Quote Number: ".$datatmp["Quote_no"]."\r\n";

$msg.="Date Quote Required: ".$datatmp["Quote_req_date"]."\r\n"; 

$subject="Quote Overdue";

runner_mail(array('to' => $email, 'subject' => $subject, 'body' => $msg)); 
}

[mrjoeblack]

hi, interested in this .... is the dbcommon.php just a connection ... can you give it's script and that of the runner mail function???? Basically can you explain how to set up the whole thing??

I'm kinda new to getting this stuff, thanks

[jasonfrew]

Hi Im Fairly new to this too. And im kinda cheating by using an application to build the scripts.

I think if i showed you the whole process that im running, would only serve to make things harder for you.

Ill start at the beginning of what I have done to have my database queried and send the mail in its simplest form.

Im using a wamp server on a windows server 2003

I have added to the wamp directory “sendmail” http://glob.com.au/sendmail/ as im not running iis to host the web app and am not using an exchange server. I have set up a web account to use the smtp server to forward the mail. If you have an exchange I would recommend using that however that will require you to set up the process slightly different.

My php.ini file looks like this:

[mail function]
; For Win32 only.
;SMTP = localhost
;smtp_port = 25

My sendmail.ini looks like this

smtp_server=smtp.yandex.ru

; smtp port (normally 25)

smtp_port=25

; SMTPS (SSL) support
;   auto = use SSL for port 465, otherwise try to use TLS
;   ssl  = alway use SSL
;   tls  = always use TLS
;   none = never try to use SSL

smtp_ssl=auto

; the default domain for this server will be read from the registry
; this will be appended to email addresses when one isn't provided
; if you want to override the value in the registry, uncomment and modify

default_domain=yandex.ru

; log smtp errors to error.log (defaults to same directory as sendmail.exe)
; uncomment to enable logging

error_logfile=error.log

; create debug log as debug.log (defaults to same directory as sendmail.exe)
; uncomment to enable debugging

;debug_logfile=debug.log

; if your smtp server requires authentication, modify the following two lines

auth_username=[e-mail]
auth_password=Y0urPa55w0rD

; if your smtp server uses pop3 before smtp authentication, modify the 
; following three lines.  do not enable unless it is required.

;pop3_server=
;pop3_username=
;pop3_password=

At this point if your doing the same as my self your server will be prepped and ready to send email. How to get it to do so, is the next challenge.

So you will have to create a Function to handle the mail this in my phpfunctions.php file

<?php

/*
	Wrapper for mail() function.
	$params array Input paramaters.
	The following parameters are supported:
	'from' Sender email address. If none specified an email address from the wizard will be used.
	'to' Receiver email address.
	'body' Plain text message body.
	'htmlbody' Html message body (do not use 'body' parameter in this case).
	'charset' Html message charset. If none specified the default website charset will be used.

Returns array with data:
"mailed" - indicates wheter mail sent or not
"errors" - array of errors
	Each error is an array with the following keys:
	"number" - error number
	"description" - error description
	"file" - name of the php file in which error happened
	"line" - line number on which error happened
*/

class ErrorHandler
{
	var $errorstack = array();
	function handle_mail_error($errno, $errstr, $errfile, $errline)
	{
		if(strpos($errstr,"It is not safe to rely on the system's timezone settings."))
			return;	
		$this->errorstack []= array('number' => $errno, 'description' => $errstr, 'file' => $errfile, 'line' => $errline);
	}
	function getErrorMessage()
	{
		$msg="";
		foreach($this->errorstack as $err)
		{
			if($msg)
				$msg.="\r\n";
			$msg.=$err['description'];
		}
		return $msg;
	}
}

function runner_mail($params)
{
	$from = isset($params['from']) ? $params['from'] : "";
	if(!$from)
	{
		$from = "";
	}
	$to = isset($params['to']) ? $params['to'] : "";
	$body = isset($params['body']) ? $params['body'] : "";
	$cc = isset($params['cc']) ? $params['cc'] : "";
	$bcc = isset($params['bcc']) ? $params['bcc'] : "";
	$replyTo = isset($params['replyTo']) ? $params['replyTo'] : "";
	$priority = isset($params['priority']) ? $params['priority'] : "";
	$charset = "";
	$isHtml = false;
	if(!$body)
	{
		$body = isset($params['htmlbody']) ? $params['htmlbody'] : "";
		$charset = isset($params['charset']) ? $params['charset'] : "";
		if(!$charset)
			$charset = "utf-8";
		$isHtml = true;
	}
	$subject = $params['subject'];

//
$header = "";
if($isHtml)
{
	$header .= "MIME-Version: 1.0\r\n";
	$header .= 'Content-Type: text/html;' . ( $charset ? ' charset=' . $charset . ';' : '' ) . "\r\n";
}

if($from)
{
	if(strpos($from, '<') !== false)
		$header .= 'From: ' . $from . "\r\n";
	else
		$header .= 'From: <' . $from . ">\r\n";

	@ini_set("sendmail_from", $from);
}
if($cc)
	$header .= 'Cc: ' . $cc . "\r\n";
if($bcc)
	$header .= 'Bcc: ' . $bcc . "\r\n";

if ($priority)
	$header .= 'X-Priority: '.$priority."\r\n";

if($replyTo)
{
	if(strpos($replyTo, '<') !== false)
		$header .= 'Reply-to: '.$replyTo."\r\n";
	else
		$header .= 'Reply-to: <'.$replyTo.">\r\n";
}


$eh = new ErrorHandler();
set_error_handler(array($eh, "handle_mail_error"));

$res = false;
if(!$header)
{
	$res = mail($to, $subject, $body);
}
else
{
	$res = mail($to, $subject, $body, $header);
}

restore_error_handler();
return array('mailed' => $res, 'errors' => $eh->errorstack, "message"=> nl2br($eh->getErrorMessage()));
} 

This is the missing link that if I was to show you it all it would cause you more problems than it would solve. So I will do my best to break it down into something usefull however you may need some assistance from other members on the forum

My dbcommon.php calls additional files in my application

include("locale.php");
include("events.php");   handles errors etc
include("commonfunctions.php");   sets out a list of standard functions  includes (phpfunctions.php)
include("dbconnection.php");   connects to the database
include("dbfunctions.php");   does what it says on the tin 
include("dal.php");   this file holds a copy of the database tables, a sort of temp file 
include("appsettings.php");

the final script that uses everything above looks like this.

<?php
include("include/dbcommon.php");
global $dal;
$dalTableName = $dal->Table("Enquiries_detail");


$rstmp = $dalTableName->Query("DATEDIFF(day, GETDATE(), Quote_req_date)<3",""and Status != 'Quote_sent');

while ($datatmp = db_fetch_array($rstmp))
{

$email='[e-mail]'; 


$msg="Quote overdue.  ";

$msg.="Customer Name:  ".$datatmp["Customer_name"]."\r\n";

$msg.="Quote Number:   Q".$datatmp["Quote_no"]."\r\n";

$msg.="Date Quote Required: ".$datatmp["Quote_req_date"]."\r\n"; 

$subject="Quote Overdue   ";

runner_mail(array('to' => $email, 'subject' => $subject, 'body' => $msg)); 
}

Global $dal; is the equivalent of select everything from every table in the database. And the $rstmp = $dalTableName->Query("DATEDIFF(day, GETDATE(), Quote_req_date)<3",""and Status != 'Quote_sent'); is in effect the where clause

There is a much easier way to do this I don’t doubt

Hope this gives you point in the correct ditrection.

[mrjoeblack]

wow phewwww pretty complicated ... hahaha. You are obviously more genious than me, perhaps in 10 years time, I'll get it all. Thanks for that tho 🙂 best of luck with your project.