I am stuck on one query in MySQL

Question

I am stuck on one query in MySQL.

I want to get the most recent comment from the table

• the comment should be the last blog comment
• blogs should be the last 3 blogs.
• display comments and blog only if their status is enabled

entries should be like this
Table Structure for Table Blog Table

---

  
 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 blog_comment table

---

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

The following is a query written by me, but the result that I get is incorrect.

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 

Exit

---

Answer 1

for this a simple solution will do 2 queries for your result. first request to get a blog post

 $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; } } 

Answer 2

 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

Answer 3

Try the following:

 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 

Answer 4

to 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)

Answer 5

 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 

Answer 6

Try this request

 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 the last 3 blogs and the latest comments for each blog.

Here, using an internal connection, you will get the latest blog. than join the comments and order them with a date or identifier and limit them according to your requirements.

Answer 7

 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 

Subquery c finds the line with the last comment for each blog using the technique in the related question. Then it connects to the blog table to retrieve the relevant blog data.