How to loop through table where criteria is met and then group results from same cat.

[bengarbi]

I have a table made up of a number of columns, id, room_id, property_id, and date.
The property_id could be a category with different room_id being subcategory.

An additional variable is the number of dyas, eg.2 .The date is stored as a timestamp, so if two nigths are selected along with a start date two timestamps are generated.

Im trying to write a php script that will:

1. select room_id euqal to or within the timestamps.
2. if the number of room_id's found matches the number nights, to then
3. display the room_id in groups under the corresponding property_id

if there are no rooms that match then there is no need to display the property_id (category).

So far this is the best i could do (keep in mind im new to php!)

$query = "SELECT DISTINCT property_id FROM availability WHERE status='1' in (SELECT property_id FROM properties WHERE city='".$city."' AND supplier_type='".$property_type."') AND property_id=property_id AND date >= '".$checkin."' AND date <= '".$checkout."'";
$result = mysql_query($query) or die (mysql_error());

   while ($row=mysql_fetch_assoc($result)){     
      $sql = "SELECT DISTINCT property_id FROM availability WHERE room_id='".$row['room_id']."'";
      $res = mysql_query($sql) or die (mysql_error());

      while($row2 = mysql_fetch_assoc($res)){

         echo "".$row2['property_id']."<br/>";

     $sql3="SELECT room_id FROM availability WHERE room_id='".$row2['room_id']."'";
     $res3=mysql_query($sql3) or die (mysql_error());

         if(mysql_num_rows($res3)>=$days){

     $sql4="SELECT DISTINCT property_id FROM availability WHERE room_id='".$row2['room_id']."'";
     $res4=mysql_query($sql4) or die (mysql_error());

     while($row4=mysql_fetch_array($res4)){

        echo "".$row4['property_id']."";
        echo "<br/>";

       #$sql5 = "SELECT * FROM availability WHERE room_id='".$row4['room_id']."'";
       $res5 = mysql_query($sql5) or die(mysql_error());
       $row5 = mysql_fetch_assoc($res5);

           echo "".$row5['id']."";
         }
      }
   }
}

Im guesing i should really be using a foreach looop or for loop to loop through my results but keep coming up with errors.

[smantscheff]

You're barking up the wrong tree. Try to build a query which contains all data to be displayed and then loop through it. Do not use 5 queries - it makes your code illegible.
And regarding your code style: avoid redundancies. If mysql_query() goes with "or die..." whereever it occurs then those two belong in a function:

function myquery($sql) { return mysql_query($sql) or die(mysql_error()); }

[bengarbi]

Thanks! that makes sense.

How do i loop through the data, and then group, i tried using GROUP BY but now realize that was stupid.

what im trying to do is list grouped subcategories under a category, only if the subcategory meets the criteria. If there is no subcategory no category should be displayed.

i hope that makes sense :S

eg

property_id_1
- room_id_1
- room_id_3

property_id_2
- room_id_2
- room_id_4
- room_id_5

property_id_3 (should not be displayed)
- (no rooms)

[smantscheff]

Show your CREATE TABLE statements.
If properties and rooms are in a 1:n relation you should be able to build a query for your objectives with a simple join from properties to rooms:

select property.id, room.id from property, room where property.id=room.id_property

This returns only those properties which have rooms associated with them.

[bengarbi]

I think i solved it with:

$prevProperty = '';
while ($row = mysql_fetch_assoc($result)) {
  $property = $row['property_id'];
  $room = $row['room_id'];
  if ($prevProperty != $property) {
	  
    echo "<b>".$property."</b><br/>";
  }
  echo "".$room."<br/>";
  $prevProperty = $property;
}

my results are now grouped, under each property_id the room_id for that property_id is listed under it.

All i need to do now is add a query to check if there are no room_id's and if there are none not to display the property_id

[bengarbi]

forget that last bit about not showing property_id if no room_id, the query does this.

[smantscheff]

Glad to hear that. Please mark this thread as solved.
Move $prevProperty = $property; into the inner if clause (for systematical reasons only).

[bengarbi]

Thank you for your help smantscheff!