Solved

Get data from database and email it list to relevant people

Posted on 2007-12-07
25
224 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

710 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