Solved

Get data from database and email it list to relevant people

Posted on 2007-12-07
25
217 Views
Last Modified: 2008-10-22
Hi,

I have some records on a MSSQL table called 'log'. The primary key is called 'CallID' and each call has a 'Status'.

What I'm trying to do in PHP is build a list of each users (field is called 'ITResource') open records (those with a 'Status' of "Logged") and email this list to each user.

Every thing I try results in either too many emails or too many records. Can someone please help?
Thanks
<?php
session_start();
 
// connect to database
include('helpdeskconnect.php');
$con = mssql_connect ($hostname, $username, $password);
mssql_select_db ($database, $con);
 
//return all open calls
$query = "SELECT ITResource FROM log WHERE Status = 'Logged' GROUP BY ITResource";
$result = mssql_query($query, $con) or die("Couldnt execute query.");
 
$num_results = mssql_num_rows($result);
 
for( $i = 0; $i < $num_results; $i++ )
    {
	//loop for each user with open calls
	$query1 = "SELECT CallID, ITResource FROM log WHERE Status = 'Logged' GROUP BY CallID, ITResource";
	$result1 = mssql_query($query1, $con) or die("Couldnt execute query.");
 
	//build the message
	$message.="<TABLE BORDER=1>";
 
	while ($row1 = mssql_fetch_array($result1))
	{
	$query2 = "SELECT Email FROM users WHERE Name = $row1['ITResource']";  //get email address
	$result2 = mssql_query($query2, $con) or die("Couldnt execute query.");
	while ($row2 = mssql_fetch_array($result2))
		{
		$to = $row['Email'];
		}
	$message .= "<TR>";
	$message .= "<TD WIDTH=200>";
	$message .= $row1["CallID"];
	$message .= "<TD WIDTH=50>";
	$message .= $row1["ITResource"];
	$message .= "</TR>";
	$message .="<BR>";
 
	//set SMTP parameters
	ini_set("SMTP","server");
	ini_set("sendmail_from",$from);
 
	//define header details
 
	$todaysdate = date("d M Y");
	$subject = "Call Logging Statistics for $todaysdate";
	$name = "Administrator";
	$from= "admin@mydomain.com";
	$message .= "<html><body>";
 
	mail("$to","$subject","$message","From: $name <$from>\n"."MIME-Version: 1.0\n"."Content-type: text/html; charset=iso-8859-1");
	}
}
?>

Open in new window

0
Comment
Question by:kbit
  • 11
  • 8
  • 5
  • +1
25 Comments
 
LVL 15

Expert Comment

by:babuno5
ID: 20426873
i dont see any error in your code
are u facing any php error or ??
0
 

Author Comment

by:kbit
ID: 20427267
There's no error, that's the problem.

At the moment, the first query returns 14 users...these users have a total of 40 records open at the moment (the second query). My code says that for each of these 14 users, put these 40 records in the email. The result is 40 emails, not 14.  

My code SHOULD say that for each of these 14 users, put ONLY THEIR records in the email. The result would be 14 emails.

Hope this makes sense, thanks for looking

0
 
LVL 3

Expert Comment

by:HeroGuran
ID: 20427503
You are using the same connection getting the second records in the deepest while. It overwrites the data-array you get from the previous looping.
Try the code below, with the added connection $con2.
You should also consider closing the database connection.

Regards,
Andreas
<?php
session_start();
 
// connect to database
include('helpdeskconnect.php');
$con = mssql_connect ($hostname, $username, $password);
$con2 = mssql_connect ($hostname, $username, $password);
mssql_select_db ($database, $con);
mssql_select_db ($database, $con2);
 
//return all open calls
$query = "SELECT ITResource FROM log WHERE Status = 'Logged' GROUP BY ITResource";
$result = mssql_query($query, $con) or die("Couldnt execute query.");
 
$num_results = mssql_num_rows($result);
 
