Grouping into categories

[bcalagoure]

Hi all. This question is probably already somewhere in here but I can't find it. I want to display rental apartments grouped by property. I have 2 tables, one that has the property info (location, etc.) the other has info for each apartment in the property (rent, vacancy, etc.) The SELECT statement I have now is

"SELECT properties.buildingID, properties.location, units.rentalID, units.unitnum, units.rent_status 
FROM properties 
LEFT JOIN units 
ON properties.buildingID = units.buildingID ";

it displays this:

Property 1 Unit 1 Rented
Property 1 Unit 2 Rented
Property 2 Unit 1 Rented
Property 2 Unit 2 Vacant

...and so on. What I'd like is to display it like this:

Property 1
Unit 1 Rented
Unit 2 Rented

Property 2
Unit 1 Rented
Unit 2 Vacant

Any suggestions?

[Cups]

As a result set from mysql, generating arrays - that extrapolates to being roughly:

$row[0]['property'] = 'Property 1';
$row[0]['unit'] = 'Unit 1';
$row[0]['status'] = 'Rented';
$row[1]['property'] = 'Property 1';
$row[1]['unit'] = 'Unit 2';
$row[1]['status'] = 'Rented';
$row[2]['property'] = 'Property 2';
$row[2]['unit'] = 'Unit 1';
$row[2]['status']= 'Rented';
$row[3]['property'] ='Property 2';
$row[3]['unit'] = 'Unit 2';
$row[3]['status'] = 'Vacant'; 

$last = '';
foreach( $row as $r ){

 if( $r['property'] != $last){
 echo '<h3>' . $r['property'] . '</h3>' . PHP_EOL ;
 }
 $last = $r['property'];
 echo '<p>' . $r['unit'] . ' is ' . $r['status'] .'</p>'. PHP_EOL ;
} 

Giving:

Property 1
Unit 1 is Rented
Unit 2 is Rented

Property 2
Unit 1 is Rented
Unit 2 is Vacant

[bcalagoure]

Thanks Cups, I'll give it a try

[bcalagoure]

