Writing a update on friend wall, this is the most important part in social networking sites. Famous networking sites are like Facebook and Orkut but people calling different like wall and scrap. Now Twitter testing this feature. I have enable this option in labs.9lessons.info. This post explains you how to design database and table relationships for posting a update on friend wall..
Live DemoNote: Before reading the post you have to follow my labs.9lessons.info database design previous posts.
- Part 1: Database Design Create Tables and Relationships with SQL
- Part 2: Join Tables Relationships with SQL
users table
Parent table contains all users data, while registration data storing in this table. Eg : labs.9lessons.info/9lessonsCREATE TABLE `users` (
`user_id` INT NOT NULL primary key AUTO_INCREMENT ,
`username` VARCHAR(45) NULL unique,
`password` VARCHAR(45) NULL ,
`email` VARCHAR(45) NULL ,
`twitter_token` VARCHAR(99) NULL ,
`twitter_token_secret` VARCHAR(99) NULL
);
`user_id` INT NOT NULL primary key AUTO_INCREMENT ,
`username` VARCHAR(45) NULL unique,
`password` VARCHAR(45) NULL ,
`email` VARCHAR(45) NULL ,
`twitter_token` VARCHAR(99) NULL ,
`twitter_token_secret` VARCHAR(99) NULL
);
Data storing like this here password stored in encrypted formate tutorial link.
updates table
Contains all users status updates data. Here user_id_fk is FOREIGN KEY to REFERENCES users.user_id and owner is FOREIGN KEY to REFERENCES users.user_id Eg : labs.9lessons.info/9lessonsCREATE TABLE `updates` (
`update_id` INT NOT NULL primary key AUTO_INCREMENT ,
`update` TEXT NULL ,
`time` INT NULL ,
`host` VARCHAR(45) NULL , // Client IP address
`vote_up` INT NULL , // Up votes data
`vote_down` INT NULL , // Down votes
`user_id_fk` INT NULL ,
`owner` INT NULL ,
FOREIGN KEY (user_id_fk) REFERENCES users(user_id),
FOREIGN KEY (owner) REFERENCES users(user_id)
);
`update_id` INT NOT NULL primary key AUTO_INCREMENT ,
`update` TEXT NULL ,
`time` INT NULL ,
`host` VARCHAR(45) NULL , // Client IP address
`vote_up` INT NULL , // Up votes data
`vote_down` INT NULL , // Down votes
`user_id_fk` INT NULL ,
`owner` INT NULL ,
FOREIGN KEY (user_id_fk) REFERENCES users(user_id),
FOREIGN KEY (owner) REFERENCES users(user_id)
);
Friend Wall Update
Insert statement for fried wall update here $session_id refers to login user eg:srinivas (1). Notice the above table data record update_id=4 Srinivas(user_id_fk=1) post a update on Arnold(owner=2) wall.INSERT INTO updates
(update,user_id_fk,time,host,owner)
VALUES
('$update','$session_id','$time','$ip','$wall_user_id');
(update,user_id_fk,time,host,owner)
VALUES
('$update','$session_id','$time','$ip','$wall_user_id');
User Wall Update
Here user writing on his wall so user_id_fk and owner both same value. Take a look at the about image record update_id=1 Srinivas updated his wall user_id_fk=1 and owner=1INSERT INTO updates
(update,user_id_fk,time,host,owner)
VALUES
('$update','$session_id','$time','$ip','$session_id');
(update,user_id_fk,time,host,owner)
VALUES
('$update','$session_id','$time','$ip','$session_id');
Home Feed Updates
Data relation between users, updates and friends tables. The following SQL statement users table object a , updates table object b and friends table object c . Here friend_one = '1' refers to users table user_id value.SELECT a.username, a.email, b.update_id, b.update, b.time, b.vote_up, b.vote_down
FROM users a, updates b, friends c
WHERE b.owner = a.user_id
AND c.friend_one = '1'
AND b.user_id_fk = c.friend_two
ORDER BY b.update_id DESC
LIMIT 15;
FROM users a, updates b, friends c
WHERE b.owner = a.user_id
AND c.friend_one = '1'
AND b.user_id_fk = c.friend_two
ORDER BY b.update_id DESC
LIMIT 15;
Profile Updates
Data relations between users and updates tables. The following SQL statement updates table object a and users table object b ,. Here user_id_fk = '1' refers to users table user_id value.SELECT a.update_id,a.update,a.time,b.username,b.email
FROM updates a,users b
WHERE a.owner=b.user_id
AND a.user_id_fk='1'
ORDER BY a.update_id DESC;
FROM updates a,users b
WHERE a.owner=b.user_id
AND a.user_id_fk='1'
ORDER BY a.update_id DESC;
It's a nice one here Srinivas. Thanks
ReplyDeleteLive Demo not working..
ReplyDeleteLive Demo no working
ReplyDeletehey .. demo page not found !!! :(
ReplyDeletecan't view the live demo :( an error Not Found
ReplyDeleteThe requested URL /high_cpu.htmlsrinivas was not found on this server.
Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request.
This is greate Srinivas, but i didn't quite get the database schema for the `friends` table
ReplyDeleteNice Information.
ReplyDelete@Demo server overload please try after 5 min. I'm planning to buy Amazon Cloud
ReplyDeleteHi, is it possible to download this script?
ReplyDeleteGood work and good guide i made my own but a few of your scripts are very good mainly anything google :)
ReplyDeleteCan you please tell me where is the friends table?
ReplyDelete@Friends Table
ReplyDeleteReal part 1 and part 2
can you also show us a "People you may know" and how to import contacts from msn, yahoo
ReplyDeleteNice
ReplyDeleteThank for sharing this, i think this will help many people.
ReplyDeleteJust one advice, use PDO instead of old mysql call, because it'll be include by default in the release 6.0
Don't copy paste the code if you don't understand it (to readers).
$someObject->somePDOMethod( 'SELECT `a.update_id,a.update`,`a.time,b.username`,`b.email`
FROM `updates a`,`users b`
WHERE `a.owner`=`b.user_id`
AND `a.user_id_fk`=1
ORDER BY `a.update_id` DESC LIMIT X'
,
$someCustomBool);
Good Luck and thank you.
Nicolas
how to add friends script??
ReplyDeleteThat was nice one from you Srinivas...
ReplyDeleteThat was nice
ReplyDeleteYou rock!!
ReplyDeletei love your tutorials
ReplyDeletenice
ReplyDeletemy problem now is the session, i cant call the session id, i just can call the session of the users' emails. i'm stuck in the session. i dont want to insert the session id manually, i want to insert it automatically according to email registered...anyone can help me??? need your help so much...thanks...
ReplyDeletethanks tamada , but please make files to download
ReplyDeleteRespect, thank you dude :) ...
ReplyDeletenice......!
ReplyDeleteIs the updates table the same as the comments table? Would be nice to to have files for download.
ReplyDeleteYour Tutorials ROCK!!! THANKS
Hi. This will help many people including me. While I'm building a website. thanks
ReplyDeleteThanks to share these codes i selected sclnetwrk as my mini project
ReplyDeletecan you make a downloadable of this tutorial please?
ReplyDelete..
ReplyDeleteIts verry cool this tutorial, is exactly wath i needed to make my school project :D Its very cool that people like you help others with this kind of tutorials :D
ReplyDeleteThanks men, you're awesome :D
Hi i followed your tutorial and changed the update function from your wallscript4.0
ReplyDeletebut instead of c.friend_one and c.friend_two mine is P.user_id and P.user_id_2
The following only shows the friends messages and not the users own messages:
This is the WHERE section of the query
WHERE M.uid_fk=U.uid AND P.user_id='$uid' AND M.uid_fk=P.user_id_2
I've been messing with this query for ages
David
I need to buy the wall script 4.0 and to get detail tutorial on how to use it with lab application. How do i make a payment since my country in not welcomed
ReplyDeleteHi srinivas
ReplyDeleteplease give me a script about if i had already add someone as friend then in place of "add connection" button show "Drop Connection" button
nice article , user for me.
ReplyDeletei have one doubt that how i know user is my friend or not. if i send request to my friend then which table of column should be updates so that he is added to my friend list..
can u help me ?.....
satyawan shinde
nice article , user for me.
ReplyDeletei have one doubt that how i know user is my friend or not. if i send request to my friend then which table of column should be updates so that he is added to my friend list..
can u help me ?.....
satyawan shinde
nice article , user for me.
ReplyDeletei have one doubt that how i know user is my friend or not. if i send request to my friend then which table of column should be updates so that he is added to my friend list..
can u help me ?.....
satyawan shinde
hi,
ReplyDeleteplease show the friend table also.
nice blog.
awesome
ReplyDeleteThanks good article.
ReplyDeletereally good but i have one doubt how to update and insert data using primarykey!
ReplyDelete