Query Problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Query Problem

Post by tito85 »

Hi,

I have designed the following ERD;
New ERD.jpg
New ERD.jpg (29.47 KiB) Viewed 890 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!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Query Problem

Post 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
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Query Problem

Post 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?
dimxasnewfrozen
Forum Commoner
Posts: 84
Joined: Fri Oct 30, 2009 1:21 pm

Re: Query Problem

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Query Problem

Post 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.
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Query Problem

Post 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']
phu
Forum Commoner
Posts: 61
Joined: Tue Mar 30, 2010 6:18 pm

Re: Query Problem

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Query Problem

Post 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)
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Query Problem

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Query Problem

Post by mikosiko »

tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Query Problem

Post 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
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Query Problem

Post 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?
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Query Problem

Post 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'];
                                  }
                         }
 ?>
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Query Problem

Post 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)
tito85
Forum Contributor
Posts: 104
Joined: Sat Mar 13, 2010 11:26 am

Re: Query Problem

Post 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.
Post Reply