php/mysql

[ray-solomon]

I am trying to turn this php script into a pure mysl query so it will be condensed and i can run it as a query in msqlman. What parts can i take out to make this work? I know i\'m supposed to take out the php, but afraid i could mess up the sql commands somehow. Please excuse me, i\'m a beginner.

 

<?

include \"config.inc\";

$db = @mysql_connect(\"localhost\", \"$databaseuser\", \"$databasepasswd\");

@mysql_select_db(\"$databasename\",$db);

 

$sql = \"CREATE TABLE customer (

firstname varchar(50) not null,

lastname varchar(50) not null,

email varchar(50) not null,

address varchar(50),

zip varchar(10),

city varchar(30),

phone varchar(20),

customerid int4 not null auto_increment,

orderid int auto_increment not null,

PRIMARY KEY (customerid),

UNIQUE email (email)

)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"CREATE TABLE affiliate (

user varchar(50) not null,

password varchar(50) not null,

firstname varchar(50) not null,

lastname varchar(50) not null,

email varchar(50) not null,

address varchar(50),

zip varchar(10),

city varchar(30),

url varchar(50) not null,

phone varchar(20),

payed float(6,2),

affiliateid int not null auto_increment,

PRIMARY KEY (affiliateid, user),

UNIQUE email (email)

)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"CREATE TABLE user (

user varchar(50) not null,

password varchar(50) not null,

usertype int not null,

PRIMARY KEY (user)

)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"CREATE TABLE orders (

customerid int not null,

orderid int not null auto_increment,

products varchar(100) not null,

date varchar(30) not null,

price varchar(15) not null,

paymentmethod varchar(15) not null,

ip varchar(15) not null,

PRIMARY KEY (orderid)

)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"CREATE TABLE orderaffiliate (

affiliateid int not null,

orderid int not null,

PRIMARY KEY (orderid)

)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"CREATE TABLE affiliatestats (

clicks int,

orders int,

affiliateid int not null,

PRIMARY KEY (affiliateid)

)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"CREATE TABLE category (

categoryid int auto_increment not null,

parentcategoryid int not null,

name varchar(25) not null,

description varchar(255) not null,

PRIMARY KEY (categoryid),

INDEX parentcategoryid (parentcategoryid),

INDEX name (name)

)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"CREATE TABLE product (

productid int auto_increment not null,

name varchar(50) not null,

description varchar(255) not null,

price float(5,2) not null,

PRIMARY KEY (productid),

INDEX namn (name)

)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"CREATE TABLE productcategory (

productid int not null,

categoryid int not null,

PRIMARY KEY (productid, categoryid)

)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"CREATE TABLE manufacturer (

manufacturerid int not null,

company varchar(30),

firstname varchar(30) not null,

lastname varchar(30) not null,

email varchar(50) not null,

address varchar(50),

zip varchar(10),

city varchar(30),

phone varchar(20),

PRIMARY KEY (manufacturerid)

)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"CREATE TABLE productmanufacturer (

productid int not null,

manufacturerid int not null,

PRIMARY KEY (productid, manufacturerid)

)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"INSERT INTO affiliate VALUES(\'test\',\'test\',\'Test\',\'Testman\',

\'test@test.com\',\'Street\',\'23455\',\'City\',\'http://www.yahoo.com\',\'055-55555\',0,1111)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"INSERT INTO affiliatestats (affiliateid, clicks, orders) VALUES (1111,0,0)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"INSERT INTO category (categoryid, parentcategoryid, name, description) VALUES (1,1,\'Software\',\'Computer software\')\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"INSERT INTO category (categoryid, parentcategoryid, name, description) VALUES (2,1,\'Graphics\',\'Photo manipulation and drawing\')\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"INSERT INTO product (productid, name, description, price)

