Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Show updates from a table, (facebook wall) - php

Posted on 2010-09-11
10
Medium Priority
?
658 Views
Last Modified: 2013-12-12
This for something like facebook wall, our updates and friends updates. Right now I have a table to keep updates and another table  to keep friends information for a specific user(user_id and his/her 's friends ids). There is a user table and a page for each user; when someone visit the page, we check for the users friends and show updates from the update table of the user and users friends. What should I do to get something like this?. I dont want the coding, just a method or a way to do it.

Thanks


P.S:- Using php and mysql
0
Comment
Question by:sahanz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
10 Comments
 
LVL 6

Expert Comment

by:birwin
ID: 33657336
We probably need more information. I assume you are using PHP/mySQL, since you posted in this section.
How are you storing the friends relationship? What data is held in the "Friend's Information table?
What are you trying to update.
How is that update stored on the "Updates Table"? Is there a line per change with a datestamp and username? How are the changes noted? Is the Update Table" data ever moved to the User Table?
Brian
0
 
LVL 1

Author Comment

by:sahanz
ID: 33680737
Hi,

Let me explain in other way, I have a user_table which contains users information such as username email address etc, and each of the user has an ID, Another table contains relationship between the users of the table, lets say the table is "friends", in front end these relations are working like this, users can become friends, so we're keeping the relationships between friends in "friends" table. Another table called "updates" has status updates(like in twitter) for each user, so the updates table has a ID for each update, text column for the update text, and user_id column coz we need to know who did this update.


For the users in "user_table" we have a profile page, we list the users details and his/her updates. Along with users updates we need to list his/her friends updates, lets say users who has the ID 1  and 2 are friends, and both of these users have status updates in "updates" table. Lets say someone visit the profile page which is for user ID-2 we grab the updates from "UPDATES" according to the user's id and show them in the page, but like I said I need to show the updates of this user, in this case updates of user ID - 2. But how to do that?
0
 
LVL 9

Accepted Solution

by:
Snarfles earned 2000 total points
ID: 33847089
You will need to perform 2 queries I think. First from the profile page, select all friend id's from the friends table where friend is the userid of the current person.

eg

SELECT friend_id_2 FROM friends WHERE friend_id_1 = '123';

Assuming that your friends table has 2 sets of user id's for the relationship.

Next build a comma deliminated list from the above so that you end up with something like this

$friend_list = "2,56,67,89,90";

Eg the current person's profile is friends with the id's 2,56,67,89,90. You will also want to tack on the user id of the current person

eg

$friend_list = $friend_list.",".user_id;

Next use that list in a new query from your updates table

SELECT * FROM updates WHERE user_id IN ($friend_list);

This should get you a query of all of the updates of the current user as well as the updates of all of his/her friends.

Make sense?
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 33855843
You might be able to use Snarfles suggestion in a single query.  Something like this...

SELECT * FROM updates WHERE user_id IN (SELECT friend_id_2 FROM friends WHERE friend_id_1 = '123');
0
 
LVL 9

Expert Comment

by:Snarfles
ID: 33856180
It would need the current users id in the list as well Ray, so assuming your select works then it would need to be something like

SELECT * FROM updates WHERE user_id IN (SELECT DISTINCT friend_id_2 FROM friends WHERE friend_id_1 = '123' AND friend_id_2 = $user_id);

So adding to the query is an additional condition "AND friend_id_2 = $user_id" to get the current user's id in the query and then also "DISTINCT" will tell it to only return unique rows so that you dont get 100 versions of the current user_id in the list.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 33858951
Yeah, good point.  I tend to test these things one-line-at-a-time when it's my own work.  DISTINCT and GROUP BY or something like that usually percolates up.  But I'm not sure whether it would matter if you had multiples in the second-tier select.  I'm sure it would not hurt to have a distinct list, but wouldn't the non-distinct query look somewhat like this:

SELECT x FROM updates WHERE user_id IN (101,101,101,123,123,456)

I've never observed the behavior, but MySQL might be smart enough to do something like array_unique() with that list.  Would be an interesting thing to test.
0
 
LVL 1

Author Comment

by:sahanz
ID: 33976653
Hi, First one works well, and second one works well without the current users id, as soon as I put "AND" and user it it returns empty results.
0
 
LVL 1

Author Closing Comment

by:sahanz
ID: 34000288
Hi, First one works well, and second one works well without the current users id, as soon as I put "AND" and user it it returns empty results.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question