for( $i = 0; $i < $num_results; $i++ )
    {
	//loop for each user with open calls
	$query1 = "SELECT CallID, ITResource FROM log WHERE Status = 'Logged' GROUP BY CallID, ITResource";
	$result1 = mssql_query($query1, $con) or die("Couldnt execute query.");
 
	//build the message
	$message.="<TABLE BORDER=1>";
 
	while ($row1 = mssql_fetch_array($result1))
	{
	$query2 = "SELECT Email FROM users WHERE Name = $row1['ITResource']";  //get email address
	$result2 = mssql_query($query2, $con2) or die("Couldnt execute query.");
	while ($row2 = mssql_fetch_array($result2))
	{
		$to = $row['Email'];
	}
	$message .= "<TR>";
	$message .= "<TD WIDTH=200>";
	$message .= $row1["CallID"];
	$message .= "<TD WIDTH=50>";
	$message .= $row1["ITResource"];
	$message .= "</TR>";
	$message .="<BR>";
 
	//set SMTP parameters
	ini_set("SMTP","server");
	ini_set("sendmail_from",$from);
 
	//define header details
 
	$todaysdate = date("d M Y");
	$subject = "Call Logging Statistics for $todaysdate";
	$name = "Administrator";
	$from= "admin@mydomain.com";
	$message .= "<html><body>";
 
	mail("$to","$subject","$message","From: $name <$from>\n"."MIME-Version: 1.0\n"."Content-type: text/html; charset=iso-8859-1");
	}
}

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:kbit
ID: 20427932
For testing purposes, I removed the following lines and I hard coded the email address ($to) to my own one instead. I stopped the browser at 500 emails...!   Any thoughts?!

      $query2 = "SELECT Email FROM users WHERE Name = $row1['ITResource']";  //get email address
      $result2 = mssql_query($query2, $con2) or die("Couldnt execute query.");
      while ($row2 = mssql_fetch_array($result2))
      {
            $to = $row['Email'];
      }
0
 
LVL 3

Expert Comment

by:HeroGuran
ID: 20428159
Try running:
$query2 = "SELECT Email FROM users WHERE Name = $row1['ITResource']";

separetely in an interface like phpmyadmin if you have it, otherwise, just print it out to se what result you get.

Regards,
Andreas
0
 

Author Comment

by:kbit
ID: 20428219
If I substitute my name into the query instead of $row['ITResource'] then SQL Server returns my email address
0
 
LVL 23

Expert Comment

by:Stacy Spear
ID: 20438720
You are running three queries against the DB when one would do I think. Going to rework the whole thing to see what I get.
0
 
LVL 23

Expert Comment

by:Stacy Spear
ID: 20438769
Before I do that, what happens when this line:

$query1 = "SELECT CallID, ITResource FROM log WHERE Status = 'Logged' GROUP BY CallID, ITResource";

is changed to:

$query1 = "SELECT CallID, ITResource FROM log WHERE Status = 'Logged' GROUP BY ITResource, CallID";
0
 

Author Comment

by:kbit
ID: 20440268
Same result.....hundreds of emails and that's when I shut the browser.
0
 
LVL 3

Expert Comment

by:HeroGuran
ID: 20440694
Well, you still use the same connection within the while statement.
What is the reason for running first:
$query = "SELECT ITResource FROM log WHERE Status = 'Logged' GROUP BY ITResource";
And then:
$query1 = "SELECT CallID, ITResource FROM log WHERE Status = 'Logged' GROUP BY CallID, ITResource";

Either use just one query, or use another connection for the inner one. As it looks now you change the query result of the while loop every time you loop, which create an infinite loop. Can't believe I missed that earlier..

Regards,
Andreas
0
 
LVL 23

Expert Comment

by:Stacy Spear
ID: 20440781
Hero, he is using different result sets. I do quite a few queries when needed off one connection quite often without issue.

The problem as I see it is that the result for the following statement:

"SELECT CallID, ITResource FROM log WHERE Status = 'Logged' GROUP BY CallID, ITResource";

is looped through to generate emails. Of course, each particular ITResource would be in that result multiple times, hence the multiple emails. If that statement could be reworked to produce one ITResource with numerous CallIDs attached, it could solve this, which is why I asked about changing the group order.

If not, then some method must be added to track whether or not an email address has been used, this before the last query for sure! Perhaps a dictionary object. I believe they work the same in PHP and .NET. Will check that out for sure.
0
 
LVL 23

Expert Comment

by:Stacy Spear
ID: 20440797
I had a syntax error when I pasted your code into PDP on the line where I think the issue lies. Try this one.

$query2 = "SELECT Email FROM users WHERE Name = ".$row1['ITResource'];  //get email address
0
 

Author Comment

