[RESOLVED] mailing the results of a query to multiple recipients

[ali_p]

I have a table of members and a table of jobs. The members table contails details such as name, email, jobseeeker( this says 'on' if they want job matches sent to them), and query( this contains a query that was saved from when they initailly selected which jobs that they are interested in , so for examply this could say " SELECT * FROM jobs where 1 and (division='L') and (sector='FU') and (location='0') order by division").

Right i am using the 'query' field to produce a set of results from the jobs table, which i then send to the recipient.

Here is my code

include("../app_global.php");

$display_block = "";
// step 1 get all the my_brightwaters who want job matches sent to them

//open the connection
$conn = mysql_connect("$glob_host", "$glob_user", "$glob_pass");
//pick the database to use
mysql_select_db($db_name, $conn);

// select only those who want the job matches sent to them
$sql = "SELECT *
		FROM my_brightwater
		WHERE jobseeker = 'on'";
$result = mysql_query($sql,$conn)or die(mysql_error());
	while ($row = mysql_fetch_array($result)) {
	//give a name to the fields
	$fullname = $row['fullname'];
	$email=$row['email'];
	$query=$row['query'];

//now we have the individual queries from the database we want to build a new query out of it, this 
$sql2 = "$query";
$result2 = mysql_query($sql2,$conn)or die(mysql_error());
	while ($row2 = mysql_fetch_array($result2)) {
	//give a name to the fields
	$job_title = $row2['job_title'];
	$job_key = $row2['job_key'];
	$division=$row2['division'];
	$sector=$row2['sector'];
	$location = $row2['location'];
	$salary = $row2['salary'];
	$job_id = $row2['job_id'];
	$perm_temp=$row2['perm_temp'];
	$company=$row2['company'];
	$location=$row2['location'];
	$job_title=$row2['job_title'];
	$job_notes=$row2['job_notes'];
	$display_block .="<table width=\"450\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\"><tr><td valign=\"top\" class=\"jtitle\">$job_title</td><td>Ref: $job_key</td></tr><tr><td colspan=\"2\">$location</td></tr><tr><td colspan=\"2\">$job_notes</td></tr><tr><td height=\"20\" colspan=\"2\"></td></tr></table>";



}
//mail stuff
$msg = "
Hello $fullname<br>
Your Job Matches are:<br><br>
$display_block

";
//$mailto = "$email";
$from = "[e-mail]";
$subject = "Job Search Results";
$headers  = "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
$headers .= "From: [e-mail]\n";
$headers .="To: $email\n";

mail($from, $subject, $msg, $headers);
$display_block5 .="Job Matches have been sent to $email\n";
//end mail stuff


}

The jobs that match are being sent out fine however only the last recipient in the list is recieving this, how can i tell it to send to all members??

Thanks in advance

[vaaaska]

print_r the results from the query that gets you list of email recipients...insert this...

// select only those who want the job matches sent to them 
$sql = "SELECT * 
        FROM my_brightwater 
        WHERE jobseeker = 'on'"; 
$result = mysql_query($sql,$conn)or die(mysql_error()); 
print_r($result); exit;

Tell us what you get on screen for this?

[ali_p]

Hi , this is what i get:

Resource id #4

[vaaaska]

Sorry, let's try to get the array a little more precisely...my fault...replace that with this...

 // select only those who want the job matches sent to them 
$sql = "SELECT * 
        FROM my_brightwater 
        WHERE jobseeker = 'on'";  

$result = mysql_query($sql,$conn)or die(mysql_error());
while ($row = mysql_fetch_array($result)) { 
print_r($result); exit;
}

We don't need the loop but this will make sure we don't get an error message...etc...then tell me what you get...I'll hang around...

[ali_p]

Hi, yeah it still says

Resource id #4

sorry

[laserlight]

I think vaaaska meant:
print_r($row);

[ali_p]

Hi yes i still get Resource id #4 cdisplayed.

Maybe i should point out that my problem is not with getting the members email and sending them a mail, as i have tested with just 'test ' mails and this is fine. Its sends all members a mail.
My problem occurs when i try to send the results from the jobs query to the member.
Basically only the last member in the table recieves.

Here is the code

 include("../app_global.php"); 

$display_block = ""; 
// step 1 get all the my_brightwaters who want job matches sent to them 

//open the connection 
$conn = mysql_connect("$glob_host", "$glob_user", "$glob_pass"); 
//pick the database to use 
mysql_select_db($db_name, $conn); 

// select only those who want the job matches sent to them 
$sql = "SELECT * 
        FROM my_brightwater 
        WHERE jobseeker = 'on'"; 
