我有两个独立的查询:
查询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)
like this 图库名称(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"表。谁能帮我解决这个问题?
下面的查询将对您有所帮助
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