OK, didn't quite work. I'm using Dreamweaver (don't judge ) so the query and result look like this:

$query_rs_status = "SELECT properties.buildingID, properties.location, units.rentalID, units.unitnum, units.rent_status  FROM properties  LEFT JOIN units  ON properties.buildingID = units.buildingID ";
$rs_status = mysql_query($query_rs_status, $conn_taylor) or die(mysql_error());
$row_rs_status = mysql_fetch_assoc($rs_status);
$totalRows_rs_status = mysql_num_rows($rs_status);

Accordingly, I modified your for loop to this:

$last = '';
foreach( $row_rs_status as $r){ 
	if( $r['buildingID'] != $last){ 
		echo '<h3>' . $r['location'] . '</h3>' . PHP_EOL ; } 
	$last = $r['buildingID']; 
	echo '<p>' . $r['unitnum'] . ' is ' . $r['rent_status'] .'</p>'. PHP_EOL ;}

But the end result is this:

1
1 is 1

3
3 is 3

1
1 is 1

1 is 1

1 is 1

Even if I change it from buildingID to location (which is the physical address) I still get this
Another note, the rent_status field is boolean (tinyint, only 0 or 1 entered) so the "3" after "is" shouldn't be there at all. What am I doing wrong?

[bcalagoure]

Anyone??

[Cups]

Probably best to tell us what the tables you are joining look like, outputting ID numbers is not likely to be what you want either is it?

show us the output of the following sql statement.

describe <table name>;

for those 2 tables.

[bcalagoure]

This is the result of the describe <table>

properties table:

Field  Type  Null  Key  Default  Extra  
buildingID tinyint(6) NO PRI NULL auto_increment 
mailingaddress text NO   NULL   
description text NO   NULL   
features text NO   NULL   
location varchar(75) NO   NULL   
image1 varchar(255) YES   NULL   
image2 varchar(255) YES   NULL   
image3 varchar(255) YES   NULL   
image4 varchar(255) YES   NULL   
image5 varchar(255) YES   NULL   
image6 varchar(255) YES   NULL   
image7 varchar(255) YES   NULL   
image 8 varchar(255) YES   NULL   


units table:

Field  Type  Null  Key  Default  Extra  
rentalID tinyint(12) NO PRI NULL auto_increment 
buildingID tinyint(6) NO   NULL   
unitnum tinyint(5) NO   NULL   
rent_status tinyint(1) NO   NULL   
image1 varchar(255) YES   NULL   
image2 varchar(255) YES   NULL   
image3 varchar(255) YES   NULL   
image4 varchar(255) YES   NULL   
details text NO   NULL   
features text NO   NULL   
numbedrooms varchar(10) NO   NULL   
rent varchar(45) NO   NULL   
deposit varchar(35) NO   NULL   
lease varchar(25) NO   NULL

And yes, I do want the location rather than ID numbers, which is why I did a LEFT JOIN in the query. The location column is in the for loop but doesn't show up.

I've also tried designating the tables as well as the columns in the for loop (i.e. properties.buildingID etc.). I've even tried using a non-numerical column like units.features but it comes out exactly the same no matter what I change.

[Cups]

$query_rs_status = "
SELECT p.location as property
, u.unitnum as unit
, u.rent_status  as status
FROM properties as p
LEFT JOIN units  as u
ON p.buildingID = u.buildingID "; 

*UNTESTED*

That should bring back rows of data which resemble those I used and example in reply #2.

Add rows back in as you see fit, but hopefully this - with the code I posted - will illustrate my point.

[bcalagoure]

Slightly different output but still not what I need

3
3 is 3

1
1 is 1

1 is 1

[Cups]

I don't understand why you are still displaying numbers - show me a var_dump() of the $rows which result from the query I suggested.

[bcalagoure]

OK, did some tinkering and used the do while loop instead of for, like this

 <?php      do { 
      $last = '';
     if( $row_rs_status['property'] != $last){ 
    echo '<h4>' . $row_rs_status['property'] . '</h4>' . PHP_EOL ; } 
    $last = $row_rs_status['property']; 
     echo '<p>' . $row_rs_status['unit'] . ' | ' . $status .'</p>' . PHP_EOL ;

 } while ($row_rs_status = mysql_fetch_assoc($rs_status)); ?>

It is now showing the actual names of the properties, but still every line

123 Water Street
101 is 1

123 Water Street
102 is 1

123 Water Street
103 is 1
.../etc.

I've rebooted in the meantime so maybe cache got cleared or something. Anyway, all data is being displayed but still the same as what I originally had.


Just saw your last replay and I'm not sure how to do a var_dump(), but this is the result from phpMyAdmin

buildingID property unit status
1 35 Belvedere Ave 1 1
1 35 Belvedere Ave 2 1
1 35 Belvedere Ave 3 1
3 460 St. Peter's Rd 1 1
3 460 St. Peter's Rd 2 1
5 315 Norwood Rd. 1 1
5 315 Norwood Rd. 2 1
5 315 Norwood Rd. 3 0
6 193 Westridge Cresant NULL NULL
7 26 Doncaster Ave NULL NULL
8 517 Malpeque Rd. NULL NULL
9 417 - 419 Norwood Rd NULL NULL
13 433 - 435 Norwood Rd. NULL NULL
14 123 Water Street 101 1
14 123 Water Street 102 1
14 123 Water Street 103 1

[Cups]

This is what var_dump() looks like, go and read about it in the database it will save your life. var_dump

var_dump($row_rs_status); 

That result set looks about right to me, try adding the code I gave you featuring a foreach() loop instead of your attempt.

[r937]

the query requires an ORDER BY clause for this to work successfully

[bcalagoure]

This is what var_dump() looks like, go and read about it in the database it will save your life.

Thanks for that, I'll definitely look up the var_dump

That result set looks about right to me, try adding the code I gave you featuring a foreach() loop instead of your attempt.

the query requires an ORDER BY clause for this to work successfully

OK, here is what I have for the query, including the ORDER BY that r937 recommended:

mysql_select_db($database_conn_taylor, $conn_taylor);
$query_rs_status = "SELECT p.buildingID as bID, p.location as property, u.unitnum as unit, u.rent_status as status  
FROM properties as p 
LEFT JOIN units  as u 
ON p.buildingID = u.buildingID  
ORDER BY bID"; 
$rs_status = mysql_query($query_rs_status, $conn_taylor) or die(mysql_error());
$row_rs_status = mysql_fetch_assoc($rs_status);
$totalRows_rs_status = mysql_num_rows($rs_status); 

And here's the loop:

$last = '';
foreach( $row_rs_status as $r ){ 
if( $r['property'] != $last){ 
echo '<h3>' . $r['property'] . '</h3>' . PHP_EOL ; } 
$last = $r['property']; 
echo '<p>' . $r['unit'] . ' is ' . $r['features'] .'</p>'. PHP_EOL ;} 

And here's the result:

1
1 is 1

3
3 is 3

1
1 is 1

1 is 1

On the other hand, with the do while loop (same query) I have

do { 
$last = '';
if( $row_rs_status['property'] != $last){ 
echo '<h4>' . $row_rs_status['property'] . '</h4>' . PHP_EOL ; } 
$last = $row_rs_status['property']; 
echo '<p>' . $row_rs_status['unit'] . ' is ' . $row_rs_status['status'] .'</p>' . PHP_EOL ;

 } while ($row_rs_status = mysql_fetch_assoc($rs_status)); ?> 

And the result is:

35 Belvedere Ave
1 is 1

35 Belvedere Ave
2 is 1

35 Belvedere Ave
3 is 1

460 St. Peter's Rd
1 is 1

460 St. Peter's Rd
2 is 1

315 Norwood Rd.
2 is 1

315 Norwood Rd.
3 is 0

Etc.

I feel like something obvious is missing or out of place but I just can't see it.

[AnthonySterling]

You need to order by property, not bID. This line:

$row_rs_status = mysql_fetch_assoc($rs_status); 

Shouldn't be called either, you only getting the first row, which *appears* to be pointless. you also don't appear to be requesting a 'featues' column, or am I mistaken?

Cups is correct, you need to break it down down into smaller chunks and debug it as you go.

Give this a whirl, it's essentially what Cups has asked you to do already though...

<?php
$result = mysql_query($sql);

$last = null;

while($record = mysql_fetch_assoc($result)){
  
  if($record['property'] !== $last){
    echo '<h4>', $record['property'], '</h4>', PHP_EOL ;
    $last = $record['property'];
  }
  
  echo '<p>', $record['unit'], ' is ', $record['status'], '</p>', PHP_EOL ;
}

[bcalagoure]

That did it!! I can't thank you enough you guys
The trick was in removing the

$row_rs_status = mysql_fetch_assoc($rs_status);

line as Anthony suggested.


Just for the record, a var_dump on the for loop resulted in

array(3) { ["property"]=> string(16) "123 Water Street" ["unit"]=> string(3) "102" ["status"]=> string(1) "1" }

so, essentially one row of data. No idea why it would display as numbers though.

I realized my mistake in doing the ORDER BY on buildingID (duh!) and changed it to property when I was testing but that was after my post. Results were the same regardless. But it works now [dance of happiness]