Page 1 of 2

Query Problem

Posted: Wed May 26, 2010 2:11 pm
by tito85
Hi,

I have designed the following ERD;
New ERD.jpg
New ERD.jpg (29.47 KiB) Viewed 889 times
I tried to build a query but I had no success and I would like some help please.

What I need to output from this query is:

1 - The Director1 and Director2 from Movies Table

2 - The GenreTypes of the particular selected movie - here is where I am lost

3 - The Rating from the Movies Table

4 - The MovieReleaseDate from the Movies Table

5 - The DVDReleaseDate from the Movies Table

Any help would be very appreciated!

Re: Query Problem

Posted: Wed May 26, 2010 3:28 pm
by Eran
I have designed the following ERD ... I tried to build a query
Somewhere in the middle you probably built the tables, I assume? would be nice to see the structure of those and the query you attempted

Re: Query Problem

Posted: Wed May 26, 2010 3:39 pm
by tito85
Hi,

Yes of course I have built the tables in MySQL using PHPMyAdmin.

I am trying the following but still no success. I don't know if I am on the right track :( I am new into this...

SELECT GenreID.*, Genre.*, Movie.*
FROM genres
INNER JOIN Genres
ON genres.genreID = genretypes.genreID
INNER JOIN movies
ON genres.MovieID = movies.MovieID

Any Help please?

Re: Query Problem

Posted: Wed May 26, 2010 3:43 pm
by dimxasnewfrozen
Well just looking at your query and looking at the diagram, it appears you don't have a table called: GenreID. I'm assuming GenreID would be replaced with GenreTypes

Re: Query Problem

Posted: Wed May 26, 2010 3:48 pm
by Eran
Several issues here -
SELECT GenreID.*, Genre.*, Movie.*
GenreID is not a table as far as I can tell from your ERD. You can't select column from it. Probably should be GenreTypes.* (by the way - why the camelcasing for table names?)
FROM genres
INNER JOIN Genres
ON genres.genreID = genretypes.genreID
You are goining the same table twice. I think you mean joining the GenreTypes table instead

Code: Select all

SELECT GenreTypes.*, Genre.*, Movie.*
FROM Genres
INNER JOIN GenreTypes ON Genres.GenreTypeID = GenreTypes.GenreTypeID
INNER JOIN movies ON Genres.MovieID = Movies.MovieID
This should work, though I think you'd better start your query from the Movies table if you want to fetch movies

Code: Select all

SELECT GenreTypes.*, Genre.*, Movie.*
FROM Movies
INNER JOIN Genres ON Genres.MovieID = Movies.MovieID
INNER JOIN GenreTypes ON Genres.GenreTypeID = GenreTypes.GenreTypeID
Also, please wrap your code in [syntax] tags in the future.

Re: Query Problem

Posted: Wed May 26, 2010 3:56 pm
by tito85
Yep,

I came up with the following:

SELECT GenreTypes.*, Genres.*, Movies.*
FROM Movies
INNER JOIN Genres ON Genres.MovieID = Movies.MovieID
INNER JOIN GenreTypes ON Genres.GenreTypeID = GenreTypes.GenreTypeID

However only movies that have a genre are coming up and I also need to add a "where" claus so that the query will output only info for a particular movie using $_GET['id']

Re: Query Problem

Posted: Wed May 26, 2010 7:16 pm
by phu
Whether your query finds rows that don't have joined matches depends on the kind of join you use. Researching the difference between left/right/inner/outer/endothermic/exothermic (there are just too many to take seriously) joins will tell you why you're not getting what you want.

Re: Query Problem

Posted: Wed May 26, 2010 7:23 pm
by mikosiko
tito85 wrote:Yep,

I came up with the following:

SELECT GenreTypes.*, Genres.*, Movies.*
FROM Movies
INNER JOIN Genres ON Genres.MovieID = Movies.MovieID
INNER JOIN GenreTypes ON Genres.GenreTypeID = GenreTypes.GenreTypeID

However only movies that have a genre are coming up and I also need to add a "where" claus so that the query will output only info for a particular movie using $_GET['id']
first.... Pytrin wrote for you "Also, please wrap your code in [syntax] tags in the future." ...you didn't get that?.. follow the posting rule make more easy to read your code and try to help you... so... again... follow the posting rules.

second: I'm not a big fan of wikipedia... but in this case this article will help you to understand some SQL basics... so please read it completely... there is your answer .... http://en.wikipedia.org/wiki/Join_(SQL)

Re: Query Problem

Posted: Thu May 27, 2010 12:45 am
by tito85
I Managed to do the following:

Code: Select all

<?php
  if (isset($_GET['id'])) {
	  
	  $select = "SELECT genretypes.*, genres.*, movies.* FROM movies INNER JOIN genres ON genres.MovieID = movies.MovieID INNER JOIN genretypes ON genres.GenreTypeID = genretypes.GenreTypeID WHERE movies.MovieID = '" . $_GET['id'] . "'";
	  
      $result = mysql_query($select);
      if (mysql_num_rows($result) > 0) {
          while ($movies = mysql_fetch_array($result)) {
              echo "<center><h3>" . stripslashes($movies['Title']) . "</h3></center>";
              echo $movies['Genre'];
 ?>
However the echo for genre is only outputting one genre of the movie.

Any help of how to echo all the genre that the movie has please?

Help....

Thanks

Re: Query Problem

Posted: Thu May 27, 2010 8:26 am
by mikosiko

Re: Query Problem

Posted: Thu May 27, 2010 8:51 am
by tito85
Hi mikosiko,

Thanks for the remider. However I am asking for help because I was not able to do it by myself.

If you can help me I would appreciate a lot.

Thanks

Re: Query Problem

Posted: Thu May 27, 2010 9:42 am
by mikosiko
in this part of your code:

Code: Select all

      if (mysql_num_rows($result) > 0) {
          while ($movies = mysql_fetch_array($result)) {
              echo "<center><h3>" . stripslashes($movies['Title']) . "</h3></center>";
              echo $movies['Genre'];
 ?>
where are you closing the {'s for the if and for the while sentences?

Re: Query Problem

Posted: Thu May 27, 2010 10:32 am
by tito85
Thanks for the reply.

You are right. I just got a snippet and left the }. However I have them in the page.

Code: Select all

<?php
  if (isset($_GET['id'])) {
         
          $select = "SELECT genretypes.*, genres.*, movies.* FROM movies INNER JOIN genres ON genres.MovieID = movies.MovieID INNER JOIN genretypes ON genres.GenreTypeID = genretypes.GenreTypeID WHERE movies.MovieID = '" . $_GET['id'] . "'";
         
      $result = mysql_query($select);
      if (mysql_num_rows($result) > 0) {
          while ($movies = mysql_fetch_array($result)) {
              echo "<center><h3>" . stripslashes($movies['Title']) . "</h3></center>";
              echo $movies['Genre'];
                                  }
                         }
 ?>

Re: Query Problem

Posted: Thu May 27, 2010 10:48 am
by mikosiko
so.. what is your problem then?.... the query is getting for each movie the associated genre name (only if the movie has an associated genre)

Re: Query Problem

Posted: Thu May 27, 2010 10:53 am
by tito85
Yes it is getting the genre. But the movie has more then one genre and hence I wish to get all the genres the movie has.

Using

Code: Select all

 echo $movies['Genre'];
is only getting one genre.