Solved

Get data from database and email it list to relevant people

Posted on 2007-12-07
25
211 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
Comment Utility
i dont see any error in your code
are u facing any php error or ??
0
 

Author Comment

by:kbit
Comment Utility
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
Comment Utility
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
 

Author Comment

by:kbit
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Same result.....hundreds of emails and that's when I shut the browser.
0
 
LVL 3

Expert Comment

by:HeroGuran
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:kbit
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Did that solve it for sure, or do you need any futher assistance?

Regards,
Andreas
0
 

Author Comment

by:kbit
Comment Utility
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
Comment Utility
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
Comment Utility
All users getting all open calls
0
 
LVL 3

Expert Comment

by:HeroGuran
Comment Utility
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
Comment Utility
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
Comment Utility
My pleasure.

Regards,
Andreas
0
 
LVL 23

Expert Comment

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

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now