1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Need help with MYSQL JOIN query

Discussion in 'MySQL' started by Dakuipje, Mar 4, 2010.

  1. #1
    Ok I need some help. I never really used JOIN in my programming but nog im forced to do so or else I would get a lot of double information.

    I have 2 tables:

    Genre:
    id
    genre
    Movieid
    
    Movies
    id  (sames as movieid in the genre table)
    lot of inforows
    Code (markup):
    I need a query that selects info out of the Movies table but I need to sort it by genre (which is in the genre table).
    I tried this:

    $query = "SELECT *
    				  FROM movies
    				  JOIN genres
    				  ON genres.movieid=movies.id
    				  WHERE genres.genre='$this->search'";
    Code (markup):
    But it turns out empty. What am I doing wrong and what should I do ????
    SEMrush
    Thnx in advance !
     
    Dakuipje, Mar 4, 2010 IP
    SEMrush
  2. hireme

    hireme Member

    Messages:
    58
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    45
    #2
    I think you need to rewrite your php code to something like this
    
    $query = "SELECT *
    				  FROM movies
    				  JOIN genres
    				  ON genres.movieid=movies.id
    				  WHERE genres.genre='".$this->search."'";
    
    Code (markup):
     
    hireme, Mar 4, 2010 IP
  3. Dakuipje

    Dakuipje Peon

    Messages:
    931
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Nope that didnt work ....
     
    Dakuipje, Mar 7, 2010 IP
  4. killaklown

    killaklown Well-Known Member

    Messages:
    2,666
    Likes Received:
    87
    Best Answers:
    0
    Trophy Points:
    165
    #4
    
    $query = "SELECT *
    				  FROM movies
    				  LEFT JOIN genres
    				  ON movies.id = genres.movieid
    				  WHERE genres.genre='$this->search'";
    
    Code (markup):
    if it isnt working, what is the error you are getting?


    EDIT: is it genres or genre? your sql had genres but you say your table name is genre
     
    killaklown, Mar 7, 2010 IP
    Dakuipje likes this.
  5. Dakuipje

    Dakuipje Peon

    Messages:
    931
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #5
    I spoke to soon it doesnt work. For a second I thought it did but a refresh learned me that I was viewing a cache version.
    Its not in the word genres, I checked the databases match.

    Please help as im lost here. First time I use JOIN and it goes horribly wrong, lol

    EDIT: I got it working, thnx for the help everyone
     
    Last edited: Mar 10, 2010
    Dakuipje, Mar 10, 2010 IP