I want to acquire users who love both through SNS using SQLite and PHP.

I would like to limit the number of users who can send messages to each other on SNS using SQLite and PHP to users who love each other (who follow each other).
What SQL statements should I issue to get a list of users (IDs) who are in love with me if I am the ID of :id?
The follow table looks like this:
from_id —Followed user ID
to_id —Followed user ID

php sqlite pdo

2022-09-30 19:26

3 Answers

If your ID is 1, assume the following contents of the follow table:

 from_id | to_id
--------+--------
1       | 10
1       | 11
1       | 12
2       | 1
11      | 1
12      | 1
12      | 2
12      | 11

In this case, you will have three users: 10, 11, and 12, and you will have three users:

To find a two-sided user, combine the following tables (one is following, the other is following), and if the following.to_id and follower.from_id, following.from_id and follower.to_id match, you can get them as two-sided users.

SELECT
  following.to_id
FROM follow following
  INNER JOIN FOLLOW FOLLOWER
    ON following.to_id=follower.from_id AND following.from_id=follower.to_id
WHERE following.from_id=:id

2022-09-30 19:26

How about a side inquiry?
The method is to create a set of from_id's 1, and to_id's to_id's to_id's to and from_id's to_id's to_id's to_id's to_id's to_id's to_id.

<?php
$pdo = new PDO('sqlite:test.db');
$pdo->query("CREATE TABLE follow(from_id, to_id)");
$pdo->query("INSERT INTO follow VALUES(1,2)");
$pdo->query("INSERT INTO follow VALUES(1,3)");
$pdo->query("INSERT INTO follow VALUES(2,1)");
$pdo->query("INSERT INTO follow VALUES(3,1)");
$pdo->query("INSERT INTO follow VALUES(3,2)");
$sql="SELECT from_id FROM follow WHERE from_id IN (SELECT to_id FROM follow WHERE from_id=:id) AND to_id=:id";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':id',2,SQLITE3_INTEGER);
$stmt->execute();
while($result=$stmt->fetch(PDO::FETCH_ASSOC)){
    var_dump($result);
    echo "<br>";
}
$pdo->query("DROP TABLE follow");

2022-09-30 19:26

Why don't you just combine yourself?
If you search by self-combination or recursive combination, you will get too many results, so I will not attach a reference link for now.
I think the specific SQL of the question is as follows, but is this inconvenient?

SELECT following.from_id, following.to_id
  FROM follow following, followed
    WHERE
      following.to_id=followed.from_id 
    AND
      followed.to_id=following.from_id
  ;

2022-09-30 19:26

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656