Emailing Results from mysql query

jnicastr

Hello, I need some help coding a script that does the following:

1) Query a table and grab all results from that table matching the criteria - This is the query that works:

SELECT * FROM table WHERE status='0'

2) Take all results and place into variables that can be inserted into text email

Example: lets say the results are 3 records, each record contains 4 fields.

3) Email - insert the results into an email

I've tried some things but not structured properly. Would like to see proper build of this....THANKS FOR ANY TIME AND HELP TO THIS.

laserlight

What have you tried?

jnicastr

well, what I tried was connecting to the database, querying, and then taking row results and inserting into the mail() function. In a sense, it worked...considering I got results in an email. The problem is, there is no format to the email...and I can't get the other fields from the row. The other issue is I need to be able to structure the body of the email....so rather than display values delimited by commas....I want to be able to insert the values into the body text of the email with some kind of presentation.

Very new...sorry....I'm like copying examples from books and online examples and learning as I go along.

jnicastr

hmmm, my reply didn't post. Retry...

So far I have connected to db, made my query and put results into the mail() function. But I'm not understanding the structure of the results...here's the code I started with (without the mail function):

<?php

//mysql user variables
$db_host = "localhost";
$db_user = "xxxxxx";
$db_pwd = "xxxxxx";
$db_name = "database";

//start the database connection
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);

//grab all rows from table where state is "new"
$sql = "SELECT * FROM table WHERE status='0'";
$query = mysql_query($sql);
while($row = mysql_fetch_array($query, MYSQL_ASSOC))
{
echo "Tracking ID :{$row['trackid']} " .
"Category : {$row['category']} " .
"Created On : {$row['dt']} ";
}

?>

So using the above, I started putting the $row variables into mail(), and it worked somewhat but I have no idea what I'm doing there.

sarah

Without including any personal information, could you include the code that sends the email?

jnicastr

Actually, I have something working now (after spending much of my time in trial and error). Here's what is working... - The main obstacle (for me) was figuring out how to grab all the results from the 'while' loop and put it into one string that I can call outside the loop.

<?php

//mysql user variables
$db_host = "localhost";
$db_user = "xxxxx";
$db_pwd = "xxxxx";
$db_name = "table";

//start the database connection
mysql_connect($db_host, $db_user, $db_pwd);
mysql_select_db($db_name);

//grab all rows from the table where state is "new" and put into array
$string = "";
$sql = "SELECT * FROM `table` WHERE status='0'";
$query = mysql_query($sql);


//loop the fetch and concactenate into string
while ($row = mysql_fetch_object($query)) { 
$string .= "ID: ".$row->trackid."<br>"."Date Created: ".$row->dt."<br>"."Name of Submitter: ".$row->name."<br><br>"; 
}


//email parameters

$to  = 'email@domain.com' . ', '; // note the comma
$to .= '';

$subject = "subject";

// To send HTML mail, the Content-type header must be set
$headers  = 'MIME-Version: 1.0' . "\r\n";
$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
$headers .= 'From: Whoever <whoever@domain.com>' . "\r\n";

$message = "Hello, 

some message some message some message: <br><br>

$string";

mail($to,$subject,$message,$headers);


?>

sarah

Looks like you have it under control. Good luck!

Trisha

@ Thanks so much for posting.... at least I got somewhere!! I'm able to get results from database and mail using SMTP.GMAIL.COM server....
Problem is, in the mail, aside from the info it also prints all the formatting tags etc.. like for instance.

What I really want to be doing is saving my results as a csv file and sending that file through mail as an attachment, so that the receiver can open it in excel !! Is there a way to modify jnicastr's code to do that?

thanks in advance!