Test the select that you have in phpmyadmin (that is what you use right?) that select is giving you ALL the associated genres... also check your data consistency.tito85 wrote: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.
Usingis only getting one genre.Code: Select all
echo $movies['Genre'];
Query Problem
Moderator: General Moderators
Re: Query Problem
Re: Query Problem
Yes I did test it in PHPMyAdmin
This is how I tested it:
The 3 in the where is a MovieID
The result is showing more then 1 genre.
This is how I tested it:
Code: Select all
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 = '3'The result is showing more then 1 genre.
Re: Query Problem
you have a different problem then...
- Your select is OK (you already tested)
- Your PHP code is ok... it IS getting all the elements (I did test it).
try making this change:
Edited: Or maybe
try adding an "ORDER BY movies.movieId" to your select 
- Your select is OK (you already tested)
- Your PHP code is ok... it IS getting all the elements (I did test it).
try making this change:
Code: Select all
echo $movies['Genre'] . "<br />";Re: Query Problem
Still not working 
I'm Totally Lost...
Any other ideas please?
I'm Totally Lost...
Any other ideas please?
Re: Query Problem
post your complete code, your table's structure and the data in the 3 tables for the ID that you are testing.... the code that you show is working, therefore you have problem in other area.
Re: Query Problem
Hi,
Here it is:
--
-- Table structure for table `genres`
--
CREATE TABLE IF NOT EXISTS `genres` (
`GenreID` int(8) NOT NULL AUTO_INCREMENT,
`MovieID` int(8) NOT NULL,
`GenreTypeID` int(8) NOT NULL,
PRIMARY KEY (`GenreID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `genres`
--
INSERT INTO `genres` (`GenreID`, `MovieID`, `GenreTypeID`) VALUES
(1, 3, 1),
(2, 3, 2),
(3, 7, 2),
(4, 7, 3);
-- --------------------------------------------------------
-- Table structure for table `genretypes`
--
CREATE TABLE IF NOT EXISTS `genretypes` (
`GenreTypeID` int(8) NOT NULL AUTO_INCREMENT,
`Genre` varchar(20) NOT NULL,
PRIMARY KEY (`GenreTypeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
--
-- Dumping data for table `genretypes`
--
INSERT INTO `genretypes` (`GenreTypeID`, `Genre`) VALUES
(1, 'Action'),
(2, 'Comedy'),
(3, 'Family'),
(4, 'History'),
(5, 'War'),
(6, 'Crime'),
(7, 'Adventure'),
(8, 'Thriller'),
(9, 'Romance'),
(10, 'Drama'),
(11, 'Fantasy');
-- --------------------------------------------------------
-- Table structure for table `movies`
--
CREATE TABLE IF NOT EXISTS `movies` (
`MovieID` int(8) NOT NULL AUTO_INCREMENT,
`Title` varchar(100) NOT NULL,
`Review` text NOT NULL,
`Rating` int(2) NOT NULL,
`Director1` varchar(30) NOT NULL,
`Director2` varchar(30) NOT NULL,
`DVDReleaseDate` date DEFAULT NULL,
`MovieReleaseDate` date DEFAULT NULL,
`Filename` varchar(100) NOT NULL,
PRIMARY KEY (`MovieID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
--
-- Dumping data for table `movies`
--
INSERT INTO `movies` (`MovieID`, `Title`, `Review`, `Rating`, `Director1`, `Director2`, `DVDReleaseDate`, `MovieReleaseDate`, `Filename`) VALUES
(3, 'Avatar', 'In the epic action adventure Avatar, James Cameron, the director of Titanic, takes us to a spectacular new world beyond imagination. On the distant moon Pandora, a reluctant hero embarks on a journey of redemption and discovery as he leads a heroic battle to save a civilization.\r\n\r\nThe film was first conceived by Cameron 14 years ago, when the means to realize his vision did not yet exist. Now, after four years of actual production work, Avatar delivers a fully immersible cinematic experience of a new kind, where the revolutionary technology invented to make the film, disappears into the emotion of the characters and the sweep of the story.', 8, 'James Cameron', '', '2010-04-22', '2009-12-18', 'Avatar.jpg'),
(7, 'Old Dogs', 'Two best friends - one unlucky-in-love divorcee and the other a fun-loving bachelor - have their lives turned upside down when they''re unexpectedly charged with the care of 7-year-old twins while on the verge of the biggest business deal of their lives. The not-so-kid-savvy bachelors stumble in their efforts to take care of the twins, leading to one debacle after another, and perhaps to a new-found understanding of what''s really important in life. ', 5, 'Walt Becker', '', '2010-03-09', '2009-11-25', 'Old Dogs.jpg'),
Here it is:
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'] . "' ORDER BY movies.MovieID";
$result = mysql_query($select);
if (mysql_num_rows($result) > 0) {
while ($movies = mysql_fetch_array($result)) {
echo "<div style=\"overflow: auto;\">";
echo "<center><h3>" . stripslashes($movies['Title']) . "</h3></center>";
echo "<br><br>";
echo "<div style=\"float: right; padding-right: 150px; height: 400px; \">";
if (strlen($movies['Filename']) > 0) {
echo '<img src="Images/Movies/' . $movies['Filename'] . '" width="246" height="365" />';
}
echo "</div>";
echo "<div style=\"float: left; padding-left: 150px; padding-top: 50px; \">";
?>
<table>
<tr height="40">
<td align="right">
<strong>Director/s: </strong>
</td>
<td>
<?php echo $movies['Director1']; ?>
</td>
<td>
<?php echo $movies['Director2']; ?>
</td>
</tr>
<tr height="40">
<td align="right">
<strong>Genre: </strong>
</td>
<td>
<?php echo $movies['Genre']; ?>
</td>
</tr>
<tr height="40">
<td align="right">
<strong>Rating: </strong>
</td>
<td>
<?php
if ($movies['Rating'] == 0)
echo "N/A";
else
echo $movies['Rating'];
?>/10
</td>
</tr>
<tr height="40">
<td align="right">
<strong>Movie Release Date: </strong>
</td>
<td>
<?php
if (date("d/m/Y", strtotime($movies['MovieReleaseDate'])) == "01/01/1970")
echo "N/A";
else
echo date("d/m/Y", strtotime($movies['MovieReleaseDate']));
?>
</td>
</tr>
<tr height="40">
<td align="right">
<strong>DVD Release Date: </strong>
</td>
<td>
<?php
if (date("d/m/Y", strtotime($movies['DVDReleaseDate'])) == "01/01/1970")
echo "N/A";
else
echo date("d/m/Y", strtotime($movies['DVDReleaseDate']));
?>
</td>
</tr>
<?php
if (isset($_SESSION['user']) && $_SESSION['user'] == true) {
?>
<tr height="40">
<?php
$select = "SELECT * FROM favorites WHERE UserID = " . $_SESSION['userinfo']['id'] . " AND MovieID = " . $_GET['id'];
$isfavorite = mysql_query($select);
if (mysql_num_rows($isfavorite) == 0) {
?>
<td colspan="2" align='center'><a href="addtofavs.php?id=<?php echo $movies['MovieID'] ?>">Add this Movie to my Favorite Movies</a></td>
<?php
} else {
?>
<td colspan="2" align='center'><a href="removefromfavs.php?id=<?php echo $movies['MovieID'] ?>">Remove this Movie from my Favorites Movies</a></td>
<?php
}
?>
</tr>
<?php
}
?>
</table>-- Table structure for table `genres`
--
CREATE TABLE IF NOT EXISTS `genres` (
`GenreID` int(8) NOT NULL AUTO_INCREMENT,
`MovieID` int(8) NOT NULL,
`GenreTypeID` int(8) NOT NULL,
PRIMARY KEY (`GenreID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
--
-- Dumping data for table `genres`
--
INSERT INTO `genres` (`GenreID`, `MovieID`, `GenreTypeID`) VALUES
(1, 3, 1),
(2, 3, 2),
(3, 7, 2),
(4, 7, 3);
-- --------------------------------------------------------
-- Table structure for table `genretypes`
--
CREATE TABLE IF NOT EXISTS `genretypes` (
`GenreTypeID` int(8) NOT NULL AUTO_INCREMENT,
`Genre` varchar(20) NOT NULL,
PRIMARY KEY (`GenreTypeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
--
-- Dumping data for table `genretypes`
--
INSERT INTO `genretypes` (`GenreTypeID`, `Genre`) VALUES
(1, 'Action'),
(2, 'Comedy'),
(3, 'Family'),
(4, 'History'),
(5, 'War'),
(6, 'Crime'),
(7, 'Adventure'),
(8, 'Thriller'),
(9, 'Romance'),
(10, 'Drama'),
(11, 'Fantasy');
-- --------------------------------------------------------
-- Table structure for table `movies`
--
CREATE TABLE IF NOT EXISTS `movies` (
`MovieID` int(8) NOT NULL AUTO_INCREMENT,
`Title` varchar(100) NOT NULL,
`Review` text NOT NULL,
`Rating` int(2) NOT NULL,
`Director1` varchar(30) NOT NULL,
`Director2` varchar(30) NOT NULL,
`DVDReleaseDate` date DEFAULT NULL,
`MovieReleaseDate` date DEFAULT NULL,
`Filename` varchar(100) NOT NULL,
PRIMARY KEY (`MovieID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
--
-- Dumping data for table `movies`
--
INSERT INTO `movies` (`MovieID`, `Title`, `Review`, `Rating`, `Director1`, `Director2`, `DVDReleaseDate`, `MovieReleaseDate`, `Filename`) VALUES
(3, 'Avatar', 'In the epic action adventure Avatar, James Cameron, the director of Titanic, takes us to a spectacular new world beyond imagination. On the distant moon Pandora, a reluctant hero embarks on a journey of redemption and discovery as he leads a heroic battle to save a civilization.\r\n\r\nThe film was first conceived by Cameron 14 years ago, when the means to realize his vision did not yet exist. Now, after four years of actual production work, Avatar delivers a fully immersible cinematic experience of a new kind, where the revolutionary technology invented to make the film, disappears into the emotion of the characters and the sweep of the story.', 8, 'James Cameron', '', '2010-04-22', '2009-12-18', 'Avatar.jpg'),
(7, 'Old Dogs', 'Two best friends - one unlucky-in-love divorcee and the other a fun-loving bachelor - have their lives turned upside down when they''re unexpectedly charged with the care of 7-year-old twins while on the verge of the biggest business deal of their lives. The not-so-kid-savvy bachelors stumble in their efforts to take care of the twins, leading to one debacle after another, and perhaps to a new-found understanding of what''s really important in life. ', 5, 'Walt Becker', '', '2010-03-09', '2009-11-25', 'Old Dogs.jpg'),
Re: Query Problem
no time to replicate you whole scenario... but my last suggestion for you are :
1) Check this lines.... you have a /10 there that shouldn't be there
2) Take just this code and create a new file for testing it will show you that the query is working, therefore you can investigate the rest of the code for problems:
and next time post your real code the first time, otherwise you waste your and our time
happy codding
EDITED: and last thing... if the code that you just posted is your COMPLETE code the you have the 2 first "IF's" and your "WHILE" {'s unclosed.... as I did suspect from the beginning.
1) Check this lines.... you have a /10 there that shouldn't be there
Code: Select all
<td>
<?php
if ($movies['Rating'] == 0)
echo "N/A";
else
echo $movies['Rating'];
?>/10
</td>
</tr>
<tr height="40">
<td align="right">
<strong>Movie Release Date: </strong>Code: Select all
<?php
/* Put your connect and select database here */
mysql_connect('localhost', 'your-username', 'your-password') or die(mysql_error());
mysql_select_db('your-database') or die(mysql_error());
$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 = '3' ORDER BY movies.MovieID";
$result = mysql_query($select) or die(mysql_error());
if (mysql_num_rows($result) > 0) {
while ($movies = mysql_fetch_array($result)) {
echo stripslashes($movies['Title']) . ' - ' . $movies['genre'];
}
}
mysql_close();
?>happy codding
EDITED: and last thing... if the code that you just posted is your COMPLETE code the you have the 2 first "IF's" and your "WHILE" {'s unclosed.... as I did suspect from the beginning.
Re: Query Problem
I have counted all the { and } and I have the same amount.
Thanks for the test. Still cannot figure it out!
Thanks for the test. Still cannot figure it out!
Re: Query Problem
.... just because I have sometime available today....
try this... replace the last 4 lines of your code for this
... and even if this is going to work.... the results are not going to be the ones that I suspect that you are waiting for
.... but that is a different tale 
try this... replace the last 4 lines of your code for this
Code: Select all
<?php
}
}
}
?>
</table>Re: Query Problem
Thanks for your time.
Still not working. It is giving me a parse error becauce now i have an extra }
Still not working. It is giving me a parse error becauce now i have an extra }
Re: Query Problem
... I will bet you whatever you want that you HAVE MORE CODE THAN THE ONE THAT YOU POSTED AS VALID!! or you screw up counting
in the code that you posted, you have 7 open { and 4 closing } ... how the hell can you say that you have the same amount?...
man...
... sorry... but I'm closing this "help desk" ticket 
in the code that you posted, you have 7 open { and 4 closing } ... how the hell can you say that you have the same amount?...
man...