Follow users php mysql system

Question

Good people,

I need to make a follow system for users of my site as notifications but I never did and I don’t know where to start, I needed an idea of how I can make this system.

Answer 1

You’ll need a relationship Many-to-Many basic in your bank (a user can be followed by 0-n users, and can follow 0-n users). Basically, you will need a table that stores the follower and the followed, follows an example of these tables:

mysql> desc usuario;
+---------+-------------+------+-----+-------------------+----------------+
| Field   | Type        | Null | Key | Default           | Extra          |
+---------+-------------+------+-----+-------------------+----------------+
| id      | int(10)     | NO   | PRI | NULL              | auto_increment |
| nome    | varchar(75) | NO   |     | NULL              |                |
| created | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
+---------+-------------+------+-----+-------------------+----------------+

mysql> desc conexao;
+----------+---------+------+-----+---------+----------------+
| Field    | Type    | Null | Key | Default | Extra          |
+----------+---------+------+-----+---------+----------------+
| id       | int(10) | NO   | PRI | NULL    | auto_increment |
| follower | int(10) | NO   | MUL | 0       |                |
| followed | int(10) | NO   | MUL | 0       |                |
+----------+---------+------+-----+---------+----------------+

Explaining: on the table usuario you will get any data from your users as any default user table, in your relationship table conexao, you will store the id user to be followed and the user’s follower (followed and follower respectively).

Example query to see who follows user x:

SELECT usuario.nome
  FROM conexao
 INNER JOIN usuario ON conexao.follower = usuario.id
 WHERE conexao.followed = @usuario_x_id;

Code CREATE and INSERT of that answer:

CREATE TABLE IF NOT EXISTS `usuario` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `nome` varchar(75) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

INSERT INTO `usuario` (`id`, `nome`, `created`) VALUES
    (1, 'Eduardo', '2015-02-03 16:11:22'),
    (2, 'Maria', '2015-02-03 16:11:29'),
    (3, 'João', '2015-02-03 16:11:36');

CREATE TABLE IF NOT EXISTS `conexao` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `follower` int(10) NOT NULL DEFAULT '0',
  `followed` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `FK__usuario` (`follower`),
  KEY `FK__usuario_2` (`followed`),
  CONSTRAINT `FK__usuario` FOREIGN KEY (`follower`) REFERENCES `usuario` (`id`),
  CONSTRAINT `FK__usuario_2` FOREIGN KEY (`followed`) REFERENCES `usuario` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO `conexao` (`id`, `follower`, `followed`) VALUES
    (1, 1, 2),
    (2, 1, 3),
    (3, 2, 3),
    (4, 3, 1);