*******************Important URL for Coding ***********************
http://de.tikiwiki.org/xref-BRANCH-1-9/nav.html?lang/ca/language.php.source.html
********************************************************
Creating the Database
Let's assume that we're running a website that sells Sony Playstation 2 games. We'll need one table to store the details of each product, and another table to store the contents of each user's shopping cart, so that these details can persist over multiple sessions.
Fire up the MySQL console application and create a database named cart. Populate the database with two tables: items and cart, using this code:
create database cart;
create table items
(
itemId int auto_increment not null,
itemName varchar(50),
itemDesc varchar(250),
itemPrice decimal(4,2),
primary key(itemId),
unique id(itemId)
);
create table cart
(
cartId int auto_increment not null,
cookieId varchar(50),
itemId int,
qty int,
primary key(cartId),
unique id(cartId)
);
The first table, items, will contain a list of items that the user will be able to add to her cart. The items table contains four fields, as described below:
* itemId: A unique numeric identifier that labels each item with its own ID.
* itemName: The name of the item in the catalog.
* itemDesc: A short description of the item in the catalog.
* itemPrice: The price of the item, such as 45.99.
The cart table will store the details of each item in the users cart as she adds them. The cart table also contains four fields, which are described below:
* cartId: A unique numeric identifier that labels each item in the user's cart with an ID.
* cookieId: This is the most important field in both of the tables. It is used to persist the user's cart over multiple sessions. It is the value of the session ID with which the user first started browsing the range of products.
* itemId: The ID of the item that the user is purchasing.
* qty: The number (quantity) of this specific item being purchased.
Displaying the Items
Displaying the list of items from our items table is fairly easy. We'll display the items for sale on one page, and display the shopping cart on another page. We'll also create a file that will store the connection details of the database—along with two functions that will allow us to connect to and work with the database. Create a file called db.php (or open this file from the folder of sample files that you downloaded on the first page of this tutorial) and enter the following code into it:
// This page contains the connection routine for the
// database as well as getting the ID of the cart, etc.
$dbServer = "localhost";
$dbUser = "admin";
$dbPass = "password";
$dbName = "cart";
function ConnectToDb($server, $user, $pass, $database)
{
// Connect to the database and return
// true/false depending on whether or
// not a connection could be made.
$s = @mysql_connect($server, $user, $pass);
$d = @mysql_select_db($database, $s);
if(!$s || !$d)
return false;
else
return true;
}
function GetCartId()
{
// This function will generate an encrypted string and
// will set it as a cookie using set_cookie. This will
// also be used as the cookieId field in the cart table
if(isset($_COOKIE["cartId"]))
{
return $_COOKIE["cartId"];
}
else
{
// There is no cookie set. We will set the cookie
// and return the value of the users session ID
session_start();
setcookie("cartId", session_id(), time() + ((3600 * 24) * 30));
return session_id();
}
}
?>
First, we'll define four variables to hold the details of the MySQL database's server, username, password, and database name respectively. Next, we'll use the ConnectToDb function, which uses our database variables and connects to the MySQL database:
$s = @mysql_connect($server, $user, $pass);
$d = @mysql_select_db($database, $s);
if(!$s || !$d)
return false;
else
return true;
Notice how the calls to mysql_connect and mysql_select_db are prepended with the @ symbol. The @ symbol tells PHP not to produce any errors if the connect fails. If either of the connection or database selection functions fail, then ConnectToDb returns false. Otherwise it returns true, indicating a successful connection.
The GetCartId function makes use of one cookie variable to track a user across multiple sessions. It starts by checking if the cartId cookie variable is set. If not, it grabs the users session ID and sets it as a cookie value which expires in 30 days.
Note: The expiry date of the setcookie function is specified in seconds, so (3600 * 24) * 30 means 3600 seconds ( 1 hour) * 24 (1 day) * 30 (1 month).
The GetCartId function is used in combination with the MySQL cart table to track which user has added which items to their cart.
Db.php is included by both the item listing page and the cart. The item listing page is called products.php. It begins like this:
// This page will list all of the items
// from the items table. Each item will have
// a link to add it to the cart
include("db.php");
// Get a connection to the database
$cxn = @ConnectToDb($dbServer, $dbUser, $dbPass, $dbName);
$result = mysql_query("select * from items order by itemName asc");
?>
As you can see, we include db.php and call the ConnectToDb function to attain a connection to our MySQL database. Next, we grab the entire list of items from the items table and store the result in the $result variable.
Each item will be displayed as part of a table. We'll output some HTML tags and the beginning row of our table before starting a loop through each item, like this:
while($row = mysql_fetch_array($result))
{
?>
For each item, we'll output its name, price, description, and a link to add it to that unique user's shopping cart:
|
$
|
|
Add Item
|
<<tr>
| Finally, we'll close all the table and HTML tags, and display a link to the shopping cart, called cart.php (we'll create the cart.php file in the next section):
Your Shopping Cart >>
|
