I though i had my query right but for some reason it was just returning the last result...
movies Table
CREATE TABLE IF NOT EXISTS `movies` (
`id` varchar(15) NOT NULL,
`title` text NOT NULL,
`plot` longtext NOT NULL,
`director` varchar(100) NOT NULL,
`release` date NOT NULL,
`mpaa` text NOT NULL,
`runtime` int(10) NOT NULL,
`company` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `movies` (`id`, `title`, `plot`, `director`, `release`, `mpaa`, `runtime`, `company`) VALUES
('tt0120903', 'X-Men', 'In a world where both Mutants and Humans fear each other, Marie D''Ancanto, better known as Rogue, runs away from home and hitches a ride with another mutant, known as Logan, a.k.a. Wolverine. Charles Xavier, who owns a school for young mutants, sends Storm and Cyclops to bring them back before it is too late. Magneto, who believes a war is approaching, has an evil plan in mind, and needs young Rogue to help him.', 'nm0001741', '2000-07-14', 'PG-13', 104, '');
a2m Table (Actors 2 Movies)
CREATE TABLE IF NOT EXISTS `a2m` (
`id` int(20) NOT NULL AUTO_INCREMENT,
`mid` varchar(100) NOT NULL,
`aid` varchar(100) NOT NULL,
`part` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
INSERT INTO `a2m` (`id`, `mid`, `aid`, `part`) VALUES
(1, 'tt0120903', 'nm0413168', 'Logan / Wolverine'),
(2, 'tt0120903', 'nm0001772', 'Professor Charles Xavier');
actors Table
CREATE TABLE IF NOT EXISTS `actors` (
`id` varchar(100) NOT NULL,
`fname` text NOT NULL,
`lname` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actors` (`id`, `fname`, `lname`) VALUES
('nm0413168', 'Hugh', 'Jackman'),
('nm0001772', 'Patrick', 'Stewart');
Now what i want to do is when a Movie page is loaded, i want to:
- Grab the actors id
- Display the actors first and last name
I am using php5+mysql5.
Here is what i originally had:
$result = mysql_query("SELECT actors.* FROM actors LEFT JOIN a2m ON actors.id=a2m.aid GROUP BY mid ORDER BY actors.fname ASC");
while ($row = mysql_fetch_assoc($result))
{
printf(' <li>%s %s</li>',
htmlspecialchars($row['fname']),
htmlspecialchars($row['lname']),
$row['fname']);
}
Any help is greatly appreciated and once my project is done i will list your name in the helpers box (if ya want)!!! <3
