This query is serving me a home page for users where a user can see all posts by him/her and his/her friends. This is an excellent query without any errors but iI only wish to make it shorter if possible!
OLD TABLES:
select U.fname, U.lname, P.postid, P.status, P.date, U.usernick, P.visible
from posts as P natural join users as U where
U.visible=1 and P.visible=1
and
(P.userid='{$_SESSION['user_id']}' or P.userid in
(select distinct U.userid from users as U inner join friends as F where
(U.userid=F.userid and F.friend='{$_SESSION['user_id']}' and F.status=1) or
(U.userid=F.friend and F.userid='{$_SESSION['user_id']}' and F.status=1)))
or
(P.streamid='{$_SESSION['user_id']}' or P.streamid in
(select distinct U.userid from users as U inner join friends as F where
(U.userid=F.userid and F.friend='{$_SESSION['user_id']}' and F.status=1) or
(U.userid=F.friend and F.userid='{$_SESSION['user_id']}' and F.status=1)))
ORDER BY P.date DESC
CREATE TABLE IF NOT EXISTS `users` (
`userid` INT NOT NULL AUTO_INCREMENT ,
`fname` VARCHAR(45) NOT NULL ,
`lname` VARCHAR(45) NOT NULL ,
`usernick` VARCHAR(45) NOT NULL ,
`useremail` VARCHAR(45) NOT NULL ,
`visible` INT(1) NOT NULL DEFAULT 1 //visible shows id is disables or not
)
CREATE TABLE IF NOT EXISTS `posts` (
`postid` INT NOT NULL AUTO_INCREMENT , //Simple postid index
`userid` INT NOT NULL , //userid of poster
`streamid` INT NOT NULL , // post is posted on his wall
`status` VARCHAR(5000) NOT NULL , //the post that has been posted
`date` TIMESTAMP NOT NULL DEFAULT now(), //post was posted on
`visible` INT(1) NOT NULL DEFAULT 1 ) //post is deleted or not
CREATE TABLE IF NOT EXISTS `network`.`friends` (
`userid` INT NOT NULL , //user who sent friend req
`friend` INT NOT NULL , //to whom friend req has been sent
`status` INT(1) NOT NULL , //0 -> above both are not friends : 1->
//both above are friends
`date` TIMESTAMP NOT NULL DEFAULT now() , //
`visible` INT(1) NOT NULL DEFAULT 1 ) //both are friends still
(People lectured me about Vulnerability on overflow that's why i removed irrelevant info's) UPDATED TABLES:
CREATE TABLE IF NOT EXISTS `network`.`users` (
`userid` INT NOT NULL AUTO_INCREMENT ,
`fname` VARCHAR(45) NOT NULL ,
`lname` VARCHAR(45) NOT NULL ,
`usernick` VARCHAR(45) NOT NULL ,
`useremail` VARCHAR(45) NOT NULL ,
`gender` SET('male','female') NOT NULL ,
`userpass` VARCHAR(45) NOT NULL ,
`dob` DATE NOT NULL ,
`doj` TIMESTAMP NOT NULL DEFAULT now() ,
`visible` INT(1) NOT NULL DEFAULT 1 ,
PRIMARY KEY (`userid`) ,
UNIQUE INDEX `usernick_UNIQUE` (`usernick` ASC) ,
UNIQUE INDEX `useremail_UNIQUE` (`useremail` ASC) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `network`.`posts` (
`postid` INT NOT NULL AUTO_INCREMENT ,
`userid` INT NOT NULL ,
`streamid` INT NOT NULL ,
`status` VARCHAR(5000) NOT NULL ,
`date` TIMESTAMP NOT NULL DEFAULT now() ,
`visible` INT(1) NOT NULL DEFAULT 1 ,
PRIMARY KEY (`postid`) ,
INDEX `userid_fk_p_idx` (`userid` ASC) ,
INDEX `streamid_fk_idx` (`streamid` ASC) ,
CONSTRAINT `userid_fk_p`
FOREIGN KEY (`userid` )
REFERENCES `network`.`users` (`userid` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `streamid_fk`
FOREIGN KEY (`streamid` )
REFERENCES `network`.`users` (`userid` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `network`.`friends` (
`userid` INT NOT NULL ,
`friend` INT NOT NULL ,
`status` INT(1) NOT NULL ,
`date` TIMESTAMP NOT NULL DEFAULT now() ,
`visible` INT(1) NOT NULL DEFAULT 1 ,
INDEX `user_id_fk_idx` (`userid` ASC) ,
INDEX `friend_id_fk_idx` (`friend` ASC) ,
PRIMARY KEY (`userid`, `friend`) ,
CONSTRAINT `user_id_fk`
FOREIGN KEY (`userid` )
REFERENCES `network`.`users` (`userid` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `friend_id_fk`
FOREIGN KEY (`friend` )
REFERENCES `network`.`users` (`userid` )
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
