How can i send email at the user (group by email) with all the details? (tools,model,sn)
user test@test.com will receive the email with tools A & B
user bbbb@test.com will receive the email with tools D
user pppp@test.com will receive the email with tools F
<?php
$link = mysql_connect($DB_host, $DB_user, $DB_password);
if (!$link) {
die ('Failed to connect: ' . mysql_error());
}
$db_selected = mysql_select_db($DB_name, $link);
if (!$db_selected) {
die ("Db errorr: " . mysql_error());
}
app.php
<?php
require 'config.php';
require 'connect.php';
// sending query
$result = mysql_query("SELECT ID,TOOLS,SN,EXPIRATION_DATE,USER_EMAIL FROM table_name WHERE MONTH(EXPIRATION_DATE) = 8 AND YEAR(EXPIRATION_DATE) = YEAR(now()) ");
if (!$result) {
die("Query to show fields from table failed");
}
$fields_num = mysql_num_fields($result);
echo "<table border='1'><tr>";
echo "<table class='hovertable'>";
// printing table headers
for($i=0; $i<$fields_num; $i++)
{
$field = mysql_fetch_field($result);
echo "<th>{$field->name}</th>";
}
echo "</tr>\n";
// printing table rows
while($row = mysql_fetch_row($result))
{
echo "<tr>";
foreach($row as $cell)
echo "<td>$cell</td>";
echo "</tr>\n";
}
mysql_free_result($result);
?>
?>
Actually this query show all the tools with expiration date on August
2014…i have to send email for expiration reminder to all the user
(there are some user that have 2 or more tools so they have to receive
only 1 mail with a description of all their tools)
Thanks
Change the query to order by user email address, then loop through the results. Each time you read a new row, add the tool information to a blank array, when the email address changes send the email containing the details in the array and clear the array. Pseudo-code:
$x = array();
$oldemail = "";
while $row = $db->fetch() {
if ($row['email'] <> $oldemail) {
if ($oldemail <> "") {
// build email body text based on contents of $x array, list of tools for this email
// send email to $oldemail
}
$oldemail = $row['email']; // or whatever the column name is
// and clear the array - turns out just re-declaring it clears it
$x = array();
}
// add the tool details to the array you're building up
$x[] = array($row['tools'], $row['model'], $row['sn'], $row['expiration_date']);
}
if (count($x)>0) {
// send the email for the remaining details
}
You should also look at moving from the old mysql calls to use PDO or mysqli.
Thanks for the reply…actually i’m stuck on your suggested code
<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=dbname", 'user', 'password');
$sql = "SELECT ID,TOOLS,MODEL,SN,EXPIRATION_DATE,USER_EMAIL FROM table_name WHERE ...... ORDER BY USER_EMAIL";
$q = $conn->query($sql);
$q->setFetchMode(PDO::FETCH_ASSOC);
//$table_fields = $q->fetchAll(PDO::FETCH_COLUMN);
} catch (PDOException $pe) {
die("Could not connect to the database $dbname :" . $pe->getMessage());
}
$x = array();
$oldemail = ""; //??
while $row = $db->fetch() {
if ($row['USER_EMAIL'] <> $oldemail) {
if ($oldemail <> "") {
// build email body text based on contents of $x array, list of tools for this email
// send email to $oldemail
}
$oldemail = $row['USER_EMAIL'];
unset($foo);
}
// add the tool details to the array you're building up
}
?>
COLUMNS THAT I WANNA USE:
USER_EMAIL → contains all the emails
Where does it fail, specifically which bit is causing trouble? Note also that it’s pseudo-code, intended more as an answer to how I read your initial query as how to group the different machines together so that a user with more than one machine will get them grouped on a single email, so you’ll have to translate it into proper PHP code yourself.
Just for testing i’ve write down this code (mixed with your example)
Now the systems send the email but only at the last record on the array…what is wrong?
Thanks
$x = array();
$oldemail = "";
while ($row = mysql_fetch_assoc($result)) {
if ($row['ENGINEER'] <> $oldemail) {
if ($oldemail <> "") {
// build email body text based on contents of $x array, list of tools for this email
echo "<pre>";
print_r($x);
echo "</pre>";
$email = $row["ENGINEER"];
$model = $row["MODEL"];
$maker = $row["MAKER"];
$sn = $row["SN"];
$expiration_date = $row["EXPIRATION_DATE"];
$status = $row["SATUS"];
$to = ", " . $email . " ";
$subject = "Reminder" . $mese . " recall";
$message = "Reminder For: " . $model . "" . $maker . "" . $sn . " is going to expire on " . $expiration_date . "";
$from = "";
$headers = "From:" . $from;
mail($to,$subject,$message,$headers);
// send email to $oldemail
}
$oldemail = $row['ENGINEER']; // or whatever the column name is
// and clear the array - turns out just re-declaring it clears it
$x = array();
}
// add the tool details to the array you're building up
$x[] = array($row['MAKER'], $row['MODEL'], $row['SN'], $row['EXPIRATION DATE']);
}
if (count($x)>0) {
// send the email for the remaining details
echo "<pre>";
print_r($x);
echo "</pre>";
$email = $row["ENGINEER"];
$model = $row["MODEL"];
$maker = $row["MAKER"];
$sn = $row["SN"];
$expiration_date = $row["EXPIRATION_DATE"];
$status = $row["SATUS"];
$to = ", " . $email . " ";
$subject = "Reminder " . $mese . " recall";
$message = "Reminder For: " . $model . "" . $maker . "" . $sn . " is going to expire on " . $expiration_date . "";
$from = "";
$headers = "From:" . $from;
mail($to,$subject,$message,$headers);
}
?>
Well, you’re not using the array to build the message contents, you’re using the most-recent values of $row - which I think will actually be the first row for the next engineer, not the last row of the previous one. You need to build up the message body from the contents of the $x array, not $row.
$x = array();
$oldemail = "";
while ($row = mysql_fetch_assoc($result)) {
if ($row['ENGINEER'] <> $oldemail) {
if ($oldemail <> "") {
// build email body text based on contents of $x array, list of tools for this email
echo "<pre>";
print_r($x);
echo "</pre>";
$email = $row["ENGINEER"];
$model = $row["MODEL"];
$maker = $row["MAKER"];
$sn = $row["SN"];
$expiration_date = $row["EXPIRATION_DATE"];
$status = $row["SATUS"];
$to = ", " . $email . " ";
$subject = "Reminder" . $mese . " recall";
if count($x)>1 {
$message = "Reminder For: " . $model . "" . $maker . "" . $sn . " is going to expire on " . $expiration_date . "";
} else {
$message = "Reminders for the following items: \n";
foreach ($x as $item) {
$message .= $item[1] . ' ' . $item[0] . ' ' . $item[2] . ' expires on ' . $item[3] . '\n';
}
}
$from = "";
$headers = "From:" . $from;
mail($to,$subject,$message,$headers);
// send email to $oldemail
}
$oldemail = $row['ENGINEER']; // or whatever the column name is
// and clear the array - turns out just re-declaring it clears it
$x = array();
}
// add the tool details to the array you're building up
$x[] = array($row['MAKER'], $row['MODEL'], $row['SN'], $row['EXPIRATION DATE']);
}
if (count($x)>0) {
// send the email for the remaining details
echo "<pre>";
print_r($x);
echo "</pre>";
$email = $row["ENGINEER"];
$model = $row["MODEL"];
$maker = $row["MAKER"];
$sn = $row["SN"];
$expiration_date = $row["EXPIRATION_DATE"];
$status = $row["SATUS"];
$to = ", " . $email . " ";
$subject = "Reminder " . $mese . " recall";
/// and the same condition here, if there's more than one item, loop around them to create the message.
$message = "Reminder For: " . $model . "" . $maker . "" . $sn . " is going to expire on " . $expiration_date . "";
$from = "";
$headers = "From:" . $from;
mail($to,$subject,$message,$headers);
}
?>
Ok now i’ve made some adjustment and it work
Now my problem is that i’m not able to extract data from the array…i’m trying to use this:
$tools = $x['TOOLS'];
but the result is blank
How can i extract data from the array $x ??
Thanks
actually on the $messagge i’ve inserted this code print_r( $x, true ) but with this i can’t format the email body…so i have to extract all the data into the array (for the actual email) and put it into a variables
Keep in mind that it’s a two-dimension array - that is, for each ‘row’ in the array, you have multiple columns. So I would expect that the first set of results would be $x[0][0] for the maker, $x[0][1] for the model, and so on, then the second set of results would be $x[1][0] for the second maker, $x[1][1] for the model, and so on.
When you add to the array you can add index names rather than using the numbers above. Lots of documentation on PHP arrays around, I’m not familiar enough to go into more detail.
