[RESOLVED] Help With Data From Two Tables Please

[whodigs]

I am attempting to work with my first normalized database (everything else I have made was simple little PHP projects that I never had the need for it).

Basically I am trying to pull data from multiple tables that needs to match up. Right now I have 10 results per page and had no problem getting that to work. I have tried a few approaches to pull the second tables data and match it to the first query.

Basically the end result for this is to manage rental properties.

"properties" is the main table that handles most the information. It has about 50 rows so I am not sure you need all the rows, but if you do let me know.

The second table has floor plans. I named this table "floorplans". It has several rows:

PlanID
PropID - this is the same one from the properties table that they both share
PlanName
Size
Bedrooms
Bathrooms
Den
Price
PriceTo
Deposit
SqFt
UnitImg

I am attempting to build a cataloging system and display the results as a set on the page. I am listing 10 items per page with various sub info on that page as well. Most of this information is in the primary 'properties' table, but I would also like to be able to list items from the sub table that match the primary ID all on the same page. A detailed page containing all the information on the subject would be easier to do since I could just call the ID from the $_GET on two separate querrys but I am trying to make a page to give previews before clicking through.

Basically the table in html is aiming to look like:

<--All from Primary Table--><---In the floorplan table--->
+----+-----------------------------------------------------------------+
| Image| 1 ABC DEF St     | Floorplan information | Price  |
|         | 44 Evergreen      | aka how many            |Info    |
|         | Something          |  rooms                         |         |
+----+----------------------------------------------------------------+ 

Is there some way to accomplish displaying the data how I am trying? With a join can I do all 5 rows that share the same PropID? If so how would I call the multiple fields that would be associated. For example if I joined the tables and a PropId had 4 PlanId's associated is it even possible to call all 4 rows of data and if so how.

I appreciate all the help.

[laserlight]

I would like to say that what you call "rows" are more appropriately called "columns" or "fields".

What is the relationship between the properties and floorplans tables?

[whodigs]

Many to one. Anywhere from 2 to 7 floorplans per property.

[whodigs]

Any help with this would be greatly appreciated. I still can't seem to find a way to accomplish what I am looking for.

[shu]

You need to use JOIN in your query, probably an OUTER JOIN will do. Take a look at the manual for JOINs for whatever database you are using.

[whodigs]

My main question then shu would be with a join how would I call from the floorplans table? Since it has many to one relationship with the properties can I still pull floor plans 2-7 or whatever associated with it. If so how would this be done?

$row->PlanId[0] or [1] and so on?

[shu]

Not quit sure what you mean by $row->PlanID[0], [1]. Are you referring to the columns (fields) that would be returned from the floorplans table?

SELECT a.*, b.floorplan_info, b.price FROM (
  (SELECT * FROM properties) AS a
LEFT JOIN 
  (SELECT * FROM floorplans) AS b 
ON (a.propID=b.propID)
) WHERE blah...blah...

See if this query will help, your database might use different syntax.

[whodigs]

Ok so using:

SELECT a.*, b.* FROM ((SELECT * FROM properties) AS a LEFT JOIN (SELECT * FROM floorplans) AS b ON (a.PropId=b.PropId))

That worked. What I have now is for each PlanId associated with each PropId, there is a new column for each PropId with the Plan Id added on. So I have 6 columns for each PropId if it has 6 plans associated with it.

