Populate an html table from a joined query

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

Populate an html table from a joined query

Post by mikeoxlarge »

I'm having trouble with the following code,

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>";
But that just threw up an error
Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting ']'
and when I use

Code: Select all

$table .= "<tr><td>$row2[items.id]</td>";
it just gets id data from the location table, even though it gets the correct values for description and serialnumber from the items table, i'm guessing this is because there are id fields in both tables but how do I tell it which to get the data from??

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()
?>
Am I missing something obvious?


regards


Mike
mezise
Forum Newbie
Posts: 17
Joined: Tue Sep 18, 2007 4:38 am

Post by mezise »

Hi Mike,
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.
Yes, it is a known problem. When you use "*" and select everything (what is asking for trouble) from many sql tables, in a result sql engine returns 2 or more fields of the same name and you have correct access to these fields only using field's indexes. Using field's names as assoc-keys in array (result of mysql_fetch_array and mysql_fetch_assoc) you have access only to the last selected field of the same name.

The best programming custom is to select only fields you want to use. It saves PC's memory and saves troubles like yours one.
If you want to use 2 fields of the same name apply aliases, e.g.:

Code: Select all

SELECT
  items.id
  , location.id AS location_id
  , items.description
  , items.serialnumber
  , items.amend_date
FROM items
INNER JOIN location ON items.location = location.id
...
Michal
Post Reply