[mysql/php] help showing records from 2 tables in a while

hi, heres my situation:
i have 2 table in the db
1- annunci
2-we_onlineusers

in annunci theres ID and regione(its the state in italian) , in we_onlineusers USERID and IS_ONLINE and GUEST, where userid take the id of the user online, then is_online became 1 and guest is 0 when the user is not a guest.

i have some record in annunci, where users insert their data to shows up in a ad page.

so the page will show the users pickin their usernames from annunci where regione==$regione.

i would like to check if the id from annunci is equal with the id in we_onlineusers so that will indicate the user is online.

thats my code

	$regione="Lazio";
  
  $query= "select * from annunci, we_onlineusers WHERE annunci.regione = '$regione' ORDER BY annunci.giorno DESC";
$result = mysql_query($query, $con);

                  while ($fu=mysql_fetch_assoc($result)) { 
                  	 {extract ($fu);

  if ($id==$userid){echo "<font color=green>online</font>";}
   else {echo "<font color=pink>offline</font>";}

PHP:

that works, BUT, if there are guests online (so the we_onlineusers is populated by more records) the page will print a duplicated ad from the same person, were only one is online, and the other "clones" are marked as offline.
thats because the parsing of we_onlineusers is included in the while.
i tried making a "select * from we_onlineusers" outside the while, and leaving only the "select * from annunci" in the while (2 distinct select pratically) but it gave me error..

any suggestion will be appreciated.
thx, and i hope you understand my problem

 

Can you do a dump of 2-3 records from each table and show it here? It would be alot easier if we could see that.

 

heres the record in the annunci table (1 ad made by stefy, id 130), and in we_onlineusers table, u can see 2 records in wE_onlineusers, one is the user logged in (with userid 130)the other one is a guest with id 0. in this case, , theres one "clone" in the ad section.



annunci




we_onlineusers

 

help im stuck!

 

up anyone? im goin insane :|

 

I am still somewhat confused...

In the table, we_onlineusers; is the userid column unique? Because I don't see how you can have guests all posting with the same userid = 0, I think that is why you are getting duplicate results.

 

I think this is what you are looking for:
<sql code>
select `regione` from annunci inner join we_onlineusers on userid = annunci.id and
we_onlineusers.userid = annunci.id;
</sql code>

That should return the region if the records userid and id match in the tables annunci and we_onlineusers

Hope this helps and that understood what you need.

R.

 

oh i just saw now the replies (the notify didnt work)..
i just figured it out now using a function

function is_online($id,$conny)
{
	if (mysql_query("SELECT userid FROM we_onlineusers WHERE userid=$id",$conny))
	return"<font color=\"green\">online</font>";
  else return "<font color=\"pink\">offline</font>";
	
}

PHP:

then recalling it

echo(is_online($id,$con));

PHP:

and it workslike a charm....

i didnt now the "inner join"
im goin to try i later

thx both for the replies.

 

false victory, it shows always "online" als if the users are offline! damn


edit-

i saw the "inner join" i dont need it cause with that, if theres a user logged in, it shows the ad, but if the user is NOT logged in, it DOESNT shows the ad..i want instead a OFFLINE if the user is not online, and an online if the user is online


@Chaos_king= the id is not unique cause guests CANT post, so i identify the guests by their id==0

 

resolved with this function

function is_online($id,$conny)
{
   if (mysql_num_rows(mysql_query("SELECT userid FROM we_onlineusers WHERE userid=$id",$conny))>0)
   return"<font color=\"green\">online</font>";
 else return "<font color=\"pink\">offline</font>";
   
}

PHP: