working with two tables

[violinrocker]

I would like to show the list of online friends by first showing the friends and then checking if they are online at another table named 'available_users' ... problem is i dont have any Idea how I would implement this... this is the code I am trying to do, it currently lists all friends, whether online or offline

 

 $query = "SELECT * FROM friends WHERE username='$session->username'"; 

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){


echo "<a href=\"javascript:void(0)\" onClick=\"javascript:chatWith('$row[friendname]')\">Chat With $row[friendname]</a><br>";
echo "</br>";

[cyberRobot]

What do your tables look like? Could you provide the column names and a small sample of the data...of course the data doesn't need to be real.

[violinrocker]

for 'available_users' there are only two ..... "username" and "time stamp"

while in 'friends' there is "id", "friendname" and "username"

 

for 'friends' the guy who wrote the code for my friendsystem made an inverse of the input friendname and username whenever someone is added as a friend, (I can use 'OR' there and discard the double post right? anyway thats out of the topic)

 

what I want is for example I have 2 friends listed in 'friends' named 'smiley' and 'jc' ... then 'smiley' is online and so his name is now on 'available_users'... now I want to list all my friends that are currently online

[pineapples]

You will be better off adding a new field to the friends table: friend_online int(1) not null. Then you can run a simple SQL query to find users online.

 

$query = "SELECT * FROM friends WHERE username = '" . $session->username . "' and friend_online = 1;";

 

If you really want to use two tables then you can use a SQL join.

 

$query = "SELECT friends.field, friends.field2 
   FROM friends INNER JOIN avaliable_users ON friends.username = avaibale_users.username WHERE username = '" . $session->username . "'";

[violinrocker]

do i need to change friends.field and friends.field2?

[pineapples]

do i need to change friends.field and friends.field2?

 

Those are the fields you are selecting from the table, from the code that you posted it looks like you just want to select friendname.

[cyberRobot]

Those are the fields you are selecting from the table, from the code that you posted it looks like you just want to select friendname.

 

You could also use friends.* if you want all the fields

[violinrocker]

one problem

 

friends.friendname = available_users.username WHERE username = '" . $session->username .

 

in here its stating the 'username' from 'available_users' for "WHERE" right?

 

what I want is get the available users from 'available_users' and then check 'friends' if any of those in 'available_users' are friends with '$session->username'

[cyberRobot]

for 'available_users' there are only two ..... "username" and "time stamp"

while in 'friends' there is "id", "friendname" and "username"

 

for 'friends' the guy who wrote the code for my friendsystem made an inverse of the input friendname and username whenever someone is added as a friend, (I can use 'OR' there and discard the double post right? anyway thats out of the topic)

 

what I want is for example I have 2 friends listed in 'friends' named 'smiley' and 'jc' ... then 'smiley' is online and so his name is now on 'available_users'... now I want to list all my friends that are currently online

 

I just want to make sure I'm understanding.

 

When someone logs into the system, their username is added to the available_users table. Are they removed from the table when they logout?

 

For the friends table, what does friendname contain...the username of the friend?

 

Is there another table which contains your user data? The one that tells you what username goes with what person?

[violinrocker]

I use jp77's login script...

 

yeah when you login the username is added in 'available_users' and when you logout it is erased...

 

in friends... the fields are 'username' and 'friendsname'

the username that I want to use for "WHERE" is $session->username which is the 'username' field in 'friends'

and the 'username's that I want to get from 'available_users' is from the 'friendsname' of 'friends' where the 'username' for 'friends' is the one logged in or '$session->username'

[violinrocker]

uhhh how will you show the results? $row['what??] ?

[cyberRobot]

Sorry, I'm still a little fuzzy on the details and unfortunatally I not familiar with jp77's login script. Maybe this scenario will help?

 

I have an account in your online forum and my username is "cyberRobot". I have two friends whose usernames are "smiley" and "jc". So I have two records in your friends table:

idfriendnameusername

1smileycyberRobot

2jccyberRobot

 

If smiley and I are logged in, the available_users table will look like this:

usernametimestamp

cyberRobot...

smiley...

 

Of course the "..." will be the timestamp for when we logged in.

 

 

And if we're looking at listing my friends, then $session->username would be equal to cyberRobot?

 

Is this a correct picture of the process?

[violinrocker]

Sorry, I'm still a little fuzzy on the details and unfortunatally I not familiar with jp77's login script. Maybe this scenario will help?

 

I have an account in your online forum and my username is "cyberRobot". I have two friends whose usernames are "smiley" and "jc". So I have two records in your friends table:

idfriendnameusername

1smileycyberRobot

2jccyberRobot

 

If smiley and I are logged in, the available_users table will look like this:

usernametimestamp

cyberRobot...

smiley...

 

Of course the "..." will be the timestamp for when we logged in.

 

 

And if we're looking at listing my friends, then $session->username would be equal to cyberRobot?

 

Is this a correct picture of the process?

 

YES SIR! =)

[cyberRobot]

This should work, but its untested.

 

//GET THE LIST OF FRIENDS
$query = "SELECT * FROM friends WHERE username='$session->username'";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
//FIGURE OUT IF THE CURRENT FRIEND IS ONLINE
$query2 = "SELECT * FROM available_users WHERE username='$row[friendname]'";
$result2 = mysql_query($query2) or die(mysql_error());

//IF THE CURRENT FRIEND IS ONLINE, SHOW CHAT LINK
if($row2 = mysql_fetch_array($result2)) {
	echo "<a href=\"javascript:void(0)\" onClick=\"javascript:chatWith('$row2[username]')\">Chat With $row2[username]</a><br></br>";
}
}

 