by:kbit
ID: 20440929
For testing purposes, I have removed the following lines and I hard coded the email address ($to) to my own one instead.
      $query2 = "SELECT Email FROM users WHERE Name = $row1['ITResource']";  //get email address
      $result2 = mssql_query($query2, $con2) or die("Couldnt execute query.");
      while ($row2 = mssql_fetch_array($result2))
      {
            $to = $row['Email'];
      }
0
 

Author Comment

by:kbit
ID: 20440936
Same problem.
Andreas, the reason I'm running this 1st query:
$query = "SELECT ITResource FROM log WHERE Status = 'Logged' GROUP BY ITResource";

is to find out how many distinct people I need to email. My 2nd query gets me the CallID's I need to put in the email message. If I dont use the 1st query, I'll get duplicate users.
0
 
LVL 3

Expert Comment

by:HeroGuran
ID: 20440952
I did some changes to the code that should work. I didn't test it yet but hopefully the idea gets through.
Tell me if you find more trouble.

Regards,
Andreas
<?php
session_start();
 
// connect to database
include('helpdeskconnect.php');
$con = mssql_connect ($hostname, $username, $password);
$con2 = mssql_connect ($hostname, $username, $password);
mssql_select_db ($database, $con);
mssql_select_db ($database, $con2);
 
//return all open calls
$query = "SELECT DISTINCT ITResource ".
"FROM log ".
"LEFT JOIN users ON log.ITResource = users.Name ".
"WHERE Status = 'Logged'";
$result = mssql_query($query, $con) or die("Couldnt execute query.");
 
while($row = mssql_fetch_array($result))
{
	//loop for each call
	$query1 = "SELECT CallID FROM log WHERE Status = 'Logged' AND ITResource = '".$row["ITResource"]."' ORDER BY CallID";
	$result1 = mssql_query($query1, $con2) or die("Couldnt execute query.");
 
	//build the message
	$message.="<TABLE BORDER=1>";
 
	while ($row1 = mssql_fetch_array($result1))
	{
		$message .= "<TR>";
		$message .= "<TD WIDTH=200>";
		$message .= $row1["CallID"];
		$message .= "<TD WIDTH=50>";
		$message .= $row["ITResource"];
		$message .= "</TR>";
		$message .="<BR>";
	
		//define header details
		$todaysdate = date("d M Y");
		$subject = "Call Logging Statistics for $todaysdate";
		$name = "Administrator";
		$from= "admin@mydomain.com";
		$message .= "<html><body>";
		$to = $row['Email'];
 		if($to)
		{
			//set SMTP parameters
			ini_set("SMTP","server");
			ini_set("sendmail_from",$from);
			mail("$to","$subject","$message","From: $name <$from>\n"."MIME-Version: 1.0\n"."Content-type: text/html; charset=iso-8859-1");
		}
	}
}
?>

Open in new window

0
 

Author Comment

by:kbit
ID: 20448567
Many thanks for that. I tried it out and it send 40 emails.
For example I have 3 open calls, I should receive 1 email with these 3 open calls contained within.

Instead I received 3 emails, the first contained 1 open call, the second contained 2 open calls and the third was correct in containing 3 open calls.

Nice efficient work you've done there, we're not too far away from solving it!  I'll experiment further...
0
 

Author Comment

by:kbit
ID: 20448619
Here's the solution I think:

<?php
session_start();
 
// connect to database
include('helpdeskconnect.php');
$con = mssql_connect ($hostname, $username, $password);
$con2 = mssql_connect ($hostname, $username, $password);
mssql_select_db ($database, $con);
mssql_select_db ($database, $con2);
 
//return all open calls
$query = "SELECT DISTINCT ITResource ".
"FROM log ".
"LEFT JOIN users ON log.ITResource = users.Name ".
"WHERE Status = 'Logged'";
$result = mssql_query($query, $con) or die("Couldnt execute query.");
 
