Sending email wtih mysql qurey result on body

phpme1221 · June 30, 2011

Hello hope someone can help me out, I am trying to send out emails to users that owns servers, with date limits. So far i am able to send mail individually with the code i have below but i am unable to send the body with all the servers that individual owns.

For example, I keep getting email that only show 1 host name when there is suppose to be 2, thanks all for your help!

Here is the code i have so far, feel free to make recommendations, since i am very new to all this.

<?php
$que = mysql_query("SELECT Email,PrimaryContact,Hostname FROM sm_table WHERE NewSetDate='2011-07-01' ORDER BY Hostname ASC");
$res = mysql_query("SELECT DISTINCT Email FROM sm_table WHERE NewSetDate='2011-07-01' ORDER BY Hostname ASC");
$sql = mysql_query("SELECT DISTINCT Hostname FROM sm_table WHERE NewSetDate='2011-07-01' GROUP BY Hostname ASC");

$pc = 'PrimaryContact';
$hc = 'Hostname';
$em = 'Email';

//while($row = mysql_fetch_assoc($que)or die(mysql_error())) {
//echo 'Primary Contact: ' . $row[$pc] . '<br/>' . 'Hostname: ' .
$row[$hc] . '<br/>'. 'Email: ' . $row[$em] . '<br/> <br/>';

while($row2 = mysql_fetch_array($sql) or die(mysql_error())) {
//$hostlist[] = $row2['Hostname'];
//print_r ($hostlist);
//print $row['Hostname'];
$body = "List of Servers:";
$body .= $row2['Hostname'];
$body .="\nPlease click on link to register or cancel";
$subject = "Server Registration";
$from = "emialerts@partners.org";
$headers = "From:" . $from;

if ($row1 = mysql_fetch_assoc($res)or die(mysql_error())) {
$to = $row1[$em];

mail($to,$subject,$body,$headers);
}
}
?>

I would like to be able to send to each owner there servers they own. I so far am able to send to multiple user but not able to attach their server list on the body of the email. it sends it one at a time and or sends it twice , example

hostname email primarycontact

abc 123@hello.com joe smith

zzz 123@hello.com joe smith

ggg lily@yahoo.com lily smith

hhh lilly@yahoo.com lilly smith

jjj lilly@yahoo.com lily smith

in the above example joe smith should only get 1 email with 2 hostname, abc and zzz

same for lily smith 1 email with 3 hostname hhh jjj and ggg. CAN YOU PLEASE HELP AND THANKS EVERYONE!

phpme1221

New php-forum User

Posts: 1

Joined: Wed Jun 29, 2011 12:57 pm

Maq · June 30, 2011

In the future, please place

OR

tags around your code.

darkfreaks · June 30, 2011

