Peruttu

Need just 1 MySQL query to select data for Facebook style wall

I want to add a Facebook style wall to my website (MySQL, Php). The problem is that I don't know how to create a MySQL query to select data from database because it looks very complicated. An example what I need you can see in PhpFox script demo.

I need this query very fast and very good optimized and indexed so that it works with huge tables and DOES NOT use using_temporary or using_filesort or other things that may kill the server.

Description:

When user posts something to the wall, he/she can select people who can view this post:

1. Public post - this post can be viewed by all users (subscribers).

2. Only subscribed friends (this post can be viewed by all friends of this user)

3. Only specific group of friends who are subscribed (this post can be viewed only by those friends who belong to a specific group).

Users can subscribe to posts of other people. When they enter the website, they see posts they are subscribed for and posts that they may view.

So, when I enter the website, the following posts must be displayed to me:

1. Posts of people whom I have saved into "subscriptions" table if posts are marked as "public".

2. Posts of my friends if they are saved into "subscriptions" table, but not all posts - only those which are marked "for all friends" and posts which are marked "for specific friends group" if I belong to this friends group of the poster.

Posts should be ordered "by id DESC "and limited by "$page, 20".

As you see, everything is very complicated. But that's not all.

Each post may have comments and I need to load last 5 comments of each post, but to display them in ascending order. (select from comments table 5 posts in DESC order but to display them in ASC mode). The comments must be loaded with the same query as posts (or in anothey way WITHOUT a separate query for each post in while loop)

Query which loads posts and comments must join Users table to load name, surname and photo of poster and comment poster. So, there are 4 tables involved, something like this. You can create more tables if needed or use less tables, it's not important.

CREATE TABLE IF NOT EXISTS `subscriptions` (

`owner_id` int(11) NOT NULL,

`user_id` int(11) NOT NULL,

`information` varchar(255) NOT NULL,

PRIMARY KEY (`uid1`,`uid2`),

KEY `uid2` (`uid2`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `friends` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`owner_id` int(11) NOT NULL DEFAULT '0',

`user_id` int(11) NOT NULL DEFAULT '0',

`information` varchar(255) NOT NULL,

`friends_group` int(11) NOT NULL DEFAULT '0'

PRIMARY KEY (`id`),

KEY `user_id` (`user_id`,`owner_id`),

KEY `friends_group` (`owner_id`,`friends_group`),

KEY `owner_id` (`owner_id`,`id`,`friends_group`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

CREATE TABLE IF NOT EXISTS `posts` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`poster_id` int(11) NOT NULL,

`text` text NOT NULL,

`type_of_post` smallint(3) NOT NULL,

`number_of_comments` mediumint(5) NOT NULL,

`like` mediumint(5) NOT NULL,

`group_who_can_view` int(11) NOT NULL,

UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

//group_who_can_view - if value is "0" this post can bi viewed by all subscribers. If value is "1" this post can be viewed by all subscribed friends. If value is other than "0" and "1" this post can be viewed only by subscribed friends who belong to a specific group (this value).

CREATE TABLE IF NOT EXISTS `posts_comments` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`comment_poster_id` int(11) NOT NULL,

`parent_id` int(11) NOT NULL,

`comment` text NOT NULL

UNIQUE KEY `id` (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

Taidot: MySQL, PHP, tietojärjestelmäarkkitehtuuri

Näytä lisää: what software needed to create a website, unique comments for facebook, the select group, problem of facebook, poster my wall, php script null, like comment subscribe, i want to create a website like facebook, i want to create a facebook page, indexed data, how to null php script, how to null a script, how to null a php script, how to create website by php, how to create a website with database, how to create a website like facebook, facebook group all posts to website, asc it, facebook wall select mysql, php mysql script, what people need, wall post, select, query table, poster\ and 1=2;#

Tietoa työnantajasta:
( 0 arvostelua ) Santa Cruz De Tenerife, Spain

Projektin tunnus: #4266679

1 freelancer is bidding on average €50 for this job

paulchun

I have done database and server scripting projects like this before and am the person to help you get it done! Contact me and we will start getting things moving so that you can get this done as soon as possible, makin Lisää

50 € EUR 2 päivässä
(0 arvostelua)
0.0