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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
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

Post 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
Last edited by mikeoxlarge on Wed Aug 22, 2007 7:45 am, edited 1 time in total.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

Post your SQL and PHP code, please. So, we can help you.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
dude81
Forum Regular
Posts: 509
Joined: Mon Aug 29, 2005 6:26 am
Location: Pearls City

Post by dude81 »

check the format of issued_date and now
mikeoxlarge
Forum Newbie
Posts: 6
Joined: Tue Aug 21, 2007 5:50 am

Post 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]
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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
There 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

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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.
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
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Post by aceconcepts »

User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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 ;)
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply