[SOLVED] JOIN query

[ninedoors]

I am trying to get data form three different tables at once.  I thing I need to use a JOIN here but I don't quite know how to use them properly it seems.  I googled it and came up with a few examples but none of them are working.

 

Here is my table structure:

 

-- --------------------------------------------------------

--
//Table structure for table `genres`
--

CREATE TABLE IF NOT EXISTS `genres` (
  `id` int(11) NOT NULL,
  `action` tinyint(4) NOT NULL default '0',
  `adventure` tinyint(4) NOT NULL default '0',
  `comedy` tinyint(4) NOT NULL default '0',
  `documentary` tinyint(4) NOT NULL default '0',
  `drama` tinyint(4) NOT NULL default '0',
  `family` tinyint(4) NOT NULL default '0',
  `horror` tinyint(4) NOT NULL default '0',
  `romance` tinyint(4) NOT NULL default '0',
  `sport` tinyint(4) NOT NULL default '0',
  `scifi` tinyint(4) NOT NULL default '0',
  `tv` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `movies`
--

CREATE TABLE IF NOT EXISTS `movies` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(200) NOT NULL,
  `location` varchar(20) NOT NULL,
  `type` varchar(20) NOT NULL,
  `thumb_path` varchar(250) NOT NULL,
  `imdb_link` varchar(300) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

-- --------------------------------------------------------

--
-- Table structure for table `roles`
--

CREATE TABLE IF NOT EXISTS `roles` (
  `id` int(11) NOT NULL,
  `actor1` varchar(60) NOT NULL default 'none',
  `actor2` varchar(60) NOT NULL default 'none',
  `actor3` varchar(60) NOT NULL default 'none',
  `actor4` varchar(60) NOT NULL default 'none',
  `actor5` varchar(60) NOT NULL default 'none',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

I want to grab each record where movies.id = roles.id = genres.id

 

I have this JOIN but it isn't working:

 

<?php
$query = "SELECT movies.id, movies.name, movies.location, movies.type, movies.thumb_path, movies.imdb_link, roles.actor1, roles.actor2, roles.actor3, roles.actor4, roles.actor5, ".
		"genres.drama, genres.documentary, genres.comedy, genres.action, genres.adventure, genres.family, genres.horror, genres.romance, genres.sport, genres.scifi, genres.tv ".
		"FROM (movies LEFT JOIN roles ON movies.id = roles.id) LEFT JOIN genres ON movies.id = genres.id LIMIT $start, $limit";
?>

 

Am I even close?  Thanks for the help.

 

Nick

[fenway]

Solution?

[ninedoors]

Yes, the query was fine.  I just didn't call mysql_query.  Stupid me.  I am actually wondering how I would go about adding a WHERE clause to this query.  Any thoughts?

[fenway]

Right before LIMIT?