SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Grouping into categories

    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
    Code:
    "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?

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    As a result set from mysql, generating arrays - that extrapolates to being roughly:
    PHP Code:
    $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'
    PHP Code:
    $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

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Cups, I'll give it a try

  4. #4
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, didn't quite work. I'm using Dreamweaver (don't judge ) so the query and result look like this:
    Code:
    $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:
    Code:
    $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?

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone??

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  7. #7
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the result of the describe <table>
    Code:
    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.
    Last edited by bcalagoure; May 8, 2011 at 05:40. Reason: add more info

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    $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.

  9. #9
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Slightly different output but still not what I need
    3
    3 is 3

    1
    1 is 1

    1 is 1

  10. #10
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  11. #11
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, did some tinkering and used the do while loop instead of for, like this
    PHP Code:
     <?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

    HTML Code:
    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

  12. #12
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    This is what var_dump() looks like, go and read about it in the database it will save your life. var_dump
    PHP Code:
    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.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    the query requires an ORDER BY clause for this to work successfully
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    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.
    Quote Originally Posted by r937 View Post
    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:
    PHP Code:
    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:
    PHP Code:
    $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:

    HTML Code:
    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
    PHP Code:
    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:
    HTML Code:
    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.

  15. #15
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    You need to order by property, not bID. This line:
    PHP Code:
    $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 Code:
    <?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 ;
    }
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  16. #16
    SitePoint Zealot
    Join Date
    Jan 2006
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    HTML Code:
    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]


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •