I have been trying to do the following from php function calls:
1. drop an earlier database
2. create an entire database again
3. create all the tables after the database has been created.
I am having some problem with creating the tables. Please find my questions inside the comment blocks):
Code: Select all
function createDatabase(){
global $connection;
$createDatabaseQuery="CREATE DATABASE `test`";
/* is there anyway I can put this long query (create Table query) separately somewhere and use it in the function? This is to make the function looks neat and clean. I thought of using a different php file, use a variable to hold the query and include that php file in this API page to be able to use the query variable in this functin. Is this a good enough idea? */
$createDatabaseTable="
CREATE TABLE product(
productID char( 16 ) NOT NULL ,
productName char( 32 ) ,
price float( 5, 2 ) ,
img char( 32 ) ,
catalogueRef char( 32 ) ,
description char( 150 ) ,
PRIMARY KEY ( productID )
);
CREATE TABLE attribute(
attributeName char( 16 ) NOT NULL ,
PRIMARY KEY ( attributeName )
);
CREATE TABLE productAttributes(
productID char( 16 ) NOT NULL REFERENCES product,
attributeName char( 16 ) NOT NULL REFERENCES attribute,
PRIMARY KEY ( productID, attributeName )
);
CREATE TABLE value(
valueName char( 16 ) NOT NULL ,
PRIMARY KEY ( valueName )
);
CREATE TABLE attributeValues(
valueName char( 16 ) NOT NULL REFERENCES value,
attributeName char( 16 ) NOT NULL REFERENCES attribute,
PRIMARY KEY ( valueName, attributeName )
);
CREATE TABLE chosenProducts(
choiceID int( 100 ) NOT NULL ,
productID char( 16 ) NOT NULL REFERENCES product,
PRIMARY KEY ( choiceID )
);
CREATE TABLE productAttributePossibleValues(
productID char( 16 ) NOT NULL REFERENCES productAttributes,
attributeName char( 16 ) NOT NULL REFERENCES productAttributes,
valueName char( 16 ) NOT NULL REFERENCES attributeValues,
PRIMARY KEY ( productID, attributeName, valueName )
);
CREATE TABLE chosenProductAttributesAndValues(
productID char( 16 ) NOT NULL REFERENCES productAttributePossibleValues,
choiceID int( 100 ) NOT NULL REFERENCES chosenProducts,
attributeName char( 16 ) NOT NULL REFERENCES productAttributePossibleValues,
valueName char( 16 ) NOT NULL REFERENCES productAttributePossibleValues,
PRIMARY KEY ( productID, choiceID, attributeName, valueName, choiceID )
);
CREATE TABLE lineItem(
lineItemID int( 200 ) NOT NULL ,
choiceID int( 100 ) NOT NULL REFERENCES chosenProduct,
quantity int( 10 ) ,
PRIMARY KEY ( lineItemID )
);
CREATE TABLE cart(
cartID char( 25 ) NOT NULL ,
PRIMARY KEY ( cartID )
);
CREATE TABLE cartLineItems(
lineItemId int( 200 ) NOT NULL REFERENCES lineItem,
cartID char( 25 ) NOT NULL REFERENCES cart,
PRIMARY KEY ( cartID, lineItemID )
);
CREATE TABLE customer(
customerID int( 100 ) NOT NULL ,
name char( 16 ) ,
title char( 5 ) ,
city char( 16 ) ,
HouseNo char( 16 ) ,
street char( 16 ) ,
postCode char( 16 ) ,
Email char( 50 ) ,
PRIMARY KEY ( customerID )
);
CREATE TABLE country(
countryName char( 16 ) NOT NULL ,
PRIMARY KEY ( countryName )
);
CREATE TABLE orders(
orderID int( 100 ) NOT NULL ,
cartID char( 25 ) NOT NULL REFERENCES cart,
customerID int( 100 ) NOT NULL REFERENCES customer,
countryName char( 16 ) NOT NULL REFERENCES country,
method char( 40 ) NOT NULL REFERENCES deliveries,
paymentOption char( 40 ) ,
PRIMARY KEY ( orderID )
);
CREATE TABLE deliveries(
method char( 40 ) NOT NULL ,
duration int( 3 ) ,
cost float( 5, 2 ) ,
PRIMARY KEY ( method )
);
";
/* this create the database fine.*/
mysql_query($createDatabaseQuery);
mysql_query($createDatabaseQuery);
/* this check works fine as well.*/
$db_selected = mysql_select_db('test', $connection);
if (!$db_selected) {
die ('Can\'t use test : ' . mysql_error());
}
/* but this does not create the tables in test database, the query is okay, I tested the sql statemetns. any idea why. Is there any function that i can use to check if a table has been created? */
mysql_query($createDatabaseTable);
}