The Tables for the Shopping Basket

 

Introduction.  Now we need to create two tables that will be used as the source for each shopper's basket.  The tables have been introduced previously on the table background webpage.  We will be needing two tables.
  • basket
  • basket_item

The basket table will contain information about the entire basket as a whole.  The basket_item table will contain information about each item in the basket.  How the basket_items are related to their particular basket will be through the use of a key.

First we need to create the basket table.  We again display what was presented earlier for ease of reference.

 

basket
Key Field Data Type Allow Nulls
id_basket int no
  order_placed int yes
  sub_total float yes
  total float yes
  cost_shipping float yes
  tax float yes
  date_created datetime yes

 

The first PHP should be called create_basket.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 = "basket";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_basket MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
order_placed SMALLINT,
sub_total FLOAT,
total FLOAT,
cost_shipping FLOAT,
tax FLOAT,
date_created 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>
 

 

Now we need to create the basket_item table.  The following table structure represents what is needed in the table.

 

basket_item
Key Field Data Type Allow Nulls
id_basket_item int no
  id_product int yes
  price float yes
  weight float yes
  product_name varchar yes
  item_quantity int yes
  id_basket int yes
  attribute1 varchar yes
  attribute2 varchar yes

 

The first PHP should be called create_basket_item.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 = "basket_item";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_basket_item MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_product MEDIUMINT,
price FLOAT,
weight FLOAT,
product_name VARCHAR(50),
item_quantity MEDIUMINT,
id_basket MEDIUMINT,
attribute1 VARCHAR(50),
attribute2 VARCHAR(50))";

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>

 

We have chosen the field names and sizes to duplicate those in other tables when appropriate.