Database Setup
Creating A Start-up File
The first thing to do is create a MySQL start-up file...
This file contains settings for the MySQL user interface program you run at the Unix command line. In particular it contains your password. This is a convenience and a security measure. It removes the need to type in your password every time you login, and hides your password from basic hacks.
It can also contain the name of a default database. Although you can create any number of different databases, it easier to put everything in one. If you don't set a default, you'll need to type: 'use database;' every time you start a session.
If you want to know what other options it can contain, type 'mysql --help' at the unix prompt.
When your webhosting account was created, a database called something like 'yourdbname' should have been created automatically. Your welcome email should contain details on how to access your database. If not - contact your webhost.
Open a telnet session to your webhost.
at the unix prompt, type:
cd
cat >.my.cnf
[client]
password=yourpassword
database=yourdbname
Then press ctrl-d to close the file
If your DB user name is different from your unix shell (login) name - you need to specify the username on the command line when you start the mysql program like this:
mysql -u dbusername
You could put it into an alias in your .bashrc (startup) file:
alias sql='mysql -u dbusername'
Creating Your Database Tables
If you want, you can modify the example definitions below, or just use them as they are...
Now start the MySQL UI (user-interface), you just need to type 'mysql' at the Unix prompt:
[you@self yourname]$ mysql
It'll welcome you, then you should get the mysql prompt:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 47948 to server version: 3.23.45
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
If you didn't set it up above, you now need to tell mysql which database you want to access.
mysql> use yourname;
The database table definitions below are written in SQL. When the mysql program encounters them, it will create the tables, exactly as they are defined. You can use them as they are, or edit them to your own specifications.
To create the database tables, simply cut and paste your database table definitions into the telnet window. The database will tell you if the create was successful or not.
A Successful create will get a message like:
Query OK, 0 rows affected (0.00 sec)
A failure looks like this:
ERROR 1050: Table 'hits' already exists
CREATE TABLE hits (
page VARCHAR(30) NOT NULL,
counter INT UNSIGNED,
PRIMARY KEY (page)
);
CREATE TABLE downloads (
file VARCHAR(30) NOT NULL,
counter INT UNSIGNED,
PRIMARY KEY (file)
);
For PayPal Integration - you should use this 'orders' table:
CREATE TABLE psales (
invoice INT UNSIGNED AUTO_INCREMENT,
receiver_email VARCHAR(60),
item_name VARCHAR(100),
item_number VARCHAR(10),
quantity VARCHAR(6),
payment_status VARCHAR(10),
pending_reason VARCHAR(10),
payment_date VARCHAR(20),
mc_gross VARCHAR(20),
mc_fee VARCHAR(20),
mc_currency VARCHAR(3),
txn_id VARCHAR(20),
txn_type VARCHAR(10),
first_name VARCHAR(30),
last_name VARCHAR(40),
address_street VARCHAR(50),
address_city VARCHAR(30),
address_state VARCHAR(30),
address_zip VARCHAR(20),
address_country VARCHAR(30),
address_status VARCHAR(10),
payer_email VARCHAR(60),
payer_status VARCHAR(10),
payment_type VARCHAR(10),
notify_version VARCHAR(10),
verify_sign VARCHAR(10),
referrer VARCHAR(10),
PRIMARY KEY (invoice)
);
If you are processing orders in another manner, use an 'orders' table like this:
CREATE TABLE orders (
counter INT UNSIGNED NOT NULL,
tstamp TIMESTAMP,
order_id VARCHAR(25),
prod_id VARCHAR(10),
name VARCHAR(60),
addr1 VARCHAR(60),
addr2 VARCHAR(60),
city VARCHAR(40),
state VARCHAR(20),
zip VARCHAR(12),
country VARCHAR(50),
phone VARCHAR(30),
email VARCHAR(60),
subscribe CHAR(1) DEFAULT 'N',
price VARCHAR(15),
PRIMARY KEY (counter)
);
A products table is only necessary if you have lots of products:
CREATE TABLE products (
prod_id VARCHAR(10) NOT NULL,
name VARCHAR(100),
descr VARCHAR(255),
price DECIMAL(10,2),
filename VARCHAR(30),
size VARCHAR(10),
web page VARCHAR(30),
image VARCHAR(30),
password VARCHAR(10),
type CHAR(1) DEFAULT 'A',
status CHAR(1) DEFAULT 'A',
PRIMARY KEY (prod_id)
);
NOTES: - The downloads table is not required if you don't want to track downloads.
- NOT NULL means - this column cannot be blank - it's mandatory.
- 'price' in the products table is a DECIMAL of 10 digits, the last 2 of which are after the decimal point... You can have products which cost up to $99999999.99
- The orders table has a 'price' column to allow for additional costs such as delivery or taxes. Like everything, if you don't need it, you can change it.
- 'counter' is your order number, 'order_id' is the order id from your credit card processor.
- You can have DEFAULT values for columns. If you don't specify a value for that column when you insert a row, the default value is used.
- When you insert a row, you only have to supply the NOT NULL columns.
|
If you want to have a separate customers and orders structure, or an ordering system that allows many items per order, the tables will have to be different. You will have to ensure that whichever structure you choose is appropriate for you, and you understand it. You'll also have to change the code to deal with these changes in database structure.
The following examples may be appropriate for your website, if not, you can easily copy & modify them:
Separate orders and customers tables:
CREATE TABLE orders (
counter INT UNSIGNED NOT NULL,
tstamp TIMESTAMP,
order_id VARCHAR(25),
prod_id VARCHAR(10),
cust_id VARCHAR(60),
price VARCHAR(15),
PRIMARY KEY (counter)
);
CREATE TABLE customers (
cust_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(60),
addr1 VARCHAR(60),
addr2 VARCHAR(60),
city VARCHAR(40),
state VARCHAR(20),
zip VARCHAR(12),
country VARCHAR(50),
phone VARCHAR(30),
email VARCHAR(60),
subscribe CHAR(1) DEFAULT 'N',
PRIMARY KEY (cust_id)
);
Notes:
- AUTO_INCREMENT automatically increments a count and puts that number into the column.
If you want a shopping cart - you need to be able to have many items per order. The tables for that now separate the order 'header' from the order 'items':
CREATE TABLE order_header (
counter INT UNSIGNED NOT NULL,
tstamp TIMESTAMP,
order_id VARCHAR(25),
cust_id VARCHAR(60),
total_price VARCHAR(15),
PRIMARY KEY (counter)
);
CREATE TABLE order_body (
counter INT UNSIGNED NOT NULL,
item INT UNSIGNED NOT NULL AUTO_INCREMENT,
tstamp TIMESTAMP,
order_id2 VARCHAR(25),
prod_id VARCHAR(10),
cust_id VARCHAR(60),
price VARCHAR(15),
PRIMARY KEY (counter, item)
);
Notes:
- Orders now are split into head and body. The head has all the data that's true for the whole order, the body contains details on each item.
- The primary key index on order_body contains two columns because it's only in combination that they're guaranteed unique!
Adding Your Data
The following example insert statement can be used for adding data to your products table. You'll need to:
- Create your own 'insert' script (a new file on your PC). You could call it 'populate.sql' as it's a SQL script for populating the database with data.
- Copy and Paste the example once for each product you want to create.
- Add your data into the 'values' section.
- Once complete, cut and paste the whole thing - into mysql.
INSERT INTO products (prod_id, name, descr, price, filename, size, web page, image, password)
VALUES ("prod_id", "Name", "Description", 0.00, "filename", "sizeKb", "web page.html", "image.gif", "password");
Once your population script is complete, you can run it at the mysql prompt.
This completes the database setup for now. In later lessons you'll be able to create additional tables for unique hit counters, users and affiliates.
|
|
