Creating and Initializing the Tables for the Checkout Process

 

Introduction.  We need to create some tables that will help us during our checkout process.  We need three tables.
  • order_data
    • contains the information on the particular order
      • id_basket
      • billing information
      • shipping information
  • shipping
    • contains information on how to compute shipping costs by weight
  • taxes_state
    • contains information about tax rates when shipping to a particular state

We will also need to initialize the taxes_state and shipping tables.  The order_data will be augmented for each order.

We repeat the basic structure from earlier in hopes that it will enhance your understanding of the code.

 

order_data
Key Field Data Type Allow Nulls
id_order int no
  id_basket int yes
  email varchar yes
  bill_first_name varchar yes
  bill_last_name varchar yes
  bill_address varchar yes
  bill_city varchar yes
  bill_state varchar yes
  bill_zipcode varchar yes
  bill_phone varchar yes
  bill_fax varchar yes
  ship_first_name varchar yes
  ship_last_name varchar yes
  ship_address varchar yes
  ship_city varchar yes
  ship_state varchar yes
  ship_zipcode varchar yes
  ship_phone varchar yes
  ship_fax varchar yes
  card_type varchar yes
  card_number varchar yes
  expiration_month varchar yes
  expiration_year varchar yes
  card_name varchar yes
  date_ordered datetime yes

 

First we will create the order_data table.  While it has the greatest number of fields and is designed to grow by one record for each order that is placed, it doesn't require any special initializations from us.  You should follow the usual procedures for creating tables and copy, upload and execute this PHP code one time only and then delete it from the server.  You should call this file create_order_data_table.php.

 

<html>
<head>
<title>Creating a Table in MySQL Using PHP</title>
</head>

<body>
<?php
// assign the values for database access
$host = "localhost";
$user = "yourlastnameweb";
$password = "yourpassword";
$db_name = "yourlastnameweb";
$table_name = "order_data";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_order MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_basket MEDIUMINT,
email VARCHAR(50),
bill_first_name VARCHAR(20),
bill_last_name VARCHAR(30),
bill_address VARCHAR(50),
bill_city VARCHAR(30),
bill_state VARCHAR(20),
bill_zipcode VARCHAR(10),
bill_phone VARCHAR(15),
bill_fax VARCHAR(15),
ship_first_name VARCHAR(20),
ship_last_name VARCHAR(30),
ship_address VARCHAR(50),
ship_city VARCHAR(30),
ship_state VARCHAR(20),
ship_zipcode VARCHAR(10),
ship_phone VARCHAR(15),
ship_fax VARCHAR(15),
card_type VARCHAR(15),
card_number VARCHAR(20),
expiration_month VARCHAR(12),
expiration_year VARCHAR(6),
card_name VARCHAR(50),
date_ordered datetime)";

if (mysql_db_query($db_name, $query_string, $link))
{
print ("The query was successfully created<BR>");
}
else
{
print ("The query could not be created<BR>");
}

mysql_close($link);
?>
</body>
</html>

 

The next table we create will be the table for shipping.  We first repeat the basic structure.

 

shipping
Key Field Data Type Allow Nulls
id_ship_interval int no
  lower_limit float yes
  upper_limit float yes
  shipping_cost float yes

 

You should call this file create_shipping_table.php.

 

<html>
<head>
<title>Creating a Table in MySQL Using PHP</title>
</head>

<body>
<?php
// assign the values for database access
$host = "localhost";
$user = "yourlastnameweb";
$password = "yourpassword";
$db_name = "yourlastnameweb";
$table_name = "shipping";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_ship_interval MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
lower_limit FLOAT,
upper_limit FLOAT,
shipping_cost FLOAT)";

if (mysql_db_query($db_name, $query_string, $link))
{
print ("The query was successfully created<BR>");
}
else
{
print ("The query could not be created<BR>");
}

mysql_close($link);
?>
</body>
</html>

 

This table needs to be initialized like the following.

 

id_ship_interval lower_limit upper_limit shipping_cost
1 0.00 1.99 5.00
2 2.00 3.99 6.00
3 4.00 5.99 7.00
4 6.00 7.99 8.00
5 8.00 9.99 9.00

 

Once the weight of an order gets larger than the maximum weight listed in the table the formula to compute the shipping costs changes to the following.

shipping_cost = 5.00 + (0.5 * total_weight)

This won't be much different than what is contained in the table.

Finally, we need to create the taxes_state table.  It should have the following structure.

 

taxes_state
Key Field Data Type Allow Nulls
id_state int no
  state_name varchar yes
  state_abbreviation varchar yes
  tax_rate float yes

 

You should call this file create_taxes_state_table.php.

 

<html>
<head>
<title>Creating a Table in MySQL Using PHP</title>
</head>

<body>
<?php
// assign the values for database access
$host = "localhost";
$user = "yourlastnameweb";
$password = "yourpassword";
$db_name = "yourlastnameweb";
$table_name = "taxes_state";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_state MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
state_name VARCHAR(20),
state_abbreviation VARCHAR(4),
tax_rate FLOAT)";

if (mysql_db_query($db_name, $query_string, $link))
{
print ("The query was successfully created<BR>");
}
else
{
print ("The query could not be created<BR>");
}

mysql_close($link);
?>
</body>
</html>

 

The initialization for the table should look like the following.  Because there are 50 states, this gets quite long, but it is appropriate.  Notice that I have left most of the tax rates at 0.00 since I don't really know them!  You should also notice that the state names do not have any spaces in them so that they match with the "values" in the sel_ship_state in the forms.

 

id_state state_name state_abbreviation tax_rate
1 Alabama AL 0.00
2 Alaska AK 0.00
3 Arizona AZ 0.00
4 Arkansas AR 0.00
5 California CA 0.00
6 Colorado CO 0.00
7 Connecticut CT 0.06
8 Delaware DE 0.00
9 Florida FL 0.00
10 Georgia GA 0.00
11 Hawaii HI 0.00
12 Idaho ID 0.00
13 Illinois IL 0.00
14 Indiana IN 0.00
15 Iowa IA 0.00
16 Kansas KS 0.00
17 Kentucky KY 0.00
18 Louisiana LA 0.00
19 Maine ME 0.00
20 Maryland MD 0.00
21 Massachusetts MA 0.00
22 Michigan MI 0.00
23 Minnesota MN 0.00
24 Mississippi MS 0.00
25 Missouri MO 0.00
26 Montana MT 0.00
27 Nebraska NE 0.00
28 Nevada NV 0.00
29 NewHampshire NH 0.00
30 NewJersey NJ 0.00
31 NewMexico NM 0.00
32 NewYork NY 0.07
33 NorthCarolina NC 0.00
34 NorthDakota ND 0.00
35 Ohio OH 0.00
36 Oklahoma OK 0.00
37 Oregon OR 0.00
38 Pennsylvania PA 0.00
39 RhodeIsland RI 0.00
40 SouthCarolina SC 0.00
41 SouthDakota SD 0.00
42 Tennessee TN 0.00
43 Texas TX 0.00
44 Utah UT 0.00
45 Vermont VT 0.00
46 Virginia VA 0.05
47 Washington WA 0.00
48 WestVirginia WV 0.00
49 Wisconsin WI 0.00
50 Wyoming WY 0.00