Table people doesn't exist CH3 p.87-89

hunt4it · June 1st, 2011, 07:36 PM

Hiya

I have created all of my tables as per the book but when I run the scripts I get the message that the table people doesn't exist although I'm sure it has been created with the associating script inserting data. The script used is below:

Code:

<?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 datbase 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=MySAM';
mysql_query($query, $db) or die (mysql_error($db));

//create the movietype table
$query = 'CREATE TABLE movietype (
	movietype_id	TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
	movietype_label VARCHAR(100)     NOT NULL,
	PRIMARY KEY (movietype_id)
	)
	ENGINE=MySAM';
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 (225)		NOT NULL,
	people_isactor    TINYINT (1) UNSIGNED	NOT NULL DEFAULT 0,
	people_isdirector TINYINT (1) UNSIGNED  NOT NULL DEAFULT 0,

	PRIMARY KEY (people_id)

	)
	ENGINE=MySAM';
mysql_query($query, $db) or die (mysql_error($db));

echo 'Movie database successfully created!';

?>

After using MySql console to check this using SHOW TABLES it only shows the tables for movie and movie_type. I have deleted all tables and then re-started Wamp Server to run again but still no joy.

Can anyone explaim why this is happeining as I don't think it's a syntax error?

atv41 · June 5th, 2011, 03:26 AM

Table people does not exists because of an extra space in table's definitions for people_fullname, people_isactor and people_isdirector variables.

Your code is as following:-

Code:

//create the people table
$query = 'CREATE TABLE people (
	people_id	      INTEGER UNSIGNED	NOT NULL AUTO_INCREMENT,
	people_fullname   VARCHAR (225)		NOT NULL,
	people_isactor    TINYINT (1) UNSIGNED	NOT NULL DEFAULT 0,
	people_isdirector TINYINT (1) UNSIGNED  NOT NULL DEAFULT 0,

	PRIMARY KEY (people_id)

	)
	ENGINE=MySAM';
mysql_query($query, $db) or die (mysql_error($db));

The correct code should be as following:-

Code:

//create the people table
$query = 'CREATE TABLE people (
	people_id	      INTEGER UNSIGNED	NOT NULL AUTO_INCREMENT,
	people_fullname   VARCHAR(225)		NOT NULL,
	people_isactor    TINYINT(1) UNSIGNED	NOT NULL DEFAULT 0,
	people_isdirector TINYINT(1) UNSIGNED   NOT NULL DEAFULT 0,

	PRIMARY KEY (people_id)

	)
	ENGINE=MySAM';
mysql_query($query, $db) or die (mysql_error($db));

hunt4it · June 5th, 2011, 07:32 PM

Ahhh, hadn't noticed this small thing so will check closely for this in the future. Thanks for that I can crack on now (though sure I will have other challenges along the way.