while($row = mssql_fetch_array($result))
{
	//loop for each call
	$query1 = "SELECT CallID FROM log WHERE Status = 'Logged' AND ITResource = '".$row["ITResource"]."' ORDER BY CallID";
	$result1 = mssql_query($query1, $con2) or die("Couldnt execute query.");
 
	//build the message
	$message.="<TABLE BORDER=1>";
 
	while ($row1 = mssql_fetch_array($result1))
	{
		$message .= "<TR>";
		$message .= "<TD WIDTH=200>";
		$message .= $row1["CallID"];
		$message .= "<TD WIDTH=50>";
		$message .= $row["ITResource"];
		$message .= "</TR>";
	}	
		//define header details
		$todaysdate = date("d M Y");
		$subject = "Call Logging Statistics for $todaysdate";
		$name = "Administrator";
		$from= "admin@mydomain.com";
		$message .= "<html><body>";
		$to = $row['Email'];
 		if($to)
		{
			//set SMTP parameters
			ini_set("SMTP","server");
			ini_set("sendmail_from",$from);
			mail("$to","$subject","$message","From: $name <$from>\n"."MIME-Version: 1.0\n"."Content-type: text/html; charset=iso-8859-1");
		}
	
}
?>

Open in new window

0
 
LVL 3

Expert Comment

by:HeroGuran
ID: 20448671
Did that solve it for sure, or do you need any futher assistance?

Regards,
Andreas
0
 

Author Comment

by:kbit
ID: 20448721
Almost, the correct number of emails are being sent...the only problem is that for each user, the previous user's calls are being appended to it.

So my name is Andrew, I get my 3 calls in my email.
Brian is next, he's getting a list of 5 calls, 3 of which are mine plus his two.

I need to prevent this somehow
0
 
LVL 3

Accepted Solution

by:
HeroGuran earned 500 total points
ID: 20448731
Try this
<?php
session_start();
 
// connect to database
include('helpdeskconnect.php');
$con = mssql_connect ($hostname, $username, $password);
$con2 = mssql_connect ($hostname, $username, $password);
mssql_select_db ($database, $con);
mssql_select_db ($database, $con2);
 
//return all open calls
$query = "SELECT DISTINCT ITResource ".
"FROM log ".
"LEFT JOIN users ON log.ITResource = users.Name ".
"WHERE Status = 'Logged'";
$result = mssql_query($query, $con) or die("Couldnt execute query.");
 
while($row = mssql_fetch_array($result))
{
	//loop for each call
	$query1 = "SELECT CallID FROM log WHERE Status = 'Logged' AND ITResource = '".$row["ITResource"]."' ORDER BY CallID";
	$result1 = mssql_query($query1, $con2) or die("Couldnt execute query.");
 
	//build the message
	$message ="<TABLE BORDER=1>";
 
	while ($row1 = mssql_fetch_array($result1))
	{
		$message .= "<TR>";
		$message .= "<TD WIDTH=200>";
		$message .= $row1["CallID"];
		$message .= "<TD WIDTH=50>";
		$message .= $row["ITResource"];
		$message .= "</TR>";
	}	
		//define header details
		$todaysdate = date("d M Y");
		$subject = "Call Logging Statistics for $todaysdate";
		$name = "Administrator";
		$from= "admin@mydomain.com";
		$message .= "<html><body>";
		$to = $row['Email'];
 		if($to)
		{
			//set SMTP parameters
			ini_set("SMTP","server");
			ini_set("sendmail_from",$from);
			mail("$to","$subject","$message","From: $name <$from>\n"."MIME-Version: 1.0\n"."Content-type: text/html; charset=iso-8859-1");
		}
	
}
?>

Open in new window

0
 

Author Comment

by:kbit
ID: 20448780
All users getting all open calls
0
 
LVL 3

Expert Comment

by:HeroGuran
ID: 20448814
That's wierd, all I did was to change
$message .="<TABLE BORDER=1>";
into
$message ="<TABLE BORDER=1>";

This resets the message for each user instead of adding more and more.
Did you change anything else?
Last post you claimed the message builds on, and that's what should solve it. By the look of the SQL it should also get the 'Logged' logs where ITResource = $row["ITResource"]. Try printing that SQL and run it seperately.

Regards,
Andreas
0
 

Author Comment

by:kbit
ID: 20449116
Very sorry, I added in a query restriction. fort esting..it works perfectly now. Thanks ever so much for all suggestions and inspiration, it's very much appreciated
0
 
LVL 3

Expert Comment

by:HeroGuran
ID: 20449129
My pleasure.

Regards,
Andreas
0
 
LVL 23

Expert Comment

by:Stacy Spear
ID: 20451582
You can still run that off of one connection. Plus you are not closing them afterwards. Just more efficient that way.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
.htaccess 5 36
hosting images 4 28
Wordpress Taxonomy 2 28
How has this flight information been added to this website? 7 22
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question