php - 从不同的表中选择数据，匹配并计算具有相同值的数字

Question

Edit: I have 2 tables in my database: files and user_logs. I want to print all the values ​​in files and match them with similar values in user_logs.

The columns in user_logs and files are the same:

  +++++++++++++++++++++
  + fileName | fileId +
  +++++++++++++++++++++

And I want the following output:

  +++++++++++++++++++++++++++++++++
  + fileName | no. of same values +
  +++++++++++++++++++++++++++++++++
  + fileNo1 |          3          +
  +++++++++++++++++++++++++++++++++
  + fileNo2 |          1          +
  +++++++++++++++++++++++++++++++++
  + fileNo3 |          0          +
  +++++++++++++++++++++++++++++++++

I don't understand what I researched. Here's my code:

$sql = "SELECT user_logs.fileName,count(*) as c FROM user_logs JOIN files ON user_logs.fileId = files.fileId UNION ALL SELECT files.fileName as file from files a group by a.fileId DESC ";
$result = mysqli_query($con,$sql);
while ($row = mysqli_fetch_assoc($result)) {
        $num_row = $row['c'];
    echo "<tr>";
        echo "<td>".$row['fileName']."</td>";
        echo "<td><a href='#' >".$num_row ." / 2 Schools Clicked</a></td>";      
    echo "</tr>";
    }

Answer 1

If I understand you, I think you want something like this as your query:

SELECT fileName, IF(t.cnt IS NULL, 0, t.cnt) AS c
FROM files LEFT JOIN
(SELECT count(*) AS cnt, fileId FROM user_logs GROUP BY fileId) AS t
ON files.fileId = t.fileId
ORDER BY fileName