Followers system php mysql

0

Good people,

I need to make a follow-up system for my site users like notifications but I never did and I do not know where to start, I needed an idea how I can do this system.

    
asked by anonymous 03.02.2015 / 18:14

1 answer

1

You will need a basic Many-to-Many _report in your bank (a user can be followed by 0-n users, and can follow 0-n users). Basically, you'll need a table that stores follower and followed , here's an example of those 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: In the usuario table you will have any of your users' data as any default user table, in your conexao relationship table, you will store id user to be followed and user follower ( followed and follower respectively).

Example query to see who follows the 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 this response:

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);
    
03.02.2015 / 19:22