php - 如何连接两个表中的两个mysql查询

Question

我有两个独立的查询:

查询 1(此查询从我的表中获取所有图库):

$showgallerys = mysqli_query($con,"SELECT * FROM canais");
while($row = mysqli_fetch_array($showgallerys)) {
echo '<div class="canal-nome">'.$row['nome'].'</div>;

查询 2(此查询计算每个类别的照片数量):

$q="SELECT categoria, COUNT(titulo) FROM galerias GROUP BY categoria ";
$res=mysqli_query($con,$q);
while($row = mysqli_fetch_array($res)){
 echo '
('. $row['COUNT(titulo)'] .')';
}

我需要显示图库名称(查询 1)和照片数量(查询 2)

喜欢这个图库名称(30)

这是第一个表的结构(称为canais):

  id | nome            | htd     | imagem   | thumb    |
   1 | Gallery Nature  | nature  | face.jpg | thumb.jg | 
   2 | Gallery Peoples | people  | face.jpg | thumb.jg | 
   3 | Gallery Animals | animal  | face.jpg | thumb.jg | 

这是第二个表的结构(称为galerias)

  id | titulo       | foto    | thumb           | data    | categoria |
  1  | Sun          | sun.jpg | sun-thumb.jpg   | now     | nature    |
  2  | Moon         | mon.jpg | mon-thumb.jpg   | now     | nature    |
  3  | Tree         | tree.jpg| tre-thumb.jpg   | now     | nature    |
  4  | Woman        | wman.jpg| wman-thumb.jpg  | now     | people    |
  5  | Girl         | gran.jpg| gr-thumb.jpg    | now     | people    |
  6  | leaf         | lea.jpg | leaf-thumb.jpg  | now     | nature    |
  7  | dog          | dog.jpg | dog-thumb.jpg   | now     | animal    |

在这种情况下，我需要显示如下结果:

   Gallery name --> Gallery Nature (4) <-- Number of  occurrences 
   Gallery name --> Gallery People (2) <-- Number of  occurrences 
   Gallery name --> Gallery Animal (1) <-- Number of  occurrences 

画廊的名称必须通过“canais”表获取，出现次数必须来自基于“categoria”列的“galerias”表。谁能帮我解决一下吗？

Answer 1

以下查询将对您有所帮助

SELECT a.nome AS gallery  ,COUNT(b.titulo)  AS photos 
FROM canais a INNER JOIN galerias b ON a.htd    = b.categoria 
GROUP BY a.nome