Pulling from multiple tables without multiple queries??

[mband2000]

I'm trying to check for users that are online in a selected city.... but the problem is that I would have to run multiple queries to get the information needed. here is how my queries look so far just to get what I need.

I'm pulling from the following tables:
- location_town (towns are sub-neighborhoods within the cities)
- location_cities
- sessions

/// First Query to get the CITY ID ///
$getmycity_query = mysql_query("SELECT city_id FROM location_town WERE id = '".$_SESSION['userinfo']['town']."'");

$getmycity_results = mysql_fetch_assoc($getmycity_query);

Ok, now that I know the city_id ... I can check the town table again to find out which towns have that city ID

/// Second Query to get a list of all towns within the selected city ///
$gettowns_query = mysql_query("SELECT id FROM location_town WHERE city_id = '".$getmycity_results['city_id']."'");



/// Now I can store each town's ID into an array ///
$townArray = array();

while($gettowns_results = mysql_fetch_assoc($gettowns_query)){
  array_push($townArray, $gettowns_results['id']);
}

/// Now I implode the list into something SQL will understand ///
$townIDs = implode(" ) OR ( `town_id` =", $townArray);
$townIDs = "( `town_id` =".$townIDs." )";

Now I have created the string $townIDs which looks something like ( town_id =5 ) OR ( town_id =10 ) OR ( town_id =349 )

I will use that string as part of my query statment when looking for town_id's in the session table

/// Third Query is to find all members with that Town ID in the sessions table ///
$find_query = mysql_query("SELECT uID FROM sessions WHERE ".$townIDs);

And that's it.... once the 3rd query is done... I know which users are online for a certain city.

But this doesn't seem like the most efficient way to do this.. anyone have suggestions?

[Yegg]

well you can use JOIN method:

select uID from sessions, location_town where location_town.id = '".$_SESSION['userinfo']['town']."' and location_town.city_id = sessions.town_id;

[mband2000]

Thanks, but that didn't return the correct data.

and I don't see where you used the JOIN method

[Yegg]

well put all columns/table names with underline (_) character in back quotes, so U need try this way:

select uID from sessions, location_town where location_town.id = '".$_SESSION['userinfo']['town']."' and location_town.city_id = sessions.town_id;

if you will still get an erro plz gimme the error name/code.

It's a TRIVIAL CROSS JOIN method, if you use several tables after FROM and use equela keys to joing, it's mean you are joining it, if you want to see join word in your clause you can use so way:

select from sessions join location_townwhere ......keys equals
select from sessions cross join location_townwhere ......keys equals
and that one
select * from sessions, location_townwhere ......keys equals

all this clauses the same for MySQL engine