Using Information from Multiple Tables

[run2web]

I know that I have seen this in other posts but I can never make it work so I am asking directly.

I have a bit of code that looks like this:

<?php echo "$LastName,  $FirstName,  $SectionNumber $LotNumber $LotLocation"; ?>

The variables LastName and FirstName come from table 1.

The variables SectionNumber, LotNumber and LotLocation come from table 2.

I have queried the tables to pull in the information from the tables with the following:

$sql_interred = "SELECT *  FROM $interred WHERE ID = '$ID'";

$result_interred = @mysql_query($sql_interred,$connection) or die("Couldn't execute query.");

while ($row = mysql_fetch_array($result_interred)) {
	$ID = $row['ID'];
	$PlotID = $row['PlotID'];
   	$FirstName  = $row['FirstName'];
	$LastName = $row['LastName'];
	$DOB = $row['DOB'];
	$DOD = $row['DOD'];
   	$IntermentDate  = $row['IntermentDate'];
	$ServiceDate = $row['ServiceDate'];
	$FunDirID = $row['FunDirID'];
	$Remains = $row['Remains'];
   	$Relocated  = $row['Relocated'];
}

$sql_plots = "SELECT * FROM $plots ORDER BY SectionNumber"
;

$result_plots = @mysql_query($sql_plots,$connection) or die("Couldn't execute query.");
	while ($row = mysql_fetch_array($result_plots)) {
		$ID = $row['ID'];
		$SectionNumber = $row['SectionNumber'];
   		$LotNumber  = $row['LotNumber'];
		$LotLocation = $row['LotLocation'];
}

I have a header at the top of the page that is supposed to display the information in the database (using the echo statement at the top of this post) that shows which record is being modified but the result is this:

Modify Interred Person: , , 17 57 4

So basically it is showing the information from the table 2 but not table 1.

Any ideas where I am going wrong here?

This is happening on other pages as well when I try to show information based on 2 tables.

BTW, the tables are being updated fine, just the header info is not showing.

Any help will be greatly appreciated.

Thanks in advance.

[bastien]

is to add a column

personID to the table with the personal details(interred table), make this the primary key

add the same column to the plot table

then change the $sql to

<?
$sql = "select interred.firstname, interred.lastname, plots.SectionNumber, plots.LotNumber, plots.LotLocation from interred, plots where interred.personid = plots.personid"

$results=mysql_query($sql,$conn) or die("no db available");

if(!$results){
echo "problem with query. no info for the person found";
}else{
while ($rows=mysql_fetch_array($results)){
$firstname = $rows['firstname'];
$lastname = $rows['lastname'];
$sectionno=$rows['sectionnumber'];
$lotno=$rows['lotnumber'];
$lotlocal=$rows['lotlocation'];

   echo "$lastname,  $firstname,  $sectionno, $lotno $lotLocal";

}

the only parameter to make this work is the personid, which you can get from a simple query to the one table based on lastname or whatever other criteria you have...

hth

[run2web]

Thank you! I will give that a go today.

I didn't think about adding another field to the database, that makes sense.