Query Problem
Moderator: General Moderators
Query Problem
Hi,
I have designed the following ERD;
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!
I have designed the following ERD;
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
Somewhere in the middle you probably built the tables, I assume? would be nice to see the structure of those and the query you attemptedI have designed the following ERD ... I tried to build a query
Re: Query Problem
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?
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
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
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
Several issues here -
This should work, though I think you'd better start your query from the Movies table if you want to fetch movies
Also, please wrap your code in [syntax] tags in the future.
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?)SELECT GenreID.*, Genre.*, Movie.*
You are goining the same table twice. I think you mean joining the GenreTypes table insteadFROM genres
INNER JOIN Genres
ON genres.genreID = genretypes.genreID
Code: Select all
SELECT GenreTypes.*, Genre.*, Movie.*
FROM Genres
INNER JOIN GenreTypes ON Genres.GenreTypeID = GenreTypes.GenreTypeID
INNER JOIN movies ON Genres.MovieID = Movies.MovieIDCode: Select all
SELECT GenreTypes.*, Genre.*, Movie.*
FROM Movies
INNER JOIN Genres ON Genres.MovieID = Movies.MovieID
INNER JOIN GenreTypes ON Genres.GenreTypeID = GenreTypes.GenreTypeIDRe: Query Problem
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']
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
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
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.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']
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
I Managed to do the following:
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
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'];
?>
Any help of how to echo all the genre that the movie has please?
Help....
Thanks
Re: Query Problem
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
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
in this part of your code:
where are you closing the {'s for the if and for the while sentences?
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'];
?>Re: Query Problem
Thanks for the reply.
You are right. I just got a snippet and left the }. However I have them in the page.
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
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
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 is only getting one genre.
Using
Code: Select all
echo $movies['Genre'];