multiple data entry into database-CodePudding

I want to set up a small social media site. People will be able to add each other as friends. like Facebook. I am using PHP and MySQL. But I couldn't find how to set up the table structure. When people add each other as friends, how should I set this up in the database? There is already a user table.

CodePudding user response：

I assume you have a user table with primary key id.

You can define a new table userfriends with id column as autoincrement primary key. For each new friend you can insert a row to this table. In the SQL code below there is no cascading action defined. May be you want to delete all friends of a user, if a user is deleted. Then you should add corresponding cascading action definition.

CREATE TABLE IF NOT EXISTS `userfriend` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userId` int(10) unsigned DEFAULT 0,
  `friendId` int(10) unsigned DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `FK_userfriend_user` (`userId`),
  KEY `FK_userfriend_user_2` (`friendId`),
  CONSTRAINT `FK_userfriend_user` FOREIGN KEY (`userId`) REFERENCES `user` (`id`),
  CONSTRAINT `FK_userfriend_user_2` FOREIGN KEY (`friendId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CodePudding user response：

One possibility is a column of friends for your user table. If this user has a unique ID (primary key), you could append to the column of the friend's user IDs followed by a space or comma to separate them. When you want to print out the list of friends, query for column of friends and row of the user that's logged in, use a PHP function such as explode(), preg_split() or str_split() to split each user ID.