Hi I need help regarding basic MySQL

I am doing this all by reading a programming book and following the directions but I think the book has some error and I can't figure it out, can someone help me.

So basically, when I open the file table1.php this is what I get as the output.

Unknown column 'movietype' in 'field list'

I'm still a beginner so if someone could help me figure this out It'd be appreciated.

This is my code for the table1.php:

<?php
//connect to MySQL
$db = mysql_connect('localhost', 'root', '') or
    die('Unable to connect. Check your connection parameters.');
    
    // make sure you're using the right database
    mysql_select_db('moviesite', $db) or die(mysql_error($db));
    
// retrieve information
$query = 'SELECT 
            movie_name, movie_year, movie_director, movie_leadactor, movietype
        FROM
            movie
        ORDER BY
            movie_name ASC,
            movie_year DESC';
        
$result = mysql_query($query, $db) or die(mysql_error($db));

// determine number of rows in returned result
$num_movies = mysql_num_rows($result);
?>

<div style="text-align: center;">
<h2>Movie Review Database</h2>

<table border="1" cellpadding="2" cellspacing="2" 
style="width: 70%; margin-left: auto; margin-right: auto;">
<tr>
    <th>Movie Title</th>
    <th>Year of Release</th>
    <th>Movie Director</th>
    <th>Movie Lead Actor</th>
    <th>Movie Type</th>
</tr>

<?php
// loop through the results
while ($row = mysql_fetch_assoc($result)) {
    extract($row);
    echo '<tr>';
    echo '<td>' . $movie_name . '</td>';
    echo '<td>' . $movie_year . '</td>';
    echo '<td>' . $movie_director . '</td>';
    echo '<td>' . $movie_leadactor . '</td>';
    echo '<td>' . $movie_type . '</td>';
    echo '</tr>';
}
?>

</table>
<p><?php echo $num_movies; ?> Movies</p>
</div>

This is my code for the Database db_ch03-1.php

<?php
//connect to MySQL
$db = mysql_connect('localhost', 'root', '') or 
    die ('Unable to connect. Check your connection parameters.');

//create the main database if it doesn't already exist
$query = 'CREATE DATABASE IF NOT EXISTS moviesite';
mysql_query($query, $db) or die(mysql_error($db));

//make sure our recently created database is the active one
mysql_select_db('moviesite', $db) or die(mysql_error($db));

//create the movie table
$query = 'CREATE TABLE movie (
        movie_id        INTEGER UNSIGNED  NOT NULL AUTO_INCREMENT, 
        movie_name      VARCHAR(255)      NOT NULL, 
        movie_type      TINYINT           NOT NULL DEFAULT 0, 
        movie_year      SMALLINT UNSIGNED NOT NULL DEFAULT 0, 
        movie_leadactor INTEGER UNSIGNED  NOT NULL DEFAULT 0, 
        movie_director  INTEGER UNSIGNED  NOT NULL DEFAULT 0, 

        PRIMARY KEY (movie_id),
        KEY movie_type (movie_type, movie_year) 
    ) 
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

//create the movietype table
$query = 'CREATE TABLE movietype ( 
        movietype_id    TINYINT NOT NULL AUTO_INCREMENT, 
        movietype_label VARCHAR(100)     NOT NULL, 

        PRIMARY KEY (movietype_id)
    ) 
    ENGINE=MyISAM';
mysql_query($query, $db) or die(mysql_error($db));

//create the people table
$query = 'CREATE TABLE people ( 
        people_id         INTEGER 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';
mysql_query($query, $db) or die(mysql_error($db));

echo 'Movie database successfully created!';
?>

I'm certainly no expert, but did you try changing movietype to movie_type in this line?

movie_name, movie_year, movie_director, movie_leadactor, movietype

To match this

movie_type      TINYINT           NOT NULL DEFAULT 0,

Wow it worked, thank you very much.

Hi Geek I ran into another stump, can you help me out again. I can't figure this one out tried to do it for hours.

This is my table2.php code

<?php

//take in the id of a director and return his/her full name
function get_director($director_id) {
	
	global $db;
	
	$query = 'SELECT
			people_fullname
		FROM
			people
		WHERE
			people_id = ' . $director_id;
	$result = mysql_query($query, $db) or die(mysql_error($db));
	
	$row = mysql_fetch_assoc($result);
	extract($row);
	
	return $people_fullname;
}

//take in the id of a lead actor and return his/her full name
function get_leadactor($leadactor_id) {
	
	global $db;
	$query = 'SELECT
			people_fullname
		FROM
			people
		WHERE
			people_id = ' . $leadactor_id;
			
		$result = mysql_query($query, $db) or die(mysql_error($db));
		
		$row = mysql_fetch_assoc($result);
		extract($row);
		
		return $people_fullname;
}

