php - 如何根据已选行中的值选择行

Question

这是我的mysql数据库。我有 ID、用户名、连接:

 id   | username | connect
 -----+----------+----------
  1   | a        |  
  2   | b        | 1 
  3   | c        | 1
  4   | d        | 2
  5   | e        | 3
  6   | f        | 2
  7   | g        | 3
  8   | h        | 4
  9   | i        | 5
 10   | j        | 6

我是这样显示的:1 级用户 a 与 2 个用户连接，因此它的连接计数应该为 2:

Level   |  Connect Count      
--------+----------------
  1     |     2

但现在我想显示 10 级结果。我通过在连接行中选择 1 级 ID 来显示 1 级结果。

for level 2 I want to search level 1 searched id 
for level 3 I want to search level 2 searched id
for level 4 I want to search level 3 searched id 
for level 5 I want to search level 4 searched id
for level 6 I want to search level 5 searched id 
for level 7 I want to search level 6 searched id
for level 8 I want to search level 7 searched id 
for level 9 I want to search level 8 searched id
for level 10 I want to search level 9 searched id

这样显示

Level  |   Connect Count      
-------+----------------
  1    |      2
  2    |      2
  3    |      2
  4    |      1
  5    |      1
  6    |      1
  7    |      0
  8    |      0
  9    |      0
 10    |      0

我当前的 php 代码

<?php
$conn=mysqli_connect("localhost","root","","");
$i = 1;
// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql="select count('$id') from user where connect = '$id'";
$result=mysqli_query($conn,$sql);
$sql="select * from user where connect = '$id'";
$resultt=mysqli_query($conn,$sql);
$row=mysqli_fetch_array($result);
$roww=mysqli_fetch_array($resultt);
// display records in a table
echo '<div style="overflow-x:auto;">';
echo "<table>";
// set table headers
echo "<tr><th>Level</th><th>User Count</th></tr>";
echo "<tr>";
echo "<th>$i</th><th>" .$row[0].  "</th><th></th>";
echo "</tr>";
$i++;
echo "</table>";
echo '</div>';
mysqli_close($conn);
?>

Answer 1

您可以在查询中使用自联接来做到这一点:

select    a.id, 
          count(b.id) connect_count 
from      user a
left join user b on a.id = b.connect
group by  a.id