• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

Get data from database and email it list to relevant people

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
kbit
Asked:
kbit
  • 11
  • 8
  • 5
  • +1
1 Solution
 
babuno5Commented:
i dont see any error in your code
are u facing any php error or ??
0
 
kbitAuthor Commented:
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
 
HeroGuranCommented:
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
kbitAuthor Commented:
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
 
HeroGuranCommented:
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
 
kbitAuthor Commented:
If I substitute my name into the query instead of $row['ITResource'] then SQL Server returns my email address
0
 
Stacy SpearPresident/Principal ConsultantCommented:
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
 
Stacy SpearPresident/Principal ConsultantCommented:
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
 
kbitAuthor Commented:
Same result.....hundreds of emails and that's when I shut the browser.
0
 
HeroGuranCommented:
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
 
Stacy SpearPresident/Principal ConsultantCommented:
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
 
Stacy SpearPresident/Principal ConsultantCommented:
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
 
kbitAuthor Commented:
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
 
kbitAuthor Commented:
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
 
HeroGuranCommented:
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
 
kbitAuthor Commented:
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
 
kbitAuthor Commented:
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
 
HeroGuranCommented:
Did that solve it for sure, or do you need any futher assistance?

Regards,
Andreas
0
 
kbitAuthor Commented:
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
 
HeroGuranCommented:
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
 
kbitAuthor Commented:
All users getting all open calls
0
 
HeroGuranCommented:
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
 
kbitAuthor Commented:
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
 
HeroGuranCommented:
My pleasure.

Regards,
Andreas
0
 
Stacy SpearPresident/Principal ConsultantCommented:
You can still run that off of one connection. Plus you are not closing them afterwards. Just more efficient that way.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 11
  • 8
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now