Php Wall script - how to select records marked as "public" or "for friends"?

lpa

I want to make changes in my Wall script so that users can set privacy for each wall post:
- public
- 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.

Thanks.

Ashley_Sheridan

I think it might be a bit of work trying to put all this into one query, but it can be done. Your query will be a bit more complex though:

SELECT fields
FROM wall
WHERE public=1
OR (for_friends = 1 AND user_id IN (sub-query to get list of ids for all friends of original poster) )

That will get you at least to the stage where you have posts marked for friends or as public. I'm not sure what you're doing with the overloading of the for_friends field though. Is it a binary-type indicator of a post marked for friends, or does it contain the id of a special friend group?

I would probably change that field to an ENUM type with the values 'public','friends','friend_group' and then check for that within the WHERE clauses, i.e.

WHERE wall_post_type = 'public'
OR (wall_post_type = 'friends' AND user_id IN (sub-query to get list of ids for all friends of original poster) )

...etc.

Lastly, I'm not sure who told you that OR operators cause problems with indexes. I use OR's in my queries all the time without any problems. As long as you've got your indexes set up right, you won't run into the problems you mention. Create indexes on fields you will be using to WHERE on, and anywhere you want to create JOINs, and you shouldn't run into too many problems.