The next question is what would I use to echo the columns for the PlanId's without showing the PropId over and over? Since there are 2-6 per PropId is it possible to make them listed as 10 complexes with all PlanId's associated with it and no duplicate PropId's shown? Right now using the sql query above and using:

    <tr bgcolor="#cccccc">
    <td valign="top">
    <span class="smalltext">
    <a href="/" title="View apartment details"><img src="images/nophoto.gif" alt="a" align="left" title="View apartment details" /></a>
    <a href="<?php echo ($row->PropID); ?>" title="View apartment details"><?php echo ($row->PropName); ?></a><br />
        <?php echo ($row->Address); ?><br />
        <?php echo ($row->City); ?>, <?php echo ($row->State); ?> <?php echo ($row->Zip); ?><br /> 
        <a href="/">apartment details</a> | 
        <a href="/">map it</a> | 
        <a href="/">view photos</a> | 
        <a href="/">view floorplans</a><br>
        Area: <?php echo ($row->Metro); ?> <?php echo ($row->Area); ?></span>
    </td>
    <td valign="top">
    <span class="smalltext">
      <?php echo ($row->Size); ?>
    </span>
    </td>
    <td valign="top">
    <span class="smalltext">
      <?php echo ($row->Price); ?>
    </span>
    </td>
    <td valign="top">
    <span class="smalltext">
      <?php echo ($row->SqFt); ?>
    </span>
    </td>
    </tr>

I appreciate all the help so far. Finally feel like its possible! I just hope my questions make sense ;p

[shu]

Sorry, you lost me with the last post!

[whodigs]

That is still going to create X columns for each PropId to accommodate the PlanId's associated with each. Basically I'm still wondering if this approach will work for my final goal.

If I were to use this query, how can I better echo the data other then how I am presently doing it.

I'm trying to show one preview of the property with all floor plans available on this one preview. The way it is doing it now it is showing 2-7 previews depending on the number of floor plans that joined.

I do appreciate all the help though! I'm still fairly new to PHP so I know what my end result is, just no idea how to get there in this case.

[shu]

If you want to use this query, you probably can accomplish what you want with PHP. You will need to add ORDER BY propID to the query though. Hope this code skeleton can provide you some help:

$currPropID = '';
$lastPropID = '';

while ($row = mysql_fetch_object($res)) {
   $currPropID = $row->propID;

   if ($currPropID != $lastPropID) {
      //Your output code for all Property-related info (from properties table)
   }

   /* Output your floor plan information in between here */


   /* Output your floor plan information in between here */

   $lastPropID = $currPropID;

};

[whodigs]

Ok I am using this query:

SELECT a.*, b.* FROM ((SELECT * FROM properties WHERE Active = 1 ORDER BY PropName ASC LIMIT 2) AS a LEFT JOIN (SELECT * FROM floorplans) AS b ON (a.PropId=b.PropId))

Then I tried to use your code I still can't seem to get the PropName to work correctly where it doesn't repeat itself over and over for each column.

$currPropId = '';
$lastPropId = '';

while($row = mysql_fetch_object($sql_result)) {
   $currPropId = $row->PropId;

   if ($currPropId != $lastPropId) {
      //This doesn't echo out any data to browser
      echo ($row->PropName);
      echo "<br />";
   }
   /* Output your floor plan information in between here */
      echo ($row->Size);  

      echo " "; 
      echo ($row->Price);    

      echo "<p />";
   /* Output your floor plan information in between here */

   $lastPropID = $currPropID;

}; 

The property area doesn't display anything on the browser. If I mess around and try and display the PropName in other areas it displays, but has the repeat going.

really do appreciate your patience.

[shu]

Are there any records returned from the query? I see you have an WHERE Active=1 clause that weren't there before.

Also, you should move the ORDER BY clause outside, like:

SELECT a.*, b.* FROM ((SELECT * FROM properties) AS a LEFT JOIN (SELECT * FROM floorplans) AS b ON (a.PropId=b.PropId)) ORDER BY a.PropName ASC LIMIT 2

[whodigs]

I put the LIMIT in there so I could select 10 Properties to list. If I put it on the end I just get 10 columns so If property 1 has 6 floor plans and property 2 has 5 it will cut off that last floor plan.

It is returning the floor plan information correctly but I can not seem to get the PropName to not loop with it for every column. For example it returns as:

Property Manor
1BD/1BA 200
Property Manor
2BD/1BA 300

I am trying to return it as:

Property Manor
1BD/1BA 200
2BD/1BA 300

Next Property 
1BD/1BA 200
2BD/1BA 300

[shu]

Does field propID exists in both tables?

[whodigs]

nope just PropId

