Geek Answers Handbook

Mysql friends online display

Ok, so I have a headech from searching everywhere on how to solve this problem. I'm trying to show online users, but not all users, only those on your friends list.

So, I have a table called users_online, and when a user logs in to my website in this table, 1 row is automatically created with the date, ip, name, user_id and friend_array (where all friends of friends are saved)

So, for example, I enter my site and a row is created in the users_online table. I want to see only my friends on the Internet, and these friends are stored in the friend_array column (1,5,16,5 (this is the identifier number of friends)). How can I take data from col_ru__ru_ru and find out which of these identifiers is registered? at the moment, what means which of these identifiers exists in the user_online table and is displayed in my profile?

Hope this is not a confusing question ...

Well, this is my code ... everything is stored in the online.php file:

// Checking wheter the visitor is already marked as being online: $inDB = mysql_query("SELECT user_id FROM users_online WHERE user_id=".$userid); if(!mysql_num_rows($inDB)) { // Selects some data required to insert into users_online table from users table $DB = mysql_query("SELECT img,fname,friend_array FROM users WHERE user_id=".$userid); while($row=mysql_fetch_assoc($DB)) { $img = $row['img']; $fname = $row['fname']; $farray = $row['friend_array']; } mysql_query(" INSERT INTO users_online (user_id,ip,img,fname,friend_array) VALUES(".$userid.",'".$intIp."','".$img."','".$fname."','".$farray."')"); } else { // If the visitor is already online, just update the dt value of the row: mysql_query("UPDATE users_online SET dt=NOW() WHERE user_id=".$userid); } // Counting all the online visitors: // Thats where i need to work out with friend array.. // I need to display all online friends only list($totalOnline) = mysql_fetch_array(mysql_query("SELECT COUNT(*) FROM users_online")); // Outputting the number as plain text: echo $totalOnline;
+4
source share
3 answers
 <?php $friends = array(1,5,16); // Array of friends $friendIDs = implode(',', $friends); // Turns array into string for SQL select statement // Gets only friends info from DB $sql = " SELECT date, ip, name, user_id FROM users_online WHERE user_id IN (".$friendIDs.")"; ?>
+2
source

Sorted guys! Thank you for your help!

 //Selecting an array from db $DB = mysql_query("SELECT friend_array FROM users_online WHERE user_id=".$userid); while($row=mysql_fetch_assoc($DB)) { $friend_array = $row['friend_array']; $friends = array($friend_array); // Array of friends $friendIDs = implode(',', $friends); } // Counting all the online friends: list($totalOnline) = mysql_fetch_array(mysql_query(" SELECT COUNT(*) FROM users_online WHERE user_id IN (".$friendIDs.")")); // Outputting the number as plain text: echo $totalOnline;

The output was 1 user online, since I was registered in 2 browsers, and in the users_online table 2 rows were created with identifiers 1 and 2. And id 1 had 3 friends in the array (2,5,16), and the user with id 2 had (1.3). So in each browser the output was 1..Uray! I hope this question helps someone ... I use the update function in the msql table, and if the user logs out, I just delete the row in the users_online table :)

+2
source

You should consider changing the database schema. Here is an example:

USER table:

 id | name | current_session_id

USER_FRIEND_USER table (contains friends added by the user):

 user_id | friend_user_id

SESSION TABLE:

 id | user_id | created_at | expires_at

SQL Query to get friend list for user with id '?':

 SELECT u.id, u.name FROM USER u INNER JOIN USER_FRIEND_USER ufu ON (ufu.friend_user_id = u.id) INNER JOIN SESSION s ON (s.id = u.current_session_id) WHERE ufu.user_id = ? AND s.expires_at >= NOW();

Here's the SQL Fiddle link: http://sqlfiddle.com/#!2/34f83/1/0

0
source

More articles:


All Articles