$result = mysql_query($sql,$conn)or die(mysql_error()); 
    while ($row = mysql_fetch_array($result)) { 
    //give a name to the fields 
    $fullname = $row['fullname']; 
    $email=$row['email']; 
    $query=$row['query']; 

//now we have the individual queries from the database we want to build a new query out of it, this 
$sql2 = "$query"; 
$result2 = mysql_query($sql2,$conn)or die(mysql_error()); 
    while ($row2 = mysql_fetch_array($result2)) { 
    //give a name to the fields 
    $job_title = $row2['job_title']; 
    $job_key = $row2['job_key']; 
    $division=$row2['division']; 
    $sector=$row2['sector']; 
    $location = $row2['location']; 
    $salary = $row2['salary']; 
    $job_id = $row2['job_id']; 
    $perm_temp=$row2['perm_temp']; 
    $company=$row2['company']; 
    $location=$row2['location']; 
    $job_title=$row2['job_title']; 
    $job_notes=$row2['job_notes']; 
    $display_block .="<table width=\"450\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\"><tr><td valign=\"top\" class=\"jtitle\">$job_title</td><td>Ref: $job_key</td></tr><tr><td colspan=\"2\">$location</td></tr><tr><td colspan=\"2\">$job_notes</td></tr><tr><td height=\"20\" colspan=\"2\"></td></tr></table>"; 



} 
//mail stuff 
$msg = " 
Hello $fullname<br> 
Your Job Matches are:<br><br> 
$display_block 

"; 
//$mailto = "$email"; 
$from = "[e-mail]"; 
$subject = "Job Search Results"; 
$headers  = "MIME-Version: 1.0\r\n"; 
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n"; 
$headers .= "From: [e-mail]\n"; 
$headers .="To: $email\n"; 

mail($from, $subject, $msg, $headers); 
$display_block5 .="Job Matches have been sent to $email\n"; 
//end mail stuff 


} 

thanks

[vaaaska]

This is confusing...why do you have a query inside of your loop that sends info to people? Shouldn't this not be in the loop? I see that you are adding to the loop who receives the email (is that right)?

A more logical way, I believe, would be to do it this way...

• query for your email list...
• query for your display block results
• loop for your mail() and insert your display block into $msg

[ali_p]

I have a query inside the loop because every member of the member table who has 'jobseeker' = 'on' has their own individual query field which contains the query that will be used to query the jobs table , thus produce their job matches ( which i intend to send to that member).
So far i have
1. queried my email list
2. pulled out their individual query filed result.
3. built a query out of this ( querying the jobs table, to get the job matches)
4. sent an email of the job matches to recipient.

My problem is that only the last member recieves the mail. Whereas if i leave out the $display_block in the message and just send "hello", all recipients recieve mail.

[vaaaska]

I'm not sure why you are building up your $blocks with ".=". What is that for?

I changed that...and took out some things I didn't like looking at...this can be cleaned up later...

include("../app_global.php");  

$display_block = "";  

// step 1 get all the my_brightwaters who want job matches sent to them 

//open the connection 
$conn = mysql_connect("$glob_host", "$glob_user", "$glob_pass");  

//pick the database to use 
mysql_select_db($db_name, $conn);  

// select only those who want the job matches sent to them 
$sql = "SELECT * 
        FROM my_brightwater 
        WHERE jobseeker = 'on'";  

$result = mysql_query($sql,$conn)or die(mysql_error());  

    while ($row = mysql_fetch_array($result)) {

//give a name to the fields 
$fullname = $row['fullname'];  
$email=$row['email'];  

// you story the query line in the table?
$query=$row['query'];  

//now we have the individual queries from the database we want to build a new query out of it, this
$sql2 = "$query";  

$result2 = mysql_query($sql2,$conn)or die(mysql_error());  

    while ($row2 = mysql_fetch_array($result2)) {

// edited this out and changed the below because I didn't want to look at them

$display_block = "<table width=\"450\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\"><tr><td valign=\"top\" class=\"jtitle\">$row2[$job_title]</td><td>Ref: $row2[$job_key]</td></tr><tr><td colspan=\"2\">$row2[$location]</td></tr><tr><td colspan=\"2\">$row2[$job_notes]</td></tr><tr><td height=\"20\" colspan=\"2\"></td></tr></table>";  

}

//mail stuff 
$msg = "Hello $fullname<br> 
		Your Job Matches are:<br><br> 
		$display_block"; 

//$mailto = "$email"; 
$from = "[e-mail]";  

$subject = "Job Search Results";  

$headers  = "MIME-Version: 1.0\r\n";  

$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";  

$headers .= "From: [e-mail]\n";  

$headers .="To: $email\n";  

mail($from, $subject, $msg, $headers);  

$display_block5 = "Job Matches have been sent to $email\n";  

//end mail stuff       

}

[LoganK]

Have any of you seen the manual for [man]mail[/man]? The first argument is "to", not "from"!!!

and FYI, header lines need to end with "\r\n", not just "\n".

[vaaaska]

LoganK wrote:

Have any of you seen the manual for [man]mail[/man]? The first argument is "to", not "from"!!!

Give me a break will you. While trying to 'help' somebody I was debugging a new app., dealing with a client planning a new project and my girlfriend was trying to plan a dinner (that I'm cooking) for tommorrow evening.

😉

[LoganK]

I'm not giving you specifically a hard time, I just thought that with so many experience PHP people here, the first they'd look at is the actual function call the user is using, in case they messed something up. The loop could be the problem, but I always start out at the simplest element - in this case, the calling of the actual mail() function.

So, no, I'm not trying to give you a hard time. Don't not reccomend me to be a mod later on :o!

[ali_p]

Thanks to all who have helped with my question, esp Vaaaska (good luck with the meal). Having tweeked my mail headers and generally cleaned up my code, the app appears to be working now.

Discussion indeed broadens the mind.

[LoganK]

Yes it does 😃

Good luck, and remember to mark the thread resolved! 🙂