Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 12th, 2006, 03:33 PM
Registered User
Join Date: Jul 2006
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default table already exists

when i go to my "createmovie.php" file from mozilla or internet explorer, i get the following message "Table 'movie' already exists", the code for the createmovie.php is the following:

//connect to MySQL
$connect = mysql_connect("localhost", "root", "mypassword") or
        die("Hey loser, check your server connection.");

//create the main database if it doesnt already exist
$create = mysql_query("CREATE DATABASE IF NOT EXISTS moviesite") or

//activate the newly created database

//create "movie" table
$movie = "CREATE TABLE movie (
    movie_id int(11) NOT NULL auto_increment,
    movie_name varchar(225) NOT NULL,
    movie_type tinyint(2) NOT NULL default 0,
    movie_year int(4) NOT NULL default 0,
    movie_leadactor int(11) NOT NULL default 0,
    movie_director int(11) NOT NULL default 0,
    PRIMARY KEY (movie_id),
    KEY movie_type(movie_type,movie_year)

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

//create "movietype table
$movietype = "CREATE TABLE movietype (
    movietype_id int(11) NOT NULL auto_increment,
    movietype_label varchar(100) NOT NULL,
    PRIMARY KEY (movietype_id)

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

//create "people" table
$people = "CREATE TABLE people (
    people_id int(11) NOT NULL auto_increment,
    people_fullname varchar(225) NOT NULL,
    people_isactor tinyint(1) NOT NULL default 0,
    people_isdirector tinyint(1) NOT NULL default 0,
    PRIMARY KEY (people_id)

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

echo "Movie Database successfully created!";


and also when i go to my file moviedata.php, which supplies all the info to populate createmovie.php i get the following message in internet explorer or mozilla: "Duplicate entry '1' for key 1"
The code for moviedata.php is:


//connect to MySQL

$connect = mysql_connect("localhost", "root", "mypassword")
    or die ("Hey loser, check your server connection.");

//make sure we're using the right database

//insert data into "movie" table
$insert = "INSERT INTO movie (movie_id, movie_name, movie_type, " .
       "movie_year, movie_leadactor, movie_director) ".
       " VALUES (1, 'Bruce Almighty', 5, 2003, 1, 2), " .
       "(2, 'Officed Space', 5, 1999, 5, 6), " .
       "(3, 'Grand Canyon', 2, 1991, 4, 3)";
$results = mysql_query($insert)
    or die(mysql_error());

//insert data into "movietype" table
$type = "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') ";
$results = mysql_query($type)
    or die(mysql_error());

//insert data into "people" table
$people = "INSERT INTO people (people_id, people_fullname, " .
        "people_isactor, people_isdirector) " .
        "VALUES (1, 'Jim Carey', 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)";
$results = mysql_query($people)
    or die(mysql_error());

echo "Data inserted successfully!";


an error that i did find in the code is in a few different instances i had wrote "..or die(mysql_query()); instead of or die(mysql_error());," so i dont know if that messed something up early on, and now i have to "un-do" something..but im not sure, im just following a long in the book "beginning php5, apache, mysql" on page 94.

can anyone please help me figure out why i cant get this to work.
thanks in advance :)

Old July 13th, 2006, 03:01 AM
Friend of Wrox
Join Date: Jan 2006
Posts: 198
Thanks: 0
Thanked 0 Times in 0 Posts

First off, your CREATE TABLE IF NOT EXISTS query never gets executed. You create a string containing that query, but you never execute it. In fact, you then create another query for creating the table without the IF case and then execute that. If you want to keep with this architecture, you should put IF NOT EXISTS in all your CREATE TABLE queries.

However, I do not think you should have CREATE TABLE queries inside a web-accessible PHP page. You should create these pages manually and then have your PHP pages rely on their existence. You should at least move your table creation script into a simple mysqldump-style script, which you would pipe to the mysql executable, rather than needlessly putting it in PHP. In fact, in a highly secured environment, the MySQL user you use from PHP should not have permission to create or drop tables -- only select, insert, update, and delete.

Jon Emerson

Similar Threads
Thread Thread Starter Forum Replies Last Post
how to check the username already exists in table asad_black ASP.NET 2.0 Basics 2 September 15th, 2008 07:19 AM
check if a column exists in the table sands SQL Server 2000 2 April 21st, 2006 11:08 PM
How to test if a column exists in a table jtrifts SQL Server 2000 2 February 16th, 2006 02:25 PM
How to detect if a table exists? mnoon Access 5 February 11th, 2005 09:43 AM
If table exists in database meghana VB Databases Basics 1 September 29th, 2004 10:39 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.