Блог

Я хочу сделать так, чтобы скрипт отображал количество совпадений значений у разных пользователей. Например, в таблице watched у меня есть идентификатор моего основного пользователя (1) и названия просмотренных им фильмов, а также просмотренные фильмы несколькими другими пользователями:

  ---- ---------- --------- 
| id | users_id | watched |
 ---- ---------- --------- 
| 1  | 1        | movie1  |
 ---- ---------- --------- 
| 2  | 1        | movie2  |
 ---- ---------- --------- 
| 3  | 1        | movie3  |
 ---- ---------- --------- 
| 4  | 2        | movie2  |
 ---- ---------- --------- 
| 5  | 2        | movie1  |
 ---- ---------- --------- 
| 6  | 3        | movie1  |
 ---- ---------- --------- 
| 7  | 3        | movie5  |
 ---- ---------- --------- 
| 8  | 3        | movie4  |
 ---- ---------- --------- 
  

Ожидаемый результат:

 my id = `1`;
matched with id `2` 2 times on titles `movie2`, `movie1`;
matched with id `3` 1 times on titles `movie1`;
  

Или если идентификатор основного пользователя 3 :

 my id = `3`;
matched with id `1` 1 times on titles `movie1`;
matched with id `2` 1 times on titles `movie1`;
  

И так далее..

Я пытался сделать что-то вроде:

 <?php
session_start();
$connect = mysqli_connect("127.0.0.1", "root", "root", "movie");

$query = mysqli_query($connect, "SELECT * FROM profile INNER JOIN users ON profile.users_id = users.id");

$id = $_SESSION['id'];
echo "my id: " . $id;

$movies = mysqli_query($connect, "SELECT * FROM watched WHERE users_id = '$id'");

$titles = array();
for ($i = 0;$i < $movies->num_rows;$i  )
{
    $result2 = $movies->fetch_assoc();
    $watched = $result2['watched'];
    $find = mysqli_query($connect, "SELECT * FROM watched WHERE watched = '$watched'");

    for ($l = 0;$l < $find->num_rows;$l  )
    {
        $found = $find->fetch_assoc();
        if ($found['watched'] === $result2['watched'] and $found['users_id'] !== $id)
        {
            $titles[] = $found['watched'];

        }
    }
}

$count_titles = array();
foreach ($titles as $a)
{
    @$count_titles[$a]  ;
}
$matches = count($count_titles); //num of matches

for ($i = 0;$i < $query->num_rows;$i  )
{
    $result = $query->fetch_assoc();

?>

<div><?php
echo "matched with id " . $result['id'] . " ";

echo $matches . " times";

foreach (array_keys($count_titles) as $key)
{
    echo " on titles " . $key;
}

?></div> 


<?php
} ?>
  

Но он отображает только общее количество совпадений по всей таблице, а не для каждого пользователя в отдельности. Как мне заставить это работать, как описано выше?

три таблицы:

 CREATE TABLE watched (
  `id` INTEGER,
  `users_id` INTEGER,
  `watched` VARCHAR(6)
);

INSERT INTO watched
  (`id`, `users_id`, `watched`)
VALUES
  ('1', '1', 'movie1'),
  ('2', '1', 'movie2'),
  ('3', '1', 'movie3'),
  ('4', '2', 'movie2'),
  ('5', '2', 'movie1'),
  ('6', '3', 'movie1'),
  ('7', '3', 'movie5'),
  ('8', '3', 'movie4');

  CREATE TABLE users (
  `id` INTEGER,
  `name` VARCHAR(355),
  `email` VARCHAR(355)
);

INSERT INTO users
  (`id`, `name`, `email`)
VALUES
  ('1', 'name1', 'email1@mail.com'),
  ('2', 'name2', 'email2@mail.com'),
  ('3', 'name3', 'email3@mail.com');

  CREATE TABLE profile (
  `id` INTEGER,
  `users_id` INTEGER,
  `about` VARCHAR(355)
);

INSERT INTO profile
  (`id`, `users_id`, `about`)
VALUES
  ('1', '1', 'something about me'),
  ('2', '2', 'something about me'),
  ('3', '3', 'something about me');
  

Этот запрос предоставит вам необработанные данные, которые вы хотите:

 SELECT w1.users_id AS user1, w2.users_id AS user2, GROUP_CONCAT(w2.watched ORDER BY w2.watched) AS movies
FROM watched w1
JOIN watched w2 ON w2.watched = w1.watched AND w2.users_id != w1.users_id
GROUP BY user1, user2
  

Вывод (для вашего примера данных):

 user1   user2   movies
1       2       movie1,movie2
1       3       movie1
2       1       movie1,movie2
2       3       movie1
3       1       movie1
3       2       movie1
  

Демонстрация на dbfiddle

Вы можете уточнить запрос по мере необходимости (например, добавить WHERE w1.users_id = 1 , чтобы выбрать только совпадения с пользователем 1, или JOIN в свои таблицы users и profile (по одному разу для каждого идентификатора пользователя), чтобы получить имена пользователей и т.д.)

Чтобы получить желаемый результат, вам больше не нужны таблицы, вы можете просто использовать что-то вроде:

 $id = $_SESSION['id'];
echo "my id: $idn";
$movies = mysqli_query($connect, "SELECT w1.users_id AS user1, 
                                         w2.users_id AS user2, 
                                         COUNT(w2.watched) AS num_movies, 
                                         GROUP_CONCAT(w2.watched ORDER BY w2.watched) AS movies
                                  FROM watched w1
                                  JOIN watched w2 ON w2.watched = w1.watched AND w2.users_id != w1.users_id
                                  WHERE w1.users_id = $id
                                  GROUP BY user1, user2");
while ($row = $movies->fetch_assoc()) {
    echo "matched with id {$row['user2']} {$row['num_movies']} times on titles {$row['movies']}n";
}