CODEWITHSUNDEEP
phpmysql
Alpesh Trivedi
Alpesh Trivedi

Reputation: 979

I am stuck on one query in mysql

I am stuck on one query in mysql.

I want to fetch most recent comment from the table

records should be like this
enter image description here enter image description here enter image description here Table Structure for the table the table blog

  
blog_id int - primary (auto increment)
blog_title       -varchar
blog_desc        -varchar
blog_image       -varchar
blog_tags        -varchar
tot_comments     -int
blog_creater     -varchar
blog_create_date -datetime
blog_status      -enum ('Enable','Disable')

table structure for the table blog_comment

comment_id       -int (auto increment)  
fk_blog_id       -int  
comment          -varchar  
comment_by       -varchar  
email            -varchar  
comment_date     -datetime  
comment_status   -enum ('Enable','Disable')

And below is query written by me, but the result I am getting is wrong.

SELECT b.blog_title,b.blog_image, bc.*
FROM blog_comments  bc, blog b
WHERE bc.comment_status='Enable'
AND b.blog_status='Enable'
AND b.blog_id=bc.fk_blog_id
GROUP BY bc.fk_blog_id    
ORDER BY bc.comment_date DESC 
LIMIT 0,3

Output

enter image description here

Upvotes: 8

Views: 248

Answers (7)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Try this query

SELECT 
  bc.* 
FROM
  blog AS b 
  INNER JOIN (SELECT id , MAX(id) AS MaxID FROM blog) AS bl ON bl.id = b.id 
  LEFT JOIN blog_comment AS bc ON bc.fk_blog_id = b.id 
ORDER BY bc.comment_id DESC 
LIMIT 3

EDITS:

SELECT 
  bc.* 
FROM
  blog AS b 
  INNER JOIN (SELECT id , MAX(id) AS MaxID FROM blog GROUP BY id) AS bl ON bl.id = b.id 
  INNER JOIN (SELECT MAX(id) , fk_blog_id FROM blog_comment  GROUP BY id) AS bc ON bc.fk_blog_id = b.id 
ORDER BY bc.comment_id DESC 
LIMIT 3

This is for 3 latest blogs and latest single comments for each blog

Here using inner join will fetch the latest blog. than join comments and order them with date or id and limit them according to your requirements.

Upvotes: 0

Techmonk
Techmonk

Reputation: 1469

SELECT b.blog_title,b.blog_image, bc.*
FROM blog b
left join (
Select * from 
blog_comments bc
WHERE bc.comment_status='Enable'
GROUP BY bc.fk_blog_id    
having max(bc.comment_date) =  bc.comment_date
) bcc on b.blog_id=bcc.fk_blog_id
where
b.blog_status='Enable'
ORDER BY b.blog_create_date desc
LIMIT 0,3

Try this one

Upvotes: 1

Barmar
Barmar

Reputation: 780994

select b.blog_title, b.blog_image, bc.*
from blog b join
(select bc.*
 from bc join (select fk_blog_id, max(comment_date) latest_date
               from blog_comment
               where comment_status = 'Enable'
               group by fk_blog_id) latest
 on bc.fk_blog_id = latest.fk_blog_id and bc.comment_date = latest_date) c
on b.blog_id = c.fk_blog_id
where b.blog_status = 'Enable'
order by c.comment_date desc
limit 0, 3

The c subquery finds the row with the latest comment for each blog, using the technique in the linked question. This is then joined with the blog table to get the appropriate blog data.

Upvotes: 0

Ripa Saha
Ripa Saha

Reputation: 2540

for this the easy solution will be execute 2 query for your result . first query get blog post result 

$db_blog="select blog_id,blog_title from blog where blog_ststus='Enable'";
$que=mysql_query($db_blog); 
while($row=mysql_fetch_object($que))
{
    echo $row->blog_title;
    $db_comment="select comment from blog_comments where fk_blog_id=".$row->blog_id." and comment_status='Enable' order by comment_date desc";
    $quec=mysql_query($db_comment);
    while($comment=mysql_fetch_object($quec))
    {
         echo $comment->comment;
    }
}

Upvotes: 1

devWaleed
devWaleed

Reputation: 475

Try this:

SELECT * FROM blog_comments  bc, blog b
WHERE `bc.comment_status`='Enable'
AND `b.blog_status`='Enable'
AND `b.blog_id`=bc.fk_blog_id
ORDER BY `bc.comment_date` DESC LIMIT 1;

Try a simpler one:

SELECT * FROM `blog_comment` WHERE 'blog_status'='Enable' AND 'blog_id'='$blogidherefromtitle' ORDER BY 'comment_date' DESC LIMIT1

Upvotes: 1

Techie
Techie

Reputation: 45124

SELECT b.blog_title,b.blog_image, bc.*
FROM blog_comments  bc JOIN blog b ON bc.fk_blog_id = b.blog_id
WHERE bc.comment_status='Enable'
AND b.blog_status='Enable'
GROUP BY bc.fk_blog_id    
ORDER BY bc.comment_date DESC 
LIMIT 0,3

Upvotes: 0

keks
keks

Reputation: 1052

try

SELECT b.blog_title,b.blog_image, bc.*
FROM blog_comments AS bc, blog AS b
WHERE bc.comment_status='Enable'
AND b.blog_status='Enable'
AND b.blog_id=bc.fk_blog_id
GROUP BY bc.fk_blog_id    
ORDER BY bc.comment_date DESC 
LIMIT 0,3;

(I'm not 100% sure)

Upvotes: 0

Related Questions