Page 1 of 1

Database creation from PHP function, need help

Posted: Mon Sep 10, 2007 3:55 am
by crazytopu
Hiya,

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);

}
Many Thanks for your help

Posted: Mon Sep 10, 2007 4:17 am
by josa
You have to do one query at a time. The query will be terminated at the first semicolon (;). Or as the manual puts it:
mysql_query() sends an unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier.
/josa

Posted: Mon Sep 10, 2007 7:38 am
by superdezign
You could explode() on semicolons and run each query.

Code: Select all

$queries = explode(';', $queryData);

foreach ($queries as $query) {
    mysql_query($query);
}