MYSQL join query not working

Question

I have 2 mysql tables named

blog
comment

What i am trying to do to retrieve following columns user_name comment_desc from the comment table using blog_id.

Both the tables have blog_id in common.

here below are the screenshots of both sql tables

Comment Table:

Blog Table:

I tried the query this way.

SELECT blog.blog_id, comment.user_name, comment.comment_desc 
    FROM 
        blog (b) 
    INNER JOIN 
        comment (c)
        ON b.blog_id = c.blog_id

i dont have access to upload the image thats why i uploaded to photobucket.

The PHP Code....

<?php

                        $comments_set = blog_comments();
                        var_dump($comments_set);
                        while($comments_all = mysql_fetch_assoc($comments_set)){
                            $name = $comments_all['user_name'];
                            $desc = $comments_all['comment_desc'];
                            echo                    
                            "<ol class=\"commentlist clearfix\">

                                <li class=\"comment even thread-even depth-1\" id=\"li-comment-1\">

                                    <div id=\"comment-1\" class=\"comment-wrap clearfix\">

                                        <div class=\"comment-meta\">

                                            <div class=\"comment-author vcard\">

                                                <span class=\"comment-avatar clearfix\">
                                                <img alt='' src='http://0.gravatar.com/avatar/ad516503a11cd5ca435acc9bb6523536?s=60' class='avatar avatar-60 photo avatar-default' height='60' width='60' /></span>

                                            </div>

                                        </div>

                                        <div class=\"comment-content clearfix\">

                                            <div class=\"comment-author\">$name<span><a href=\"#\" title=\"Permalink to this comment\">January 24, 2013 at 10:46 am</a> &middot; <a class='comment-reply-link' href=\"#\">Reply</a></span></div>

                                            <p>$desc</p>


                                        </div>

                                    </div>


                                </li>



                            </ol>
                            ";
                        }
                            ?>

Actually you can post the output of console output (e.g. DESC blog) here. — Raptor
– Raptor, Commented May 8, 2013 at 11:20
i am getting this error......Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\projects\New smartbakhtar\blog-single.php on line 187 — Ahad Murtaza
– Ahad Murtaza, Commented May 8, 2013 at 11:23
@AhadMurtaza - problem appears to be somewhere in blog_comments() function, so please post its code also — Viktor S.
– Viktor S., Commented May 8, 2013 at 11:37

Viktor S. · Accepted Answer · 2013-05-08 11:26:20Z

1

You need user_name and desc from comment table with blog_ig

Why you need to join these 2 tables then ? you could retrieve information from comment table by blog id

select * from comments where blog_id='blog_id'

edited May 8, 2013 at 11:26

Viktor S.

12.8k1 gold badge30 silver badges56 bronze badges

answered May 8, 2013 at 11:24

user2362083

337 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ahad Murtaza Over a year ago

The both have two fields common i.e blog_id.so that the a specific comment is for some specific blog.

user2362083 Over a year ago

Ahad Bhai, in either way you have to mention blog_id, or the lef join can be the good option here, hav u tried it ?

Akash KC · Accepted Answer · 2013-05-08 11:43:52Z

0

You are using alias so at the time of SELECT statement, specify it through alias name. You need to remove parenthesis around alias name...

SELECT b.blog_id, c.user_name, c.comment_desc 
    FROM 
        blog b 
    INNER JOIN 
        comment c
        ON b.blog_id = c.blog_id

edited May 8, 2013 at 11:43

answered May 8, 2013 at 11:29

Akash KC

16.4k6 gold badges41 silver badges59 bronze badges

Comments

cha · Accepted Answer · 2013-05-08 11:31:49Z

0

I think you have incorrectly define the blog_id columns. Please check columns' datatype and sizes. It will fail when joining tables. If you can paste error it will be more helpful

Thanks.

answered May 8, 2013 at 11:31

cha

7402 gold badges14 silver badges29 bronze badges

8 Comments

Ahad Murtaza Over a year ago

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\projects\New smartbakhtar\blog-single.php on line 187

Bojan Kovacevic Over a year ago

first check if query is working on it own on mysql server. i think you have sql error which will then be printed.

cha Over a year ago

This php error occurs when null object returns. Error in you sql code. It doesn't return results. Did you run this sql without using the php code? Try this in phpmyadmin. Then you can see mysql errors.

cha Over a year ago

Run sql query in phpmyadmin, then you can see errors. Did you run sql?

Ahad Murtaza Over a year ago

i checked it using var_dump() whether its returning any values but it returned "false."

|

Kinjal Bagaria · Accepted Answer · 2013-05-08 11:32:50Z

0

I think you should go for Left Join because may be you want to have blogs to be displayed whether they have comments or not.

SELECT b.blog_id, c.user_name, c.comment_desc 
    FROM 
        blog as b
    LEFT JOIN 
        comment as c
        ON b.blog_id = c.blog_id

edited May 8, 2013 at 11:32

answered May 8, 2013 at 11:24

Kinjal Bagaria

462 bronze badges

1 Comment

Kinjal Bagaria Over a year ago

exactly what you are getting in result set?

Collectives™ on Stack Overflow

MYSQL join query not working

4 Answers 4

2 Comments

Comments

8 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related