php page to email data to users from a mysql database in htm
Moderator: General Moderators
-
mikeoxlarge
- Forum Newbie
- Posts: 6
- Joined: Tue Aug 21, 2007 5:50 am
php page to email data to users from a mysql database in htm
right, I have two tables in my stock database, this is the last little bit I'm stuck on, somebody must have the answer???????
USERS:
uniqueid
id
email
tel
ITEMS:
uniqueid <not linked to uniqueid in users>
id <same as id in users>
description
amend_date
blah
blah
blah
What I need to do is create an HTML email with a table of items that each user currently has (or items where amend date < 7 days ago) this will automatically remind people that they have stock outstanding, I assume the best way to go would be as follows
join both tables on id field (username)
query joined table to only get data where issued_date < (now() - 7days)
cycle through the email addresses sending a table of data for each email address
This works in theory but I'm really struggling with it, is there any php wizards out there that can shed some light on this???
my project is nearly complete and this is the last little bit thats stopping us switching off the MSaccess based system (limited to two users) and going to a full intranet based system.
Any help would be greatly appreciated
regards
Mike
USERS:
uniqueid
id
tel
ITEMS:
uniqueid <not linked to uniqueid in users>
id <same as id in users>
description
amend_date
blah
blah
blah
What I need to do is create an HTML email with a table of items that each user currently has (or items where amend date < 7 days ago) this will automatically remind people that they have stock outstanding, I assume the best way to go would be as follows
join both tables on id field (username)
query joined table to only get data where issued_date < (now() - 7days)
cycle through the email addresses sending a table of data for each email address
This works in theory but I'm really struggling with it, is there any php wizards out there that can shed some light on this???
my project is nearly complete and this is the last little bit thats stopping us switching off the MSaccess based system (limited to two users) and going to a full intranet based system.
Any help would be greatly appreciated
regards
Mike
Last edited by mikeoxlarge on Wed Aug 22, 2007 7:45 am, edited 1 time in total.
-
mikeoxlarge
- Forum Newbie
- Posts: 6
- Joined: Tue Aug 21, 2007 5:50 am
feyd | Please use
Location table
[/syntax]
feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
In the following tables id.location and location.items are the username for each record.
Items table
[syntax="sql"]CREATE TABLE `items` (
`uniqueid` int(11) NOT NULL auto_increment,
`id` varchar(20) default NULL,
`title` varchar(50) default NULL,
`description` varchar(50) default NULL,
`status` varchar(20) default NULL,
`notes` mediumtext,
`creation_date` date default NULL,
`amend_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`location` varchar(40) default NULL,
`job_number` varchar(255) default NULL,
`serialnumber` varchar(255) default NULL,
`udf3` varchar(255) default NULL,
`amended_by` varchar(255) default NULL,
`itemtype` varchar(20) default NULL,
`itmvalue` decimal(19,4) default NULL,
`fault_description` varchar(255) default NULL,
`test_results` varchar(255) NOT NULL,
`quantity` int(11) default NULL,
`qtyavail` int(11) default NULL,
`currentloc` varchar(20) default NULL,
`reorderpoint` int(11) default NULL,
`stdorderqty` int(11) default NULL,
KEY `ix_items_autoinc` (`uniqueid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14440 ;Location table
Code: Select all
CREATE TABLE `location` (
`uniqueid` int(11) NOT NULL auto_increment,
`id` varchar(40) NOT NULL,
`name` varchar(50) default NULL,
`TEL_` varchar(20) NOT NULL,
`email` varchar(40) NOT NULL,
`postcode` varchar(8) NOT NULL,
`status_preset` varchar(30) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `LocationIDIndex` (`id`),
KEY `ix_location_autoinc` (`uniqueid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1037 ;Code: Select all
<?php
// Create Connection
$dbhost = 'localhost';
$dbuser = 'stockcontrol';
$dbpass = 'stockcontrol';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
$dbname = 'stockcontrol';
mysql_select_db($dbname);
// Get data into array
$result = mysql_query(SELECT * FROM location inner join items on location.id = items.location where (location.status_preset = 'ALLOCATED TO ENGINEER' or location.status_preset = 'DISPATCHED TO SITE') AND (location.email <> '') AND (currentloc = 'TRUE') AND (datediff(now(),amend_date) > 7) AND (status = 'AVL' OR status = 'ALL' OR status = 'ALLOCATED TO ENGINEER' OR status = 'ALLOCATED TO ENGINEE' OR status = 'DISPATCHED TO SITE') order by location.email);
// get all data in to an array
$row = mysql_fetch_array($result)
{
[b]ITS THIS WHILE LOOP I CANT FIGURE[/b]
// Build array into readable format $message
if ($row['id']<>""){
$table = "<html>";
$table .= "<p>Hi $row[name],</p>";
$table .= "<p>The following is a list of stock that was allocated to you 7 or more days ago.<br>";
$table .= "Please could you check your current stock and notify the stores with any amendments required.</p>";
$table .= "<br>";
$table .= "<br>";
$table .= "<table width=\"600\" border=\"1\">"; // Start the table
$table .= "<tr>";
$table .= "<td width=\"100\"><strong>Item No.</strong></td>";
$table .= "<td width=\"350\"><strong>Description</strong></td>";
$table .= "<td width=\"200\"><strong>Date Issued</strong></td>";
while($row2 = mysql_fetch_array($result2))
{
$table .= "<tr><td>$row2[id]</td>";
$table .= "<td>$row2[description]</td>";
$table .= "<td>$row2[amend_date]</td>";
$table .= "</tr>";
}
$table .= "</table>"; // End the table
$table .= "</html>";
echo $table;
//ini_set("SMTP","localhost");
//ini_set("sendmail_from", "admin@localhost");
//send message
$to = $row['email'];
$subject = "Weekly Stock Check";
$message = "$table";
$from = "stores@comms-care.com";
if (strtoupper(substr(PHP_OS,0,3)=='WIN'))
$eol="\r\n";
elseif (strtoupper(substr(PHP_OS,0,3)=='MAC'))
$eol="\r";
else
$eol="\n";
// Now append $eol variable with header
$headers = "From: ".stripslashes($from)." < ".stripslashes($from).">".$eol;
//specify MIME version 1.0
$headers .= "MIME-Version: 1.0".$eol;
//unique boundary
$boundary = md5(uniqid(time()));
//tell e-mail client this e-mail contains//alternate versions
$headers .= "Content-Type: multipart/alternative" .
"; boundary =".$boundary.$eol;
//message to people with clients who don't
//understand MIME
$headers .= "This is a MIME encoded message.".$eol;
//HTML version of message
$headers .= "--".$boundary.$eol .
"Content-Type: text/html; charset=ISO-8859-1".$eol .
"Content-Transfer-Encoding: 8bit".$eol;
if (mail(stripslashes($to), stripslashes($subject), stripslashes($message), $headers)) {
echo $row['name'];
echo("<p>Email successfully sent!</p>");
} else {
echo $row['name'];
echo("<p>Message delivery failed...</p>");
}
}
}
// close connection
$conn = mysql_close()
?>[/b]feyd | Please use
Code: Select all
,Code: Select all
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]mikeoxlarge wrote:Code: Select all
$row = mysql_fetch_array($result) { ITS THIS WHILE LOOP I CANT FIGURE
Code: Select all
while ($row = mysql_fetch_array($result))
{
THE WHILE LOOPThere are 10 types of people in this world, those who understand binary and those who don't
-
mikeoxlarge
- Forum Newbie
- Posts: 6
- Joined: Tue Aug 21, 2007 5:50 am
[s]I think you should use LEFT JOIN instead of INNER JOIN in order to have all ITEMS per LOCATION[/s]. Then you have to generate the e-mail HTML contents for each user - something like this:
Using * in the SQL query with JOINs is ambiguous as far as I know.
Code: Select all
<?
$result = mysql_query("SELECT * FROM location INNER join items on location.id = items.location where (location.status_preset = 'ALLOCATED TO ENGINEER' or location.status_preset = 'DISPATCHED TO SITE') AND (location.email <> '') AND (currentloc = 'TRUE') AND (datediff(now(),amend_date) > 7) AND (status = 'AVL' OR status = 'ALL' OR status = 'ALLOCATED TO ENGINEER' OR status = 'ALLOCATED TO ENGINEE' OR status = 'DISPATCHED TO SITE') order by location.email");
// get all data in to an array
$prev_id = 0;
while ($row = mysql_fetch_array($result)
{
if ($row['location_id'] != $prev_id)
{
if ($prev_id)
{
$table .= "</table>"; // End the table
$table .= "</html>";
// Do mail() $table to $row['email'] here
$table = "";
}
$table .= "<html><body>";
$table .= "<p>Hi {$row[name]},</p>";
$table .= "<p>The following is a list of stock that was allocated to you 7 or more days ago.<br>";
$table .= "Please could you check your current stock and notify the stores with any amendments required.</p>";
$table .= "<table width=\"600\" border=\"1\">"; // Start the table
$table .= "<tr>";
$table .= "<td width=\"100\"><strong>Item No.</strong></td>";
$table .= "<td width=\"350\"><strong>Description</strong></td>";
$table .= "<td width=\"200\"><strong>Date Issued</strong></td>";
$table .= "<tr>";
$prev_id = $row['id'];
}
$table .= "<tr>";
$table .= "<td>{$row['item_id']}</td>";
$table .= "<td>{$row['description']}</td>";
$table .= "<td>{$row['amend_date']}</td>";
$table .= "</tr>";
}
if ($prev_id)
{
$table .= "</table>"; // End the table
$table .= "</html>";
// Do mail() $table to $row['email'] here
}
?>
Last edited by VladSun on Wed Aug 22, 2007 8:38 am, edited 2 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London