PHP MYSQL 从超过 2 个表中检索数据

Question

这是从数据库中获取分页文章列表以及每篇文章的“红心”数量的查询:

$MySQL = '
SELECT
    SQL_CALC_FOUND_ROWS
a.id, a.address, a.autor, a.date_created, a.time_created, a.date_edited,     a.time_edited, a.title, a.content, a.category, a.reads, a.article_type,  a.article_img, a.article_video, COUNT(*) as \'hcount\', ah.ip
FROM articles AS a
LEFT JOIN article_hearts AS ah ON ah.article_id = a.id
GROUP BY a.id, a.address, a.autor, a.date_created, a.time_created, a.title, a.content, a.category, a.reads
ORDER BY a.date_created DESC, a.time_created DESC
LIMIT
    ' . (($pagination->get_page() - 1) * $records_per_page) . ', ' . $records_per_page . '
';

表:文章

id | address | autor | date_created | time_created | date_edited | time_edited | title content

表:article_hearts

id | ip | article_id

结果是所有文章的输出以及每篇文章的红心数 article_hearts.article_id = articles.id

问题是当我尝试从其他表中获取数据时。

我想得到评论的数量

表:article_comments

id | id_post | name | etc....

它应该显示每篇文章有多少条评论......

我使用这个脚本(只是其中的一部分...)

// fetch the total number of records in the table
$rows = mysql_fetch_assoc(mysql_query('SELECT FOUND_ROWS() AS rows'));

<?php while ($row = mysql_fetch_assoc($result)):?>

    <?php
    $id = $row['id'];
    $address = $row['address'];
    $autor =  $row['autor'];
    $title =  $row['title'];
     //etc.....

     Echo "......all variables in html....";

       ?>

    php endwhile?>

我试过 LEFT JOIN 但它不起作用...

我也试过这个:

 $MySQL = '
 SELECT
    SQL_CALC_FOUND_ROWS
  a.id, a.address, a.autor, a.date_created, a.time_created, a.date_edited,  a.time_edited, a.title, a.content, a.category, a.reads, a.article_type,   a.article_img, a.article_video, COUNT(*) as \'hcount\',
   ah.ip, (SELECT * FROM article_comments WHERE id_post=a.id) AS q1
  FROM articles AS a
  LEFT JOIN article_hearts AS ah ON ah.article_id = a.id
 GROUP BY a.id, a.address, a.autor, a.date_created, a.time_created, a.title,     a.content, a.category, a.reads
  ORDER BY a.date_created DESC, a.time_created DESC
  LIMIT
    ' . (($pagination->get_page() - 1) * $records_per_page) . ', ' .   $records_per_page . '
   ';

有什么想法吗？

还有我的好奇心……1. 如何检索每篇文章的评论数，其中 ac.name = "Guest"

1. 是否可以从名为 article_visits 的第 4 个表中获取计数行表:article_visits

   编号 | post_id |知识产权 |等....

2. 而且，就像评论中的情况一样...要在女巫中获取一个值，它会显示有多少是由 ip = "YYY..."生成的，或者只是检查是否在中找到 ip = "YYY"表格不管出现多少次

如果你能给我一些有值(value)的建议，这对我理解 mysql 查询将是一大步。

Answer 1

我认为除了 join 你还可以做 sub select for count

Select *, 
       (Select count(1) from article_comment as ac where ac.article_id = a.id and ac.name = 'Guest'),
       (Select count(1) from article_visits as av where av.article_id = a.id  and up = 'xxxx')
From  article as a 
Group by a.id