I want to make changes in my Wall script so that users can set privacy for each wall post:
- for all friends
-for specific group of friends.
So far I have changed everything. Now I am confused how can I select appropriate posts from the database. The query should look like this:
$result = mysql_query("SELECT id, text, etc... FROM wall
WHERE public='1' or for_friends='1' or for_friends='$my_friend_group'
order by id DESC limit $start, $step") or die('Error');
But this query will display all records marked as "public" or "for_friends" without checking if the person is a friend of the poster.
(for_friends='1' - means that this post can be viewed by all friends; for_friends='my_friend_group' - means that this post can be viewed only by friends which belong to a specific group.)
Problem N 1
Each wall post belongs to a different owner and before selecting posts the query must know if I am a friend of the owner of this post to select only those posts where for_friends='1' or for_friends='$my_friend_group' . Of course, I have "friends_relations" table with user IDs where is possible to check if 2 users are friends..
Problem N 2
"OR" operators always cause problems with indexes + create more problems like filesort, using temporary etc. I would preffer not to use "or" if it's possible.
I need an idea for database and query structure to select the appropriate posts from a really huge table and to avoid problems with "or". I can create additional tables/columns, that's not a problem. I just need an idea.
Can anyone help? I am willing to spend some money. I had an idea to buy some social network script to see how they do it but I have a bad experience with it. There are many paid scripts with non-optimized queries that can crash the server if the number of users are higher than 50 or 100.