Member Avatar for mark2326l

I have a MySQL DB with 2 Tables in particular, "Properties" and "Tenants". Each Table has a unique address Field "propAddress" & "TenAddress".
I have a PHP page with a repeating reigon that lists all properties in the DB.
What I'm trying to do is list all the tenants info to the matching property based on the addresses.
I have it in my mind I should be using the "Where Clause".
Any advise will be greatly appreciated.

mysql_select_db($database_rent, $rent);
$query_RsProp = "SELECT * FROM Properties ORDER BY PropId ASC";
$query_limit_RsProp = sprintf("%s LIMIT %d, %d", $query_RsProp, $startRow_RsProp, $maxRows_RsProp);
$RsProp = mysql_query($query_limit_RsProp, $rent) or die(mysql_error());
$row_RsProp = mysql_fetch_assoc($RsProp);

if (isset($_GET['totalRows_RsProp'])) {
  $totalRows_RsProp = $_GET['totalRows_RsProp'];
} else {
  $all_RsProp = mysql_query($query_RsProp);
  $totalRows_RsProp = mysql_num_rows($all_RsProp);
}
$totalPages_RsProp = ceil($totalRows_RsProp/$maxRows_RsProp)-1;

$colname_Recordset1 = "-1";
if (isset($_GET['TenantId'])) {
  $colname_Recordset1 = $_GET['TenantId'];
}
mysql_select_db($database_rent, $rent);
$query_Recordset1 = sprintf("SELECT Properties.Address, Properties.PropId, Tenants.TenantId, Tenants.FirstName1, Tenants.LastName1, Tenants.FirstName2, Tenants.LastName2, Tenants.Phone1, Tenants.Phone2, Tenants.ContractStart, Tenants.ContractEnd, Tenants.RentDueDate, Tenants.Email, Tenants.ContractLink, Tenants.ApartmentNo, Tenants.TenantAdress FROM Properties, Tenants WHERE Properties.PropId AND Properties.Address=Tenants.TenantAdress AND %s", GetSQLValueString($colname_Recordset1, "int"));
$Recordset1 = mysql_query($query_Recordset1, $rent) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
<style type="text/css">
body {
    background-color: #E2E8FF;
}
</style>
</head>

<body>
<form id="form1" name="form1" method="post" action="">
  <?php do { ?>
    <table width="800" border="0">
      <tr>
        <td width="114"><img src="<?php echo $row_RsProp['ImageLink']; ?>" width="150"/></td>
        <td width="625"><p><b><span style='font-size: 20px;' ><?php echo $row_RsProp['Address']; ?> </span></b><br /><?php echo $row_RsProp['City']; ?>, <?php echo $row_RsProp['State']; ?> <?php echo $row_RsProp['Zip']; ?><br />
            <?php echo $row_Recordset1['FirstName1']; ?> <?php echo $row_Recordset1['LastName1']; ?><br />
        </p>
        <p>&nbsp;</p></td>
        <td width="20">&nbsp;</td>
        <td width="23">&nbsp;</td>
      </tr><br /><br /> <br />
    </table>
    <?php } while ($row_RsProp = mysql_fetch_assoc($RsProp)); ?><br /><br />

</form>
  • 3 Contributors
  • 4 Replies
  • 228 Views
  • 2 Hours Discussion Span
  • Latest Post Latest Post by cigoL..:)
Member Avatar for minitauros

So which fields are the matching fields? Suppose that you have two tables:

persons (id, name)
addresses (id, person_id, address)

Each person can have one or multiple addresses, but each address is bound to only one person. Then you could execute the following query to select a person together with all his addresses:

SELECT persons.id,
    persons.name,
    addresses.id,
    addresses.address
    FROM addresses
    JOIN persons ON addresses.person_id = persons.id
    WHERE persons.id = [fill in a person ID]

This would select all addresses that are connected to a person (JOIN indicates that there MUST be a match in persons) and returns a list of addresses + persons. For example if you have one person named "John", who has 3 addresses, it would return 3 rows, each with persons.name "John", but different addresses.

Is this what you were looking for?

In your case you could get something like:

SELECT Properties.Address, 
    Properties.PropId,
    Tenants.TenantId,
    Tenants.FirstName1,
    Tenants.LastName1,
    Tenants.FirstName2,
    Tenants.LastName2,
    Tenants.Phone1,
    Tenants.Phone2,
    Tenants.ContractStart,
    Tenants.ContractEnd,
    Tenants.RentDueDate,
    Tenants.Email,
    Tenants.ContractLink,
    Tenants.ApartmentNo,
    Tenants.TenantAdress
    FROM Properties
    JOIN Tenants ON Properties.Address = Tenants.TenantAddress
    WHERE %s
Edited by minitauros
Member Avatar for mark2326l

So which fields are the matching fields?
"propAddress" & "TenAddress" if the tenants address matches the property address I would like to have the tenants info display on the corresponding property, which is displayed in a repeating region.
I'll order by DESC so if a new tenant moves in it will be the one displayed, but will still have records of older ones.
I'll take a look at your suggestion.
Thank you.

Member Avatar for minitauros

Well yea, my suggestion should work for that, I think :). Note that if you use "JOIN", there MUST be a match in order for a row to be retrieved. If you don't mind if there is a match and want to retrieve it either way, you could use LEFT JOIN.

Member Avatar for cigoL..:)

I would suggest that you use INNER JOIN.

SELECT * FROM Properties INNER JOIN on Properties.propAddress. = Tenants.tenAddress WHERE [CONDITION]=[VALUE]

i hope this will help..

-Alex

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.