Also, the process could be done in fewer steps if you use the JOIN clause to get data from both tables at the same time. But I never remember exactly how to use it.

[violinrocker]

Tnx! it works now...

 

I hope you dont mind, but is it possible if we get another field from another table?

 

like... we now have the list of friends that are also available at 'available_users' right? can we get 'avatar' from another table named 'users' wherein the 'username' field is the same with active friends?

 

for example --'users'--

 

username          name        password        avatar

---------------------------------------------------------------------

smiley                smiley        *********      78139.png

 

 

[violinrocker]

anyone?

[cags]

Selecting online friends should be possible with a single query. It would look something along the lines of this...

 

SELECT f.*, au.* FROM friends f 
JOIN available_users au ON au.username = f.friendname
WHERE username = '{$session->username}'

 

You can then request the other field in the same manner. It would look something along the lines of this...

 

SELECT f.*, au.*, u.avatar FROM friends f 
JOIN available_users au ON au.username = f.friendname
JOIN users u ON u.username = f.friendname
WHERE username = '{$session->username}'

[cyberRobot]

It sounds like you want to get the avatar for the friends that are online. If that's the case, this should work.

 

//GET THE LIST OF FRIENDS
$query = "SELECT * FROM friends WHERE username='$session->username'";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
//FIGURE OUT IF THE CURRENT FRIEND IS ONLINE
$query2 = "SELECT * FROM available_users WHERE username='$row[friendname]'";
$result2 = mysql_query($query2) or die(mysql_error());

//IF THE CURRENT FRIEND IS ONLINE, SHOW CHAT LINK
if($row2 = mysql_fetch_array($result2)) {
	//MAKE SURE THE AVATAR VARIABLE IS BLANK
	$avatar = '';

	//GET CURRENT FRIEND'S AVATAR
	$query3 = "SELECT avatar FROM users WHERE username='$row[friendname]'";
	$result3 = mysql_query($query3) or die(mysql_error());
	if($row3 = mysql_fetch_array($result3)) {
		$avatar = $row3['avatar'];
	}

	//SHOW CHAT LINK
	echo "<a href=\"javascript:void(0)\" onClick=\"javascript:chatWith('$row2[username]')\">";
	if($avatar != '') { echo "<img src='$avatar'>"; }
	echo "Chat With $row2[username]</a><br></br>";
}
}

 

 

As mentioned before, using a join statement would be more efficient for the MySQL query. But I would need to go back to the books to figure out the syntax.

[violinrocker]

I also like double queries more... I get confused with INNER JOIN...

 

ummm another thing haha.... I want the image link to be lik 'no_image.png' if the avatar field is blank...

I already know how to do it, but my old code kinda looks different and requires something else,

 

BTW Thank you so much! cyberRobot

[cyberRobot]

