I'm getting noting

[Jragon]

When i try and use this code i get nothing but what i want is to have the lead actor and movie name in a table

 

code:

<?php
    $link = include("connect.php");
    include("connect.php");
    mysql_select_db('moviesite') or die(mysql_error);

    //select the movie titles and there ganre after 1990
    $query = 'SELECT
            movie.movie_name, people.people_fullname
        FROM
            movie LEFT JOIN people ON people_fullname = movie_leadactor
        WHERE
            movie.movie_leadactor = people.people_id
        ORDER BY
            movie_type';

    $result = mysql_query($query) or die(mysql_error);

    //Show results
    echo '<table border="1"';
    while($row = mysql_fetch_assoc($result)){
        echo '<tr>';
        echo '<tr>';
        foreach ($row as $value){
            echo '<td>' . $value . '</td>';
        }
        echo '</tr>';
    }
    echo '</table>';
?>

 

mysql export:

-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 06, 2010 at 07:36 PM
-- Server version: 5.1.41
-- PHP Version: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `moviesite`
--

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

--
-- Table structure for table `movie`
--

CREATE TABLE IF NOT EXISTS `movie` (
  `movie_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `movie_name` varchar(255) NOT NULL,
  `movie_type` tinyint(4) NOT NULL DEFAULT '0',
  `movie_year` smallint(5) unsigned NOT NULL DEFAULT '0',
  `movie_leadactor` int(10) unsigned NOT NULL DEFAULT '0',
  `movie_director` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`movie_id`),
  KEY `movie_type` (`movie_type`,`movie_year`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `movie`
--

INSERT INTO `movie` (`movie_id`, `movie_name`, `movie_type`, `movie_year`, `movie_leadactor`, `movie_director`) VALUES
(1, 'Bruce Almighty', 5, 2003, 1, 2),
(2, 'Office Space', 5, 1999, 5, 6),
(3, 'Grand Canyon', 2, 1991, 4, 3);

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

--
-- Table structure for table `movietype`
--

CREATE TABLE IF NOT EXISTS `movietype` (
  `movietype_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `movietype_label` varchar(100) NOT NULL,
  PRIMARY KEY (`movietype_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Dumping data for table `movietype`
--

INSERT INTO `movietype` (`movietype_id`, `movietype_label`) VALUES
(1, 'Sci Fi'),
(2, 'Drama'),
(3, 'Adventure'),
(4, 'War'),
(5, 'Comedy'),
(6, 'Horror'),
(7, 'Action'),
(8, 'Kids');

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

--
-- Table structure for table `people`
--

CREATE TABLE IF NOT EXISTS `people` (
  `people_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `people_fullname` varchar(255) NOT NULL,
  `people_isactor` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `people_isdirector` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`people_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Dumping data for table `people`
--

INSERT INTO `people` (`people_id`, `people_fullname`, `people_isactor`, `people_isdirector`) VALUES
(1, 'Jim Carrey', 1, 0),
(2, 'Tom Shadyac', 0, 1),
(3, 'Lawrence Kasdan', 0, 1),
(4, 'Kevin Kline', 1, 0),
(5, 'Ron Livingston', 1, 0),
(6, 'Mike Judge', 0, 1);

 

Please help

 

Thanks

 

Jragon

[msaz87]

Try:

 

$query = " SELECT titles.movie_name AS movie_title, actors.people_fullname AS lead_actor 
                  FROM movie AS titles 
                  JOIN people AS actors 
                  ON titles.move_leadactor = actors.people_id 
                  ORDER BY titles.movie_type";

$results = mysql_query($query) or die(mysql_error());

echo "<table>";
echo "<tr><td><b>Movie Title</b></td><td><b>Lead Actor</b></td></tr>";

while($row = mysql_fetch_array($results)) {

      echo "<tr>";
      echo "<td>".$row['movie_title']."</td>";
      echo "<td>".$row['lead_actor']."</td>";
      echo "</tr>";

}

echo "</table>";

[Jragon]

Still getting nothing

 

[msaz87]

Do you get any kind of error? Or is it just an empty table? Post the output source and that might help

[Jragon]

Whats the out put source?

[MadTechie]

ON titles.move_leadactor = actors.people_id

 

I assume msaz87 means "movie_leadactor"

 

Jragon define "Nothing!" are you getting any output at all ?

[MadTechie]

ON titles.move_leadactor = actors.people_id

 

I assume msaz87 means "movie_leadactor"

 

Jragon define "Nothing!" are you getting any output at all ?

 

View source on the browser

[msaz87]

ON titles.move_leadactor = actors.people_id

 

I assume msaz87 means "movie_leadactor"

 

Whoops. Good catch... my mistake

[Jragon]

i have fiddeld with it a bit more and i now get:

Not unique table/alias: 'people'

 

my codeL

<?php
    $link = include("connect.php");
    include("connect.php");
    mysql_select_db('moviesite') or die(mysql_error);

    $query = " SELECT movie.movie_name AS movie_title, people.people_fullname AS people_fullname
                  FROM movie AS people
                  JOIN movie AS people
                  ON movie.move_leadactor = people.people_id
                  ORDER BY titles.movie_type";

    $results = mysql_query($query) or die(mysql_error());

    echo "<table>";
    echo "<tr><td><b>Movie Title</b></td><td><b>Lead Actor</b></td></tr>";

    while($row = mysql_fetch_array($results)) {

        echo "<tr>";
        echo "<td>".$row['movie_title']."</td>";
        echo "<td>".$row['lead_actor']."</td>";
        echo "</tr>";

    }

    echo "</table>";
?>

[MadTechie]

FROM movie AS people
JOIN movie AS people

Doesn't make any sense

 

try

SELECT movie.movie_name AS movie_title, people.people_fullname AS people_fullname
FROM movie
JOIN people ON movie.move_leadactor = people.people_id
ORDER BY titles.movie_type

[msaz87]

You renamed the aliases the same thing... that won't work. Try using the below with my typo corrected:

 

$query = " SELECT titles.movie_name AS movie_title, actors.people_fullname AS lead_actor 
                  FROM movie AS titles 
                  JOIN people AS actors 
                  ON titles.movie_leadactor = actors.people_id 
                  ORDER BY titles.movie_type";

$results = mysql_query($query) or die(mysql_error());

echo "<table>";
echo "<tr><td><b>Movie Title</b></td><td><b>Lead Actor</b></td></tr>";

while($row = mysql_fetch_array($results)) {

      echo "<tr>";
      echo "<td>".$row['movie_title']."</td>";
      echo "<td>".$row['lead_actor']."</td>";
      echo "</tr>";

}

echo "</table>";

 

If it's still nothing, copy the source code out of the browser and show us it

[Jragon]

Its now sort of working this is what sorce code i get now:

<table><tr><td><b>Movie Title</b></td><td><b>Lead Actor</b></td></tr><tr><td>Grand Canyon</td><td>Kevin Kline</td></tr><tr><td>Bruce Almighty</td><td>Jim Carrey</td></tr><tr><td>Office Space</td><td>Ron Livingston</td></tr></table>

 

but it is not a table its just a list

[msaz87]

Its now sort of working this is what sorce code i get now:

<table><tr><td><b>Movie Title</b></td><td><b>Lead Actor</b></td></tr><tr><td>Grand Canyon</td><td>Kevin Kline</td></tr><tr><td>Bruce Almighty</td><td>Jim Carrey</td></tr><tr><td>Office Space</td><td>Ron Livingston</td></tr></table>

 

but it is not a table its just a list

 

No... that's a table. Did you want a border on it? Change

<table>

 

to

<table border="1">

[Jragon]

Yeah i needed a border