select from 2 tables

[AviNahum]

hey,

i'm trying to select information from 2 tables in one query...

so i have 2 tables...

 

members - (holds the members information)

looks like this:

+---+--------+------------+------------+
| id | name  |    email    | password |
+---+--------+------------+------------+
|  1 | user1 | [email protected] | ****       |
|  2 | user2 | [email protected] | ****       |
|  3 | user3 | [email protected] | ****       |
|  4 | user4 | [email protected] | ****       |
|  5 | user5 | [email protected] | ****       |
|  6 | user6 | [email protected] | ****       |
+---+--------+------------+------------+

 

the second table is profile_views

which stores the profile views...

+---+--------+-----------+---------------+
| id | userid | viewerid |       time      |
+---+--------+-----------+---------------+
|  1 | 4        | 1           | 1287949172 |
|  2 | 6        | 2           | 1287949172 |
|  3 | 2        | 4           | 1287949172 |
|  4 | 4        | 5           | 1287949172 |
|  5 | 3        | 2           | 1287949172 |
+---+--------+-----------+---------------+

 

userid - the profile (member) id that been watched

viewerid - the id of the member who watched

time - the time this happened

 

im trying to select from profile_views the viewers ids' and select the info from the members according to the viewerid, but also i want to select the time from the profile_views, so it's should look like this:

+---+--------+------------+----------+------------+
| id | name  |    email    | password |  time
+---+--------+------------+----------+------------+
|  1 | user1 | [email protected] | ****        | 1287949172 
|  2 | user2 | [email protected] | ****        | 1287949172 
|  3 | user3 | [email protected] | ****        | 1287949172 
|  4 | user4 | [email protected] | ****        | 1287949172 
|  5 | user5 | [email protected] | ****        | 1287949172 
|  6 | user6 | [email protected] | ****        | 1287949172 
+---+--------+------------+----------+------------+

 

this is my code:

$DB->query("SELECT viewerid FROM profile_views WHERE userid={$core->input['showuser']} ORDER BY time DESC LIMIT 5");

if ( $DB->get_num_rows() > 0 )
{
	while ( $all_viewers = $DB->fetch_row() )
	{
		$viewers_ids[] = $all_viewers['viewerid'];
	}

	$viewers_ids = implode(",", $viewers_ids);


$DB->query("SELECT m.*, v.*
                                FROM members m
                                LEFT JOIN profile_views v ON (v.userid=m.id)
                                WHERE m.id IN ({$viewers_ids})");

	while ( $viewers = $DB->fetch_row() )
	{

	}
}

 

but it's just dont work!

any ideas?

 

Thanks in advance!

[Psycho]

It's called a JOIN. Go read some tutorials on all the wonderful things you can do with them:

 

SELECT m.id, m.name, m.email, pv.time

FROM profile_views as pv

JOIN members as m
  ON pv.viewerid = m.id

ORDER BY m.id

[AviNahum]

alright! works great! thanks!!!