violinrocker Posted April 22, 2010 Share Posted April 22, 2010 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>"; Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/ Share on other sites More sharing options...
cyberRobot Posted April 22, 2010 Share Posted April 22, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046395 Share on other sites More sharing options...
violinrocker Posted April 22, 2010 Author Share Posted April 22, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046426 Share on other sites More sharing options...
pineapples Posted April 22, 2010 Share Posted April 22, 2010 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 . "'"; Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046434 Share on other sites More sharing options...
violinrocker Posted April 22, 2010 Author Share Posted April 22, 2010 do i need to change friends.field and friends.field2? Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046438 Share on other sites More sharing options...
pineapples Posted April 22, 2010 Share Posted April 22, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046440 Share on other sites More sharing options...
cyberRobot Posted April 22, 2010 Share Posted April 22, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046444 Share on other sites More sharing options...
violinrocker Posted April 22, 2010 Author Share Posted April 22, 2010 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' Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046449 Share on other sites More sharing options...
cyberRobot Posted April 22, 2010 Share Posted April 22, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046467 Share on other sites More sharing options...
violinrocker Posted April 22, 2010 Author Share Posted April 22, 2010 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' Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046479 Share on other sites More sharing options...
violinrocker Posted April 22, 2010 Author Share Posted April 22, 2010 uhhh how will you show the results? $row['what??] ? Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046515 Share on other sites More sharing options...
cyberRobot Posted April 22, 2010 Share Posted April 22, 2010 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? Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046524 Share on other sites More sharing options...
violinrocker Posted April 22, 2010 Author Share Posted April 22, 2010 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! =) Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046525 Share on other sites More sharing options...
cyberRobot Posted April 22, 2010 Share Posted April 22, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046571 Share on other sites More sharing options...
violinrocker Posted April 23, 2010 Author Share Posted April 23, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046786 Share on other sites More sharing options...
violinrocker Posted April 23, 2010 Author Share Posted April 23, 2010 anyone? Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046942 Share on other sites More sharing options...
cags Posted April 23, 2010 Share Posted April 23, 2010 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}' Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046957 Share on other sites More sharing options...
cyberRobot Posted April 23, 2010 Share Posted April 23, 2010 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. Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046976 Share on other sites More sharing options...
violinrocker Posted April 23, 2010 Author Share Posted April 23, 2010 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 Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046985 Share on other sites More sharing options...
cyberRobot Posted April 23, 2010 Share Posted April 23, 2010 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}' Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046991 Share on other sites More sharing options...
cyberRobot Posted April 23, 2010 Share Posted April 23, 2010 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>"; } } Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1046993 Share on other sites More sharing options...
violinrocker Posted April 23, 2010 Author Share Posted April 23, 2010 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" Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1047001 Share on other sites More sharing options...
cyberRobot Posted April 23, 2010 Share Posted April 23, 2010 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; } Quote Link to comment https://forums.phpfreaks.com/topic/199378-working-with-two-tables/#findComment-1047010 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.