Hi,
I'm having sort of a weird issue with a table join and the data that gets displayed. On one page where the table join query is executed and the data is called in echo statements, it works fine. I am asking for 2 fields from table 1 (bldgs) and if there is a joining record from table 2 (forlease), then display 1 field from that table. The 2 common field names these table share are the primary id for the first table, AKA "bldg_id". Here is the script for the successful query;
$query = "select * from bldgs left join forlease on forlease.bldg_id = bldgs.bldg_id ";
//then call the query and display it
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
$numofrows = mysql_num_rows($result);
echo "<TABLE BORDER=\"0\">\n";
echo "<TR bgcolor=\"#E9F0F3\"><TD>Name</TD></TR>\n";
for($i = 0; $i < $numofrows; $i++) {
$row = mysql_fetch_array($result); //get a row from our result set
if($i % 2) { //this means if there is a remainder
echo "<TR bgcolor=\"#F0F0F0\">\n";
} else { //if there isn't a remainder we will do the else
echo "<TR bgcolor=\"white\">\n";
}
echo '<TD><img src=../usrfiles/properties/'.$row['pix'].' width=75></TD><TD>'.$row['address'].'</TD><TD>'.$row['rent'].'</TD><TD><a href="properties/propview.php?bldg_id='.$row['tmk'].'">View</a></TD>';
echo "</TR>\n";
}
//now let's close the table and be done with it
echo "</TABLE>\n";
Ok, as you can see there is an ahref going on with the passed variable as "tmk". Once onto that page, I am performing sql queries like this to pull data from the bldgs table;
$result = mysql_query("SELECT * FROM bldgs WHERE bldg_id = '$bldg_id'");
$myrow = mysql_fetch_array($result);
echo "".$myrow["propname"];
and to pull data from the forlease table I am doing like this;
$sqlquery = "select * from forlease WHERE bldg_id = '$bldg_id' ";
$result = mysql_query($sqlquery) or die('Query failed: ' . mysql_error());
$rentrow = mysql_fetch_array($result);
$numrows = mysql_num_rows($result);
echo "".$rentrow["rent"];
Now heres the funky part. Lets say that record 1 from the bldgs table has a joined record from the forlease table and I pull the data onto a page from both tables, everything shows up fine. Now record 2 from the bldgs table is a standalone record with no joined record from the forlease table; nothing shows up on the page at all. Not even from the bldgs table, where there is data.
Now why is it the first script will behave normally and pull the data from both sources correctly but not on the second example? Whats wrong with my query? :glare:
