Grouping into categories

bcalagoure · May 6, 2011, 7:45pm

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 · May 6, 2011, 10:21pm

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 · May 7, 2011, 10:16am

Thanks Cups, I’ll give it a try

bcalagoure · May 7, 2011, 11:23am

OK, didn’t quite work. I’m using Dreamweaver (don’t judge :injured:) 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

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 · May 7, 2011, 3:13pm

Anyone??

Cups · May 7, 2011, 3:23pm

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 · May 8, 2011, 12:29pm

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 · May 8, 2011, 2:29pm


$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 · May 8, 2011, 2:53pm

Slightly different output but still not what I need

3
3 is 3

1
1 is 1

1 is 1

Cups · May 8, 2011, 4:36pm

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 · May 8, 2011, 5:10pm

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 · May 8, 2011, 7:03pm

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


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 · May 8, 2011, 8:04pm

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

bcalagoure · May 9, 2011, 2:55am

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.

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 · May 9, 2011, 4:05pm

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 · May 9, 2011, 4:54pm

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]