Jump to content


Photo

php/mysql


  • Please log in to reply
3 replies to this topic

#1 ray-solomon

ray-solomon
  • Members
  • Pip
  • Newbie
  • 2 posts

Posted 30 May 2003 - 04:15 PM

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\");
?>

#2 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 30 May 2003 - 08:45 PM

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

Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#3 ray-solomon

ray-solomon
  • Members
  • Pip
  • Newbie
  • 2 posts

Posted 30 May 2003 - 10:17 PM

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.

#4 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 30 May 2003 - 11:01 PM

I dont get any error now are using MYSQL or msql or Microsoft SQL ??
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users