php - 将 MySQL 查询与行计数查询相结合，以使用 PHP 进行优化

Question

我有一个可用的 PHP 脚本，它通过用户表的联接来选择内容。然后我 while 循环结果。

但是，我想知道是否可以在初始查询中调用它，而不是每个循环调用另一个查询？基本上合并它们？

代码:

$content_result = mysql_query("
  SELECT content.id, content.type, content.title, content.url, users.username
  FROM content
  INNER JOIN users ON content.uploaderuid = users.id
  ORDER BY content.id DESC
  LIMIT $offset, $rowsperpage
");

while($content_row = mysql_fetch_array($content_result)){
$contentid = $content_row['id'];
$result_num_votes = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) FROM votes WHERE votedcontentid = '$contentid' AND value = '1'"));
$num_votes = $result_num_votes['COUNT(*)'];

}

Answer 1

试试这个:

SELECT COUNT(votedcontentid)as numbervotes,content.id, content.type, content.title, content.url, users.username 
FROM content 
INNER JOIN users ON content.uploaderuid = users.id 
JOIN votes ON votes.votedcontentid = content.id 
ORDER BY content.id DESC LIMIT $offset, $rowsperpage

这里将其更改为

$num_votes = $result_num_votes['numbervotes'];