The Table for a Shopper to Maintain a Profile

 

Introduction.  Now we need to create a table that will be used as the source for each shopper's profile.  This table will just be called profile.  Remember we want to have several different types of information about the shopper in the profile to ease the checkout and data mining processes.
  • billing contact information
  • shipping information
  • credit card information

Obviously we could have quite a bit more information about things like shopper and shipping preferences.  We will keep things relatively simple and assume that you could elaborate the information fairly easily since the patterns will be the similar.

Remember, the basic structure we want for the profile table is like that contained in the following tables.

 

profile
Key Field Data Type Allow Nulls
id_profile int no
  email varchar yes
  password 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_fax varchar yes
  bill_phone 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_fax varchar yes
  ship_phone varchar yes
  card_type varchar yes
  card_number varchar yes
  expiration_month varchar yes
  expiration_year varchar yes
  card_name varchar yes
  date_registered datetime yes

 

The first PHP should be called create_profile.php.  It should be uploaded to your firefox directory and executed just once.

 

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

<body>
<?php
// assign the values for database access
$host = "localhost";
$user = "your_user_name";
$password = "your_password";
$db_name = "database_name";
$table_name = "profile";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_profile MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(50),
password VARCHAR(15),
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_registered 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>
 

After you have determined that these tables were all created successfully, you should delete these create_some_table.php files from your firefox directory.  You won't need them anymore.

In order to initialize these tables we could use either MySQL within PHP or use the MySQL Admin interface directly.  But this table won't require any initial inputs, all of its contents will be created by users.