Page 1 of 1

php page to email data to users from a mysql database in htm

Posted: Wed Aug 22, 2007 7:08 am
by mikeoxlarge
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

Posted: Wed Aug 22, 2007 7:10 am
by VladSun
Post your SQL and PHP code, please. So, we can help you.

Posted: Wed Aug 22, 2007 7:13 am
by dude81
check the format of issued_date and now

Posted: Wed Aug 22, 2007 7:21 am
by mikeoxlarge
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 ;
[/syntax]

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]

Posted: Wed Aug 22, 2007 7:34 am
by VladSun
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 LOOP

Posted: Wed Aug 22, 2007 7:38 am
by mikeoxlarge
how does that differentiate which records get emailed to each user?

each user should only get the records that are allocated to them, I'll give it a blast anyway


thanks


Mike

Posted: Wed Aug 22, 2007 7:56 am
by VladSun
[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:

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
}
?>
Using * in the SQL query with JOINs is ambiguous as far as I know.

Posted: Wed Aug 22, 2007 8:29 am
by aceconcepts

Posted: Wed Aug 22, 2007 8:37 am
by VladSun
aceconcepts wrote:http://www.w3schools.com/sql/sql_join.asp

It's a very helpful page :D
Absolutely :)
Excuse me for this inappropriate, even useless, piece of advice ;)