//take in the id of a movie type and return the meaningful textual
//description

function get_movietype($type_id) {
	
	global $db;
	
	$query = 'SELECT
			movietype_label
		FROM
			movietype
		WHERE
			movietype_id ' . $type_id;
	$result = mysql_query($query, $db) or die(mysql_error($db));
	
	$row = mysql_fetch_assoc($result);
	extract($row);
	
	return $movietype_label;
}

//connect to MySQL
	$db = mysql_connect('localhost', 'root', '') or
	die('Unable to connect. Check your connection parameters.');
	
//make sure you're using the right database
	mysql_select_db('moviesite', $db) or die(mysql_error($db));
	
//retrieve information
	$query = 'SELECT
		movie_name, movie_year, movie_director, movie_leadactor, movie_type
	FROM
		movie
	ORDER BY
		movie_name ASC,
		movie_year DESC';
$result = mysql_query($query, $db) or die(mysql_error($db));

//determine number of rows in returned result
$num_movies = mysql_num_rows($result);

$table = <<<ENDHTML
<div style="text-align: center;">
<h2>Movie Review Database</h2>
<table border="1" cellpadding="2" cellspacing="2"
style="width: 70%; margin-left: auto; margin-right: auto;">

<tr>
	<th>Movie Title</th>
	<th>Year of Release</th>
	<th>Movie Director</th>
	<th>Movie Lead Actor</th>
	<th>Movie Type</th>
</tr>
ENDHTML;

//loop through the results
while ($row = mysql_fetch_assoc($result)) {
	extract($row);
	$director = get_director($movie_director);
	$leadactor = get_leadactor($movie_leadactor);
	$movietype = get_movietype($movie_type);
	
	$table .= <<<ENDHTML
<tr>
	<td>$movie_name</td>
	<td>$movie_year</td>
	<td>$director</td>
	<td>$leadactor</td>
	<td>$movietype</td>
</tr>
ENDHTML;
}

$table .= <<<ENDHTML
</table>
<p>$num_movies Movies</p>
</div>
ENDHTML;

echo $table;
?>

This is my code for the Database db_ch03-1.php

<?php
//connect to MySQL
$db = mysql_connect('localhost', 'root', '') or 
    die ('Unable to connect. Check your connection parameters.');

//create the main database if it doesn't already exist
$query = 'CREATE DATABASE IF NOT EXISTS moviesite';
mysql_query($query, $db) or die(mysql_error($db));

//make sure our recently created database is the active one
mysql_select_db('moviesite', $db) or die(mysql_error($db));

//create the movie table
$query = 'CREATE TABLE movie (
        movie_id        INTEGER UNSIGNED  NOT NULL AUTO_INCREMENT, 
        movie_name      VARCHAR(255)      NOT NULL, 
        movie_type      TINYINT           NOT NULL DEFAULT 0, 
        movie_year      SMALLINT UNSIGNED NOT NULL DEFAULT 0, 
        movie_leadactor INTEGER UNSIGNED  NOT NULL DEFAULT 0, 
        movie_director  INTEGER UNSIGNED  NOT NULL DEFAULT 0, 

        PRIMARY KEY (movie_id),
        KEY movie_type (movie_type, movie_year) 
    ) 
    ENGINE=MyISAM';
mysql_query($query, $db) or die (mysql_error($db));

//create the movietype table
$query = 'CREATE TABLE movietype ( 
        movietype_id    TINYINT NOT NULL AUTO_INCREMENT, 
        movietype_label VARCHAR(100)     NOT NULL, 

		PRIMARY KEY (movietype_id)
    ) 
    ENGINE=MyISAM';
mysql_query($query, $db) or die(mysql_error($db));

//create the people table
$query = 'CREATE TABLE people ( 
        people_id         INTEGER 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';
mysql_query($query, $db) or die(mysql_error($db));

echo 'Movie database successfully created!';
?>

Oh I forgot to tell you what error I get. The error I get is:

Table 'moviesite.people' doesn't exist

Can anyone still help me. It's been a couple days and I still cannot figure this out.

It seems the people table doesn't exist. Are you sure it has been successfully created?

Yes it has been created. But how can I double check?

Please show a bit of code or the query being run when the error occurs.

Table 'moviesite.people' doesn't exist

This is the error I get when I try to open it.

Thank you

I'm sorry but I can't see what's wrong. Since MySQL is saying the table doesn't exist, it probably doesn't. Either it isn't created at all, or you've made some spelling mistake or similar (check for big and small letters) so that MySQL is looking for the wrong table.

I did find another error though, but it will not fix this problem. In the function get_movietype() the line

movietype_id ' . $type_id;

should be

movietype_id = ' . $type_id;