$sql = mysql_query("SELECT Hostname, 
COUNT (Hostname) AS rowHostNames FROM sm_table 
WHERE NewSetDate='2011-07-01' GROUP BY Hostname HAVING ( COUNT(Hostname) > 1 )"); 
//Selecting Hostnames with more than server

phpme1221 · June 30, 2011

Thanks darkfreaks

Im getting this error now in the browser

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /var/www/email.php on line 25

FUNCTION XLSX_DB.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

I added your sql and changed this value $body .= $row['Hostname']; to $body .= $row['rowHostNames']; same error as above

added with array same error.. what am i missing?

darkfreaks · July 1, 2011

$sql = mysql_query("SELECT Hostname, 
COUNT (Hostname) AS rowHostNames FROM sm_table 
WHERE NewSetDate='2011-07-01' GROUP BY Hostname HAVING (COUNT(Hostname) > 1)");

doesn't like the extra white space before count i took it out

phpme1221 · July 1, 2011

hey dark

i tried the new sql you provided, but still does not work, no errors and no emails sent either this time. Using my original code up top, I print out the array on the browser and i do see it ( list of servers that needs to be sent out) but in the email it wont attach the last one

emails the [0] [1] array but NOT [2] ,

Help all!!!!

IrOnMaSk · July 1, 2011

hey, you have to create a html table before you your select and fetch array, then pass your result into the table... get it? like create table columns with host name, server, ...etc then put your $row into the columns like echo $row['Host'] and watever you need... srry this is kinda a quick and confusing,,, let me know if you need code for that. i got mine to work with similar stuff you're doing...

phpme1221 · July 1, 2011

Hey IrOnMask,

Thanks for the reply,

yes if you can please post it, thanks. I now not even sure i have my tables setup right.

IrOnMaSk · July 1, 2011

So, let say you want to send data in the hostname table in the email body...

 
$to = "";//will send to the email you select from the database
$subject = "whatever";//you can write whatever the subject is in here
$message = "";//will be the html table with hostname data

$message .= "<table border = 0>";
echo "<table border = 0>";
echo "<tr><th>HostName</th></tr>;//If you want to send more data create another columns for it...
$message .= "<tr><th>HostName</th><th>Email</tr>";
//select your data
$sql = mysql_query("SELECT Email,PrimaryContact,Hostname FROM sm_table WHERE NewSetDate='2011-07-01' ORDER BY Hostname ASC");
//get all the rows
while($row2 = mysql_fetch_array($sql) or die(mysql_error())) {
echo "<tr><td>";
$message .= "<tr><td>";
echo $row['HostName'];//assuming HostName is the column you want to grab data from
$message .= $row['HostName'];
echo "</td><td>";
$message .= "</td><td>";
echo $row['Email'];
$to .= $row['Email'];
echo "</td></tr>";

echo "</table>";
$message .= "</table>";

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

// More headers
$headers .= 'From: <you@something.com>' . "\r\n";
$headers .= 'Cc: customers@example.com' . "\r\n";
mail($to,$subject,$message,$headers);
}

Ok, that works for me sending data in the body to the email address in the database.........

check the code i might miss something.....

goodluck

phpme1221 · July 1, 2011

Thanks IrOnMask,

It did not work, pretty much you the code provided

all it shows on the browser is "Hostname" in bold, no email as well. shucks

i am able to retrieve all the date on browser and send email with my own code but it sends it the number of records found, if it find 3 i get 3 emails 4 i get 4 emails so not sure

its a while loop so sends it out how many times the record matchs/found.

darkfreaks · July 1, 2011

<?php
$sql = mysql_query("SELECT Hostname, 
COUNT(Hostname)  FROM sm_table 
WHERE NewSetDate='2011-07-01' GROUP BY Hostname HAVING COUNT(Hostname) > 1"); 
//Selecting Hostnames with more than server
// output PHP loop here

?>

removed all white space. also removed AS because it was redundant and not needed.

phpme1221 · July 1, 2011

All thanks again but Its just not working. Dark, its not emailing. :'(

all i want is a to send email to the perspective owner of their servers thats all.

darkfreaks · July 2, 2011

the hostname isn't showing up because it isn't being escaped and parsed right in the body, i combined all your variables into one $body variable and escaped it with /n line breaks. and escaped the hostname variable in dots and double quotes , so it should work unless the MYSQL throws an error.

<?php
$que = mysql_query("SELECT Email,PrimaryContact,Hostname FROM sm_table WHERE NewSetDate='2011-07-01' ORDER BY Hostname ASC");
$res = mysql_query("SELECT DISTINCT Email FROM sm_table WHERE NewSetDate='2011-07-01' ORDER BY Hostname ASC");
$sql = mysql_query("SELECT Hostname, COUNT(Hostname)  FROM sm_table 
WHERE NewSetDate='2011-07-01' GROUP BY Hostname HAVING COUNT(Hostname) > 1"); 
$pc = 'PrimaryContact';
$hc = 'Hostname';
$em = 'Email';
.
$row[$hc] . '<br/>'. 'Email: ' . $row[$em] . '<br/> <br/>';

while($row2 = mysql_fetch_array($sql) or die(mysql_error())) {
$subject = "Server Registration";
$from = "emialerts@partners.org";
$headers = "From:" . $from;
$body="List of Servers:/n". $row2['Hostname']."
\nPlease click on link to register or cancel";

if ($row1 = mysql_fetch_assoc($res)or die(mysql_error())) {
$to = $row1[$em];

mail($to,$subject,$body,$headers);
}
}
?>

phpme1221 · July 2, 2011

thanks again dar, nope does not work

darkfreaks · July 2, 2011

please specify what is wrong , any errors :confused:

phpme1221 · July 2, 2011

no errors, just blank browser page no emails either

darkfreaks · July 2, 2011

<?php
$que = mysql_query("SELECT Email,PrimaryContact,Hostname FROM sm_table WHERE NewSetDate='2011-07-01' ORDER BY Hostname ASC");
$res = mysql_query("SELECT DISTINCT Email FROM sm_table WHERE NewSetDate='2011-07-01' ORDER BY Hostname ASC");
$sql = mysql_query("SELECT Hostname, COUNT(Hostname)  FROM sm_table 
WHERE NewSetDate='2011-07-01' GROUP BY Hostname HAVING COUNT(Hostname) > 1"); 
$pc = 'PrimaryContact';
$hc = 'Hostname';
$em = 'Email';
.
$row[$hc] . '<br/>'. 'Email: ' . $row[$em] . '<br/> <br/>';

while($row2 = mysql_fetch_array($sql) or die(mysql_error())) {
$subject = "Server Registration";
$from = "emialerts@partners.org";
$headers = "From:" . $from;
$body="List of Servers:/n". $row2['Hostname']."
\nPlease click on link to register or cancel";

if ($row1 = mysql_fetch_assoc($res)or die(mysql_error())) {
$to = $row1[$em];

if(mail($to,$subject,$body,$headers)){ echo "Message Successfully Sent";} else { echo "Message failed to deliver";}
}
}
?>

if your emails are being sent to the JUNK folder use something like PHPMAILER

now it should tell you if the email is sent or not.

Pikachu2000 · July 2, 2011

There's a parse error in the code.

$em = 'Email';
.  // <----- HERE
$row[$hc] . '<br/>'. 'Email: ' . $row[$em] . '<br/> <br/>';

phpme1221 · July 2, 2011

Hi pikachu

im getting this error now

Parse error: syntax error, unexpected '.' in /var/www/email.php on line 18

thanks

phpme1221 · July 2, 2011

Hi dark thanks again , but just not working

how do you send out mail to each individually and only recieve 1 that has all the server names listed?

Pikachu2000 · July 2, 2011

After removing the extra concatenation operator, I don't get any more parse errors. If you're still getting one, post the revised code.

phpme1221 · July 2, 2011

Hi Pikachu,

Thanks,

here is the code that displays it correctly on the browser, the problem is how to send it out in email to the owner of the servers with the their list of server they own on the email body.

<?php
$que = mysql_query("SELECT Email,PrimaryContact,Hostname FROM sm_table WHERE NewSetDate='2011-07-01' ORDER BY Hostname ASC");
$res = mysql_query("SELECT DISTINCT Email FROM sm_table WHERE NewSetDate='2011-07-01' ORDER BY Hostname ASC");
$sql = mysql_query("SELECT DISTINCT Hostname,Email, PrimaryContact FROM sm_table WHERE NewSetDate='2011-07-01' GROUP BY Hostname ASC");

$pc = 'PrimaryContact';
$hc = 'Hostname';
$em = 'Email';

while($row = mysql_fetch_assoc($sql) or die(mysql_error())) {
echo 'Primary Contact: ' . $row[$pc] . '<br/>' . 'Hostname: ' . $row[$hc] . '<br/>'. 'Email: ' . $row[$em] . '<br/> <br/>';
$smlist[] = $row['Hostname'];
print_r ($smlist);

$body = "List of Servers:";
$body .= join($smlist, ",");
$body .="\nPlease click on link to register or cancel";
$subject = "Server Registration";
$from = "emialerts@partners.org";
$headers = "From:" . $from;

if ($row1 = mysql_fetch_assoc($res)or die(mysql_error())) {
$to = $row[$em];
mail($to,$subject,$body,$headers);
}
?>

darkfreaks · July 2, 2011

your while loop is missing a bracket {}

IrOnMaSk · July 8, 2011

Are you able to send out email using php mail function?

Try giving it an email address in the $to without pulling from the database.

Or try just using while loop to grab the address and send it without any info in it.

$to = '';
$subject = 'email testing';
$message = 'see this?';
$query = "select Email from DATABASE";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)){
$to .= $row['Email']	

//end email here
$headers = "MIME-Version: 1.0" . "\r\n";
$headers .= "Content-type:text/html;charset=iso-8859-1" . "\r\n";

// More headers
$headers .= 'From: <me@whatever.com>' . "\r\n";
$headers .= 'Cc: myboss@example.com' . "\r\n";
mail($to,$subject,$message,$headers);
}

See if that even works. If not you're having SMTP problem...

phpme1221 · July 8, 2011

Thanks ironmask,

here is an example of my table

id email server

1 abc@abc.com test1

2 bcd@abc.com test211

3 cde@abc.com live222

3 cde@abc.com fun122

3 cde@abc.com hay123

2 bcd@abc.com top267

I want to send out an email to each perspective owner there list of servers

so; example based on the table above

abc@abc.com server = test1

cde#abc.com server=live222, fun122, hay123

bcd@abc.com server = top267 and test211

I hope you guys get it, i can send out email i know the mail() function for php its a matter of sending the information based the table i have

Sign In

Sending email wtih mysql qurey result on body

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information