On the JOIN though each plan is making a new column so the PropName is available every time. If this is to difficult to accomplish I may try to just create a field on the properties table for generic info to be pulled. I still wish I could get the way I have been aiming for to work though.

This is my first script that is actually going to accomplish something useful so I may be getting ahead of myself for how I want to display the data.

[shu]

Ok, to fix the problem you need to list out the fields in the floorplans table instead of using b.*:

SELECT a.*, b.Size,b.Price FROM ((SELECT * FROM properties WHERE Active = 1 ORDER BY PropName ASC LIMIT 2) AS a LEFT JOIN (SELECT * FROM floorplans) AS b ON (a.PropId=b.PropId)) 

[SoulAssassin]

Not 100% sure what it is you are trying to do, but If I would have done something like this. I obviously couldn't test it, so there might be some syntax errors.

<?
// Select specific properties Info
if ($id)
{
$getprop = mysql_query("Select * from properties WHERE propID = '$id'", $con);
$prop = mysql_fetch_array($getprop, MYSQL_ASSOC)
?>
<strong>Property Info</strong> 
<table width="744" border="0">
  <tr> 
    <td width="14%">Prop ID</td>
    <td width="20%">Area</td>
    <td width="20%">Size</td>
    <td width="15%">Price</td>
  </tr>
  <tr> 
    <td width="14%"><? echo $prop['propID']; ?></a></td>
    <td width="20%"><? echo $prop['area'];?></td>
    <td width="20%"><? echo $prop['size'];?></td>
    <td width="15%"><? echo $prop['price'];?></td>
  </tr>
  <tr> 
    <td height="21" colspan="4">
	   <a href="thispage.php?detail=<? echo $prop['propID']; ?>&id=<? echo $prop['propID']; ?>">apartment details</a>
	 | <a href="thispage.php?map=<? echo $prop['propID']; ?>&id=<? echo $prop['propID']; ?>">map it</a>
	 | <a href="thispage.php?photos=<? echo $prop['propID']; ?>&id=<? echo $prop['propID']; ?>">view photos</a>
	 | <a href="thispage.php?plan=<? echo $prop['propID']; ?>&id=<? echo $prop['propID']; ?>">view floorplans</a>
   </td>
  </tr>
</table>
<?
}
else   // Select all properties Info
{
$getprop = mysql_query("Select * from properties", $con);
$gprop = mysql_num_rows($getprop);
$n = 0;
?>
<strong>All Properties</strong> 
<table width="744" border="0">
  <tr> 
    <td width="14%">Prop ID</td>
    <td width="20%">Area</td>
    <td width="20%">Size</td>
    <td width="15%">Price</td>
  </tr>
  <? while($prop = mysql_fetch_array($getprop, MYSQL_ASSOC))
{
$n++;
?>
  <tr> 
    <td width="14%"><? echo $prop['propID']; ?></a></td>
    <td width="20%"><? echo $prop['area'];?></td>
    <td width="20%"><? echo $prop['size'];?></td>
    <td width="15%"><? echo $prop['price'];?></td>
  </tr>
  <tr> 
    <td height="21" colspan="4">
	   <a href="thispage.php?detail=<? echo $prop['propID']; ?>&id=<? echo $prop['propID']; ?>">apartment details</a>
	 | <a href="thispage.php?map=<? echo $prop['propID']; ?>&id=<? echo $prop['propID']; ?>">map it</a>
	 | <a href="thispage.php?photos=<? echo $prop['propID']; ?>&id=<? echo $prop['propID']; ?>">view photos</a>
	 | <a href="thispage.php?plan=<? echo $prop['propID']; ?>&id=<? echo $prop['propID']; ?>">view floorplans</a>
   </td>
  </tr>
  <?
}
?>
</table>
<?
}

