there are two tables, items and location, each table has a field called id (which aren't related in any way) the tables are inner joined and I'm trying to populate an html email from the results of the query.
The query seems to be working fine however it is pulling the id field from the wrong table, its pulling id from the location table instead of the items table. How do I get it to populate the html table from the correct database table.
I've tried
Code: Select all
$table .= "<tr><td>$row2[items.id]</td>";and when I useParse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting ']'
Code: Select all
$table .= "<tr><td>$row2[items.id]</td>";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 date 7 days ago
$sevendaysago = date("Y-m-d H:i:s", strtotime("-7 days"));
// Get Engineer list into array
$result = mysql_query("SELECT * FROM location WHERE (status_preset = 'ALLOCATED TO ENGINEER' or status_preset = 'DISPATCHED TO SITE') AND (email <> '')");
// Loop through engineers getting stock more than 7 days old
while($row = mysql_fetch_array($result))
{
$result2 = mysql_query("SELECT
*
FROM
items
Inner Join location ON items.location = location.id
WHERE
items.currentloc = 'TRUE' AND
items.location = '".$row['id']."' AND
DATE_ADD(amend_date, INTERVAL 7 DAY) < now() AND
location.email <> ''");
$row2 = mysql_fetch_array($result2);
// Build array into readable format $message
if ($row2['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=\"750\" 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=\"100\"><strong>Serial Number</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[serialnumber]</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 = "Daily 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()
?>regards
Mike