[RESOLVED] php & mysql loop issue

[dreams4000]

Hey All,

	I have a small chunk of code that fully works, but I want to speed it up a bit as large data sets tend to slow it down.
	The problem is the fact that I am using a mysql call within a loop that is created by another mysql call. I am not 
	quite sure how to join these tables in a manner that would work as I am trying to get an output that looks like:

	User1 --  House_loc1 --- House loc2 --- House loc3
	User2 --  House_loc1 --- House loc2 --- House loc3
	User3 --  House_loc1 --- House loc2 --- House loc3

	Where users are unique and the house locs for each user are unique for that user.  The problem I had was that with a join, the # of house locs was 
	causing the user names to duplicate. Any ideas?  thanks a ton!

[code]	

//Selects our inital group of users
$tmpquery = "SELECT * FROM users WHERE location IN($user_locations)";
$Users = new db_request();
$Users->openUsers($tmpquery);
$Count_Users = count($Users->users_id);

	//Loops through each user and gives us their id and username
	for ($a=0; $a<$Count_Users; $a++) {
		$user_id = $Users->users_id[$a];
		$user_name = $Users->users_name[$a];

//Problem sql query, causing too much drag on the system.. matches each user id to an id
//in the user_houses table so we get a list of all houses that belong to each user.
$tmpquery = "SELECT * FROM user_houses WHERE user_id = '$user_id'";
	$Houses = new db_request();
	$Houses->openUser_Houses($tmpquery);
	$Count_Houses = count($Houses->user_houses_id);


	//sets up a demo table like the one shown above, where the $house_loc is the problem item.
echo "<tr><td>$user_id</td><td>$user_name</td>";

		for ($m=0; $m<$Count_Houses; $m++) {
	$house_loc = $Houses->user_houses_location[$m];

echo "<td>$house_loc </td>";

		}

echo "</tr>";



	}

[/code]

[dreams4000]

Anyone have any ideas? or anything I can do to make the problem clearer? Thanks!

[dreams4000]

Just for those that may run into the same issue.. Here is something that helped to point me in the right direction with my code. It needs just a few mods to fit the main code, but gets the core of it rollin. Thanks to bjwilke for helping out with this!

 // Select id, name from users table, location from user_location table 
$sql = "SELECT u.users_id, u.users_name, uh.location 
   FROM users AS u, user_houses AS uh 
   WHERE u.location IN($user_locations) AND 
      uh.user_id = u.users_id"; 

$UserHouses = new db_request(); 
$UserHouses->searchme($sql);    

$CountUserHouses = count($UserHouses->users_id); 

$OldUsersId = -1; 
for($i=0; $i<$CountUserHouses; $i++ ) { 
   if ($UserHouses->users_id[$i] != $OldUsersId) { 
      if ($i != 0 ) { 
         echo "</tr>"; 
      } 
      echo "<tr><td>{$UserHouses->users_id[$i]}</td> 
                <td>{$UserHouses->users_name[$i]}</td>"; 
   } 
   echo "<td>{$UserHouses->user_houses_location[$i]}</td>"; 
   $OldUsersId = $UserHouses->users_id[$i]; 
} 
echo '</tr>';