PHP MYSQL从2个以上的表中检索数据

Question

This is the query that gets a list of paginated articles from the database and the number of "hearts" for each of them:

$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 . '
';

Table: articles

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

Table: article_hearts

id | ip | article_id

The result is an output with all articles and with the number of hearts each of them have for article_hearts.article_id = articles.id

The problem is when i try to get data from other tables.

I want to get number of comments from

Table: article_comments

id | id_post | name | etc....

It should display for each article how many comments does it have....

I use this script (just parts of it...)

// 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?>

I tried LEFT JOIN but it does not work...

I also tried this:

 $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 . '
   ';

Any ideas?

And my curiosites... 1. How do i retrieve the numer of comments for each article where ac.name = "Guest"

1. Is it possible to get count rows from a 4th table named article_visits Table: article_visits

   id | post_id | ip | etc....

2. And, like the comments case...to get a value in witch is shows how many are made by ip = "YYY...", or just to check if ip = "YYY" is found in table no matter how many times it occurs

This would be a big step for my understanding of mysql queryes if you help me with some valuable advice.

Answer 1

I think instead of join you can just do 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