Mysql Query不会回显所有结果！ PHP

问题描述：

My code goes like this,

$sql = "SELECT Month(time) as Month, Year(time) as Year,
title, COUNT(*) AS total FROM posts GROUP BY Year, Month ORDER BY time DESC";

$stmt = $conn->query($sql);

if ($stmt->num_rows > 0) {
while($row = $stmt->fetch_array()){
  echo "<div class=title>" . $row["title"]. "</div>";
}
}

it is supposed to output 4 titles,

Bellavisa
Mist Neting
Turkey is cool!
Cock of the Rock

but it only outputs

Bellavisa

Turkey is cool!

Cock of the Rock

Note that bellavisa and mist neting are in the same year and month, (setting up an archive list)

EDIT

Here is some of the table data

title "bellavisa" content "yadadada" time "timestamp ..." Author "author"   

title "mist nesting" content "yadadada" time "timestamp ..." Author "author"

我的代码是这样的， p>

  $ sql =“ 选择月份（时间）作为月份，年份（时间）作为年份，
title，COUNT（*）AS总计FROM帖子GROUP BY年份，月份ORDER BY时间DESC“; 
 
 $ stmt = $ conn-＆gt;查询 （$ sql）; 
 
if（$ stmt-＆gt; num_rows＆gt; 0）{
while（$ row = $ stmt-＆gt; fetch_array（））{
 echo“＆lt; div class = title＆gt;”  。  $行[“称号”。  “＆lt; / div＆gt;”; 
} 
} 
  code>  pre> 
 
 它应该输出4个标题， p> 
 
 
   Bellavisa 
Mist Neting 
Turkey很酷！
摇滚摇滚 pre> 
 
 但它只输出 p> 
 
 
  Bellavisa 
 
Turkey很酷！
 
Cock of the Rock 
  code>  pre> 
 
 请注意，bellavisa和mist neting是同一年和月，（设置 存档列表） p> 
 
 
编辑 p> 
 
 
以下是一些表格数据 p> 
 
 
  title  “bellavisa”内容“yadadada”时间“时间戳...”作者“作者”
 
title“雾嵌套”内容“yadadada”时间“时间戳...”作者“作者”
  code>  pre  > 
  div>

答

Well, title is per post, so to get all the titles you should use no GROUP BY, while COUNT(*) is per month, so to get counts you need to GROUP BY the way you do, so in a simple SELECT you can either select one or another, but not both.

To select both, you need to use a subquery, something along the lines of

$sql = "SELECT Month(time) as Month, Year(time) as Year, title, (SELECT COUNT(*) FROM posts WHERE Month(time) = Month AND Year(time) = Year) AS total FROM posts ORDER BY time DESC;";

Effectively, the query selects all the posts, and for each post computes a count. It is not the most efficient way to do that, you can rewrite it using a join, if every post has a unique ID. But for a table with reasonable size this query will work just fine.

Mysql Query不会回显所有结果！ PHP

相关推荐