Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005
    Posts
    18

    Unanswered: SQL database creation script Error message

    I have got an sql script that should create a database and tables for me automatically but it keeps on giving me error messages that I should check my mysql commands.
    Can any one help me with this. I do not think there is any error with the script though.
    This is the sql script sample below:

    -- MySQL Administrator dump 1.4
    --
    -- ------------------------------------------------------
    -- Server version 5.0.16-nt


    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;

    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


    --
    -- Create schema property
    --

    CREATE DATABASE /*!32312 IF NOT EXISTS*/ property;
    USE property;

    --
    -- Table structure for table `property`.`advertise`
    --

    DROP TABLE IF EXISTS `advertise`;
    CREATE TABLE `advertise` (
    `houseId` int not null primary key auto_increment,
    `userID` int not null,
    `bannerId` varchar(45) NOT NULL,
    `numberOfBanners` int(10) unsigned NOT NULL,
    `timeUnits` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table `property`.`advertise`
    --

    /*!40000 ALTER TABLE `advertise` DISABLE KEYS */;
    /*!40000 ALTER TABLE `advertise` ENABLE KEYS */;


    --
    -- Table structure for table `property`.`carddesc`
    --

    DROP TABLE IF EXISTS `carddesc`;
    CREATE TABLE `carddesc` (
    `houseId` int not null primary key auto_increment,
    `userID` int not null,
    `cardType` varchar(45) NOT NULL,
    `cardName` varchar(45) NOT NULL,
    `cardAddress` varchar(45) NOT NULL,
    `cardPostCode` varchar(45) NOT NULL,
    `cardNumber` varchar(45) NOT NULL,
    `cardSecCode` varchar(45) NOT NULL,
    `cardIssueDate` time NOT NULL,
    `cardExpDate` time NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table `property`.`carddesc`
    --

    /*!40000 ALTER TABLE `carddesc` DISABLE KEYS */;
    /*!40000 ALTER TABLE `carddesc` ENABLE KEYS */;


    --
    -- Table structure for table `property`.`housedesc`
    --

    DROP TABLE IF EXISTS `housedesc`;
    CREATE TABLE `housedesc` (
    `houseId` int not null primary key auto_increment,
    `houseName` varchar(45),
    `NumOfSingleRooms` enum ("1", "2","3", ">3"),
    `rooms` enum("1","2", "3",">3"),
    `bathroom` enum ("Bath", "Shower", "Towel", "Toilet Tissue", "Shower Gel", "Shampoo"),
    `Storage` enum("Yes", "No"),
    `Garden` enum("Yes", "No"),
    `Garage` enum("Yes", "No"),
    `childFac` enum("Yes", "No"),
    `lift` enum("Yes", "No"),
    `Floor` int(10) unsigned default NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;



    --
    -- Table structure for table `property`.`amennities`
    --

    DROP TABLE IF EXISTS `amenities`;
    CREATE TABLE `amenities` (
    `houseId` int not null primary key auto_increment,
    `bathroom` enum ("Bath", "Shower", "Towel", "Toilet Tissue", "Shower Gel", "Shampoo"),
    `kitchen` enum ("Microwave", "Cooker", "Kettle for boiling water", "Fridge/Freezer", "Washing machine", "Laundry Facilities"),
    `meal` enum ("Breakfast", "Lunch", "Dinner", "Tea provided in room", "Coffee provided in room", "No meals"),
    `otherFacitities` enum ("Internet", "Phone", "Cable or Satelite TV")
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


    --
    -- Dumping data for table `property`.`housedesc`
    --

    /*!40000 ALTER TABLE `housedesc` DISABLE KEYS */;
    /*!40000 ALTER TABLE `housedesc` ENABLE KEYS */;


    --
    -- Table structure for table `property`.`personalprofile`
    --

    DROP TABLE IF EXISTS `personalprofile`;
    CREATE TABLE `personalprofile` (
    `houseId` int not null primary key auto_increment,
    `addressID` int not null,
    `fullName` varchar(45) NOT NULL,
    `addressType` enum ("Home", "Business", "Others"),
    `phone` integer unsigned,
    `tel_number` integer unsigned,
    `tel_type` enum ("Landline", "Mobile", "Others"),
    `fax_number` integer unsigned,
    `fax_type` enum ("Home", "BusinessS", "Others"),
    `email` varchar(45) NOT NULL,
    `email_type` enum ("Personal", "BusinessS", "Others"),
    `note` text,
    `mobile` integer unsigned NOT NULL,
    `fax` int(15) unsigned NOT NULL,
    `PreModeContact` enum ("Landline", "Mobile", "Email", "Fax"),
    `contactFrom` time,
    `contactTill` time,
    `date_added` datetime,
    `date_modified` datetime
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table `property`.`personalprofile`
    --

    /*!40000 ALTER TABLE `personalprofile` DISABLE KEYS */;
    /*!40000 ALTER TABLE `personalprofile` ENABLE KEYS */;


    --
    -- Table structure for table `property`.`pvtpersonaltype`
    --

    DROP TABLE IF EXISTS `pvtpersonaltype`;
    CREATE TABLE `pvtpersonaltype` (
    `houseId` int not null primary key auto_increment,
    `gender` enum ("Male", "Female", "Any"),
    `type` enum ("Professional", "Student", "Retired person", "Others", "Anybody"),
    `couple` enum ("Yes", "No"),
    `NumPeople` enum ("one", "two", "Three", "More"),
    `personWithKid` enum ("Yes", "No"),
    `MaxNumOfkids` enum ("1", "2", "3", "4", ">5"),
    `Smoker` enum ("Yes", "No"),
    `PetsAllowed` enum ("Yes", "No"),
    `MaxNumOfPets` enum ("1", "2", "3"),
    `ChargeBase` enum ("Day", "Week", "Month", "Year"),
    `singleRoomCharge` integer unsigned NOT NULL,
    `DoubleRoomCharge` integer unsigned NOT NULL,
    `ChargePerPerson` integer unsigned NOT NULL,
    `ChargePerChild` integer unsigned NOT NULL,
    `fullPropertyCharges` integer unsigned NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table `property`.`pvtpersonaltype`
    --

    /*!40000 ALTER TABLE `pvtpersonaltype` DISABLE KEYS */;
    /*!40000 ALTER TABLE `pvtpersonaltype` ENABLE KEYS */;


    --
    -- Table structure for table `property`.`pvtpropertydesc`
    --

    DROP TABLE IF EXISTS `pvtpropertydesc`;
    CREATE TABLE `pvtpropertydesc` (
    `houseId` int not null primary key auto_increment,
    `PropertyType` enum ("terrace", "flat", "house", "Semi-detached", "Bungalow", "Studio Flat", "Maisonette"),
    `NumOfRooms` enum ("1", "2", "3", "4", "5", ">5"),
    `NumOfSingleRooms` enum ("1", "2", "3", "4", "5", ">5"),
    `NumOfDoubleRooms` enum ("1", "2", "3", "4", "5", ">5"),
    `Units` enum ("1", "2", "3", "4", "5", "5"),
    `Period` enum ("Days", "Weeks", "Months", "Years"),
    `parking` enum ("Off road", "On street", "Pay & display", "Parking permit required"),
    `heating` enum ("Central heating", "Storage heater", "Electric fire", "Gas fire"),
    `RentalPeriod` enum ("1", "2", "3", "4", "5", "6"; "7", "8", "9", "10", "11", "12"),
    `RentalUnit` enum ("Days", "Weeks", "Months", "Years"),
    `roomfloor` enum ("Ground Floor", "First floor", "Second Floor", "Higher Floor"),
    `lift` enum ("Yes", "No"),
    `AvailableFrom` text
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table `property`.`pvtpropertydesc`
    --

    /*!40000 ALTER TABLE `pvtpropertydesc` DISABLE KEYS */;
    /*!40000 ALTER TABLE `pvtpropertydesc` ENABLE KEYS */;


    --
    -- Table structure for table `property`.`pvtpropertyinfo`
    --

    DROP TABLE IF EXISTS `pvtpropertyinfo`;
    CREATE TABLE `pvtpropertyinfo` (
    `houseId` int not null primary key auto_increment,
    `userId` int NOT NULL,
    `addressID` int not null,
    `houseName` varchar(45),
    `houseNum` varchar(45),
    `flatNum` varchar(45),
    `address` varchar(255) NOT NULL,
    `area` varchar(45) NOT NULL,
    `city` varchar(45) NOT NULL,
    `postCode` varchar(10) not null,
    `country` varchar(45) NOT NULL,
    `county` varchar(45) NOT NULL,
    `streetName` varchar(45) NOT NULL,
    `date_added` datetime,
    `date_modified` datetime
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    --
    -- Dumping data for table `property`.`pvtpropertyinfo`
    --

    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user` (
    `username` varchar(16) primary key,
    `passwd` char(16) not null,
    `email` varchar(100) not null
    );



    grant ALL
    on property.*
    to root@localhost identified by 'password';


    /*!40000 ALTER TABLE `pvtpropertyinfo` DISABLE KEYS */;
    /*!40000 ALTER TABLE `pvtpropertyinfo` ENABLE KEYS */;

    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

    I will be glad if someone can help me out. Thanks.

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    What line number is your error coming from?

    Try posting in the MySQL forum instead of php.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •