Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    May 2012
    Posts
    89
    Thanks
    51
    Thanked 0 Times in 0 Posts

    inner join query question

    Hi

    I've done inner joins before, and I figured this one would be doing the same thing as the others, but it isn't working, and I was wondering if someone could see the error?

    I have 3 tables - a DJs table, a favs table, and a members table (favs table stores members favorite djs).

    The favs table contains 3 columns - fav_id, member_id and the dj_id.

    The DJs table contains 2 - dj_id, and name.

    I'm trying to retrieve the name of each DJ that the member likes.

    Code:
    $sql  = "SELECT DJs.name
    	FROM favs
    	INNER JOIN DJs
    	ON favs.dj_id = DJs.dj_id
    	WHERE favs.member_id = '{$my_id}'	
    	ORDER BY DJs.name";
    Should that not return a result set if $my_id appears in favs, and there are matches between favs.dj_id and DJs.dj_id?

    Thanks for taking a look.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,613
    Thanks
    80
    Thanked 4,634 Times in 4,596 Posts
    Looks right to me. So maybe the problem is in your PHP code?

    Start by debugging:
    Code:
    $sql = "SELECT DJs.name FROM favs INNER JOIN DJs 
               ON favs.dj_id = DJs.dj_id WHERE favs.member_id = '{$my_id}'
               ORDER BY DJs.name";
    
    echo "<hr/>DEBUG SQL: " . $sql . "<hr/>";
    
    ...
    Look at the debug: Does it have the right member_id value?

    Oh, and if member_id is a NUMERIC field, then kill the apostrophes around $my_id.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    shaunthomson (11-09-2012)

  • #3
    New Coder
    Join Date
    May 2012
    Posts
    89
    Thanks
    51
    Thanked 0 Times in 0 Posts
    Wanna know what I did? I forgot to encase the query in mysql_query() !!! I've done that a few times now, and have thoroughly learned my lesson!!!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •