doushi1912
2015-12-06 11:35
浏览 65
已采纳

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

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.

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • douqiang3768 2015-12-06 11:59
    已采纳

    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
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dqmhgz5848 2015-12-06 11:57

    You can put extra JOIN clauses to your query:

    SELECT A.id, C.id, H.id
    FROM acrticles A
    LEFT JOIN comments C ON (A.id=C.articleid_id)
    LEFT JOIN hearts H ON (A.id=H.article_id);
    

    Aggregation functions work on there too:

    SELECT A.id, COUNT(C.id), H.id
    FROM acrticles A
    LEFT JOIN comments C ON (A.id=C.articleid_id)
    LEFT JOIN hearts H ON (A.id=H.article_id)
    GROUP BY (A.id)
    
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题