SELECT f.*, au.* FROM friends f 
JOIN available_users au ON au.username = f.friendname
WHERE username = '{$session->username}'

 

 

Shouldn't there be an AS between the table name and the name shortcut? Also I think you need to designate which username you're referrring to in the WHERE clause?

 

SELECT f.*, au.* FROM friends AS f 
JOIN available_users AS au ON au.username = f.friendname
WHERE au.username = '{$session->username}'

[cyberRobot]

The code below has been modified to address the issue of a blank avatar.

 

//GET THE LIST OF FRIENDS
$query = "SELECT * FROM friends WHERE username='$session->username'";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
//FIGURE OUT IF THE CURRENT FRIEND IS ONLINE
$query2 = "SELECT * FROM available_users WHERE username='$row[friendname]'";
$result2 = mysql_query($query2) or die(mysql_error());

//IF THE CURRENT FRIEND IS ONLINE, SHOW CHAT LINK
if($row2 = mysql_fetch_array($result2)) {
	//MAKE SURE THE AVATAR VARIABLE IS BLANK
	$avatar = '';

	//GET CURRENT FRIEND'S AVATAR
	$query3 = "SELECT avatar FROM users WHERE username='$row[friendname]'";
	$result3 = mysql_query($query3) or die(mysql_error());
	if($row3 = mysql_fetch_array($result3)) {
		$avatar = $row3['avatar'];
	}

	//SHOW CHAT LINK
	echo "<a href=\"javascript:void(0)\" onClick=\"javascript:chatWith('$row2[username]')\">";
	if($avatar != '') {
		echo "<img src='$avatar'>";
	} else {
		echo "<img src='no_image.png'>";
	}
	echo "Chat With $row2[username]</a><br></br>";
}
}

[violinrocker]

TNX! works great

I have one final problem (i think) if you dont mind

 

$query = "SELECT * FROM friends WHERE username='$session->username'";$result = mysql_query($query) or die(mysql_error());while($row = mysql_fetch_array($result)) {	//FIGURE OUT IF THE CURRENT FRIEND IS ONLINE	
$query2 = "SELECT * FROM active_users WHERE username='$row[friendname]'";	$result2 = mysql_query($query2) or die(mysql_error());		//IF THE CURRENT FRIEND IS ONLINE, SHOW CHAT LINK	
if($row2 = mysql_fetch_array($result2)) {		echo "You have ".mysql_num_rows($result2)." friend/s online<br>"; }

 

I used this code to show the number of friends online... problem is, when you have two friends online it also only shows 2 "you have 1 friend/s online"

[cyberRobot]

Then you're probably going to want to store the list of friends in a variable and display it later. Try this:

 

//INITIALIZE VARIABLE TO STORE LIST OF FRIENDS
$listOfFriends = '';

//GET THE LIST OF FRIENDS
$query = "SELECT * FROM friends WHERE username='$session->username'";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
//FIGURE OUT IF THE CURRENT FRIEND IS ONLINE
$query2 = "SELECT * FROM available_users WHERE username='$row[friendname]'";
$result2 = mysql_query($query2) or die(mysql_error());

//IF THE CURRENT FRIEND IS ONLINE, SHOW CHAT LINK
if($row2 = mysql_fetch_array($result2)) {
	//MAKE SURE THE AVATAR VARIABLE IS BLANK
	$avatar = '';

	//GET CURRENT FRIEND'S AVATAR
	$query3 = "SELECT avatar FROM users WHERE username='$row[friendname]'";
	$result3 = mysql_query($query3) or die(mysql_error());
	if($row3 = mysql_fetch_array($result3)) {
		$avatar = $row3['avatar'];
	}

	//SHOW CHAT LINK
	$listOfFriends .= "<a href=\"javascript:void(0)\" onClick=\"javascript:chatWith('$row2[username]')\">";
	if($avatar != '') {
		$listOfFriends .= "<img src='$avatar'>";
	} else {
		$listOfFriends .= "<img src='no_image.png'>";
	}
	$listOfFriends .= "Chat With $row2[username]</a><br></br>";
}
}

//DISPLAY THE LIST OF FRIENDS
if($listOfFriends != '') {
echo "You have " . mysql_num_rows($result2) . " friend/s online<br>";
echo $listOfFriends;
}