// Apartment Detail
if ($detail)
{
$getdetail = mysql_query("Select * from detail WHERE propID='$detail' ORDER BY '$order'", $con);
$gdetail = mysql_num_rows($getdetail);
$n = 0;
?>
<strong>Apartment Detail</strong> 
<table width="744" border="0">
    <tr> 
    <td width="14%">Prop ID</td>
    <td width="15%">Squire Feet</td>
    <td width="20%">Last Sold</td>
    <td width="15%">Bedrooms</td> 
	<td width="15%">Bathrooms</td> 
	</tr>
<? while($det = mysql_fetch_array($getdetail, MYSQL_ASSOC))
{
$n++;
?>
    <tr> 
      <td width="14%"><? echo $det['propID']; ?></td>
      <td width="20%"><? echo $det['sqrf'];?></td>
      <td width="15%"><? echo $det['lastsold'];?></td>
	  <td width="12%"><? echo $det['bedr'];?></td>
	  <td width="12%"><? echo $det['bathr'];?></td>
    </tr>
<?
}
?>
  </table>
<?
}

// Get maps
if ($map)
{
$getmap = mysql_query("Select * from maps WHERE propID='$map'", $con);
$gmap = mysql_fetch_array($getmap, MYSQL_ASSOC)
?>
<strong>Map</strong> 
<table width="744" border="0">
  <tr> 
    <td colspan="4">Map ID - <? echo $mapit['mapID'];?></td>
  </tr>
  <tr> 
    <td><img src="<? echo $mapit['mappath'];?>"></td>
  </tr>
</table>
<?
}

// Get Photo's
if ($photos)
{
$getphotos = mysql_query("Select * from photo's WHERE propID='$photos'", $con);
$gphotos = mysql_fetch_array($getphotos, MYSQL_ASSOC)
?>
<strong>Photo's</strong> 
<table width="744" border="0">
  <tr> 
    <td colspan="4">Photo ID - <? echo $gphotos['photoID'];?></td>
  </tr>
  <tr> 
    <td><img src="<? echo $gphotos['photopath'];?>"></td>
  </tr>
</table>
<?
}

// Select floorplans
if ($plan)
{
$getplan = mysql_query("Select * from plans WHERE propID='$plan'", $con);
$gplan = mysql_num_rows($getplan);
$n = 0; 
?>
<strong>Floor Plans</strong> 
<table width="744" border="0">
  <tr> 
    <td width="14%">Prop ID</td>
    <td width="20%">Plan ID</td>
    <td width="20%">Size</td>
	<td width="20%">Plan Name</td>
  </tr>
  <? while($gpln = mysql_fetch_array($getplan, MYSQL_ASSOC))
{
$n++;
?>
  <tr> 
    <td width="14%"><? echo $gpln['propID']; ?></td>
    <td width="20%"><? echo $gpln['area'];?></td>
    <td width="20%"><? echo $gpln['size'];?></td>
	<td width="20%"><? echo $gpln['planame'];?></td>
  </tr>
  <tr> 
    <td colspan="4"><img src="<? echo $gpln['planpath'];?>"></td>
  </tr>
  <?
}
?>
</table>
<?
}
?>

[whodigs]

Listing the fields still has the same result. Since their are multiple rows it just creates duplicates of the properties table.

Is there a way to make it a separate query for the floor plans? Like

$sql = "SELECT * FROM properties WHERE Active = '1' ORDER BY PropName ASC";

$PropId = ($row->PropID)

$sqlfloorplan = "SELECT * FROM floorplans WHERE PropId = $PropId"

Something like that. Now I just need to see if this way will actually work.

[shu]

Yes, what you had there should work just fine!

$props = mysql_query("SELECT * FROM properties WHERE Active = '1' ORDER BY PropName ASC");

while ($row = mysql_fetch_object($props)) {

  $PropId = $row->PropID;

  /* Output your Properties information here */


  /* Output your Properties information here */

  $plans = mysql_query("SELECT * FROM floorplans WHERE PropId = $PropId");

  while ($row2 = mysql_fetch_object($plans)) {

  /* Output your Floor Plans information here */


  /* Output your Floor Plans information here */

  }; //End inner loop: Floorplans


}; //End outer loop: Properties

This should work, what puzzles me is that the one query method should work as well. There's probably something missing in your php codes or something we are not seeing. But anyway, use which ever method that suits your need!

Cheers.