VALUES (1, \'Aladobe Photonshop\', \'A great program for photon editing\', 35)\";

 

$result = @mysql_query(\"$sql\",$db);

 

$sql = \"INSERT INTO productcategory (productid, categoryid) VALUES (1,2)\";

 

$result = @mysql_query(\"$sql\",$db);

 

header(\"Location: index.php\");

?>

[shivabharat]

Do u want to separate the mysql commands ??? :roll:

 

Create a database and then execute these commands

 

u can actually save this file in .sql and execute in mysql directly

 

If u have more doubts do let us know

 

CREATE TABLE customer ( 

firstname varchar(50) not null, 

lastname varchar(50) not null, 

email varchar(50) not null, 

address varchar(50), 

zip varchar(10), 

city varchar(30), 

phone varchar(20), 

customerid int4 not null auto_increment, 

orderid int auto_increment not null, 

PRIMARY KEY (customerid), 

UNIQUE email (email) 

)





CREATE TABLE affiliate ( 

user varchar(50) not null, 

password varchar(50) not null, 

firstname varchar(50) not null, 

lastname varchar(50) not null, 

email varchar(50) not null, 

address varchar(50), 

zip varchar(10), 

city varchar(30), 

url varchar(50) not null, 

phone varchar(20), 

payed float(6,2), 

affiliateid int not null auto_increment, 

PRIMARY KEY (affiliateid, user), 

UNIQUE email (email) 

)





CREATE TABLE user ( 

user varchar(50) not null, 

password varchar(50) not null, 

usertype int not null, 

PRIMARY KEY (user) 

)





CREATE TABLE orders ( 

customerid int not null, 

orderid int not null auto_increment, 

products varchar(100) not null, 

date varchar(30) not null, 

price varchar(15) not null, 

paymentmethod varchar(15) not null, 

ip varchar(15) not null, 

PRIMARY KEY (orderid) 

)



CREATE TABLE orderaffiliate ( 

affiliateid int not null, 

orderid int not null, 

PRIMARY KEY (orderid) 

)



CREATE TABLE affiliatestats ( 

clicks int, 

orders int, 

affiliateid int not null, 

PRIMARY KEY (affiliateid) 

)





CREATE TABLE category ( 

categoryid int auto_increment not null, 

parentcategoryid int not null, 

name varchar(25) not null, 

description varchar(255) not null, 

PRIMARY KEY (categoryid), 

INDEX parentcategoryid (parentcategoryid), 

INDEX name (name) 

)



CREATE TABLE product ( 

productid int auto_increment not null, 

name varchar(50) not null, 

description varchar(255) not null, 

price float(5,2) not null, 

PRIMARY KEY (productid), 

INDEX namn (name) 

)



CREATE TABLE productcategory ( 

productid int not null, 

categoryid int not null, 

PRIMARY KEY (productid, categoryid) 

)



CREATE TABLE manufacturer ( 

manufacturerid int not null, 

company varchar(30), 

firstname varchar(30) not null, 

lastname varchar(30) not null, 

email varchar(50) not null, 

address varchar(50), 

zip varchar(10), 

city varchar(30), 

phone varchar(20), 

PRIMARY KEY (manufacturerid) 

)



CREATE TABLE productmanufacturer ( 

productid int not null, 

manufacturerid int not null, 

PRIMARY KEY (productid, manufacturerid) 

)



INSERT INTO affiliate VALUES(\'test\',\'test\',\'Test\',\'Testman\', 

\'test@test.com\',\'Street\',\'23455\',\'City\',\'http://www.yahoo.com\',\'055-55555\',0,1111);



INSERT INTO affiliatestats (affiliateid, clicks, orders) VALUES (1111,0,0);



INSERT INTO category (categoryid, parentcategoryid, name, description) VALUES (1,1,\'Software\',\'Computer software\');  



INSERT INTO category (categoryid, parentcategoryid, name, description) VALUES (2,1,\'Graphics\',\'Photo manipulation and drawing\'); 



INSERT INTO product (productid, name, description, price) 

VALUES (1, \'Aladobe Photonshop\', \'A great program for photon editing\', 35); 



INSERT INTO productcategory (productid, categoryid) VALUES (1,2); 

[ray-solomon]

This error message was sent back when I sent it as a query in mysqlman.

 

MySQL said: You have an error in your SQL syntax near \'CREATE TABLE affiliate ( user varchar(50) not null, passwo\' at line 1.

 

I\'m lost.

[shivabharat]

I dont get any error now are using MYSQL or msql or Microsoft SQL ??