DB_cart classVersion 1.10 from Aug 20, 2005

Author: Olaf Lederer

This universal shopping cart script is powered by MySQL and works with external customer and product data.

Description:

DB_cart Class is a MySQL shopping cart script and can be used with 3rd party product catalogues and membership systems. The whole MySQL database structure is neutral to existing systems. In the current version it's possible to handle the shopping cart (add, update and empty) and checkout process (set the shipment address and e-mail the order). For existing cart users (customers) the items of the cart are still available while until his next visit and are available during checkout. The package includes all application files and example data to test all functions. Since version 1.10 its possible to use this class with product data where the stock value is variable. Notice the additional example files (they are explained at the end).

Features:

Standard class

"on Stock" extenstion

Requirements:

PHP 4, MySQL 3.23 or higher, access to a smtp server (windows only) and some knowledge in OOP

How to use?

First of all, the examples files have all information, you only need to:

Please note that this class is supposed for mature php/MySQL enabled web hosts. It's possible that the script will not work f.e. on free web hosts!.

You can use this class on two ways:

The example files:

In this manual are only the important files explained which are needed to use the class. If you like to try the other examples check the notes inside the files.

db_config.php
There are several constant variables defined, to use this class nothing but the database connection variables have to be changed. If you need to change them read the notes inside the file. The next values are important to modify:

db_cart_example.php
This file get the product information from the example table "db_cart_example_customer" and stores this data in a record set. The data can be used for the presentation on the page. The following information is used later by the class:The product ID, the name and the price.

The next code is optional and gets some customer data from the example table "db_cart_example_customer" and will store the customer number and the e-mail address into a session. You can use other variables for these session variables too.

One of the advantages of this class is the possibility to use product data which doesn't belong to this class. You can handle the data for a product like this:

  1. <?php while ($prod_obj = mysql_fetch_object($prod_result)) { ?>
  2. <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  3. <label for="prod_<?php echo $prod_obj->id; ?>">
  4. <b><?php echo $prod_obj->name; ?></b><br>
  5. <?php echo $prod_obj->description." - price: ".$myCart->format_value($prod_obj->price); ?>
  6. </label>
  7. <input type="text" name="quantity" size="5" value="0">
  8. <input type="hidden" name="price" value="<?php echo $prod_obj->price; ?>">
  9. <input type="hidden" name="art_no" value="<?php echo $prod_obj->art_no; ?>">
  10. <input type="hidden" name="product" value="<?php echo $prod_obj->name; ?>">
  11. <input type="submit" name="submit" value="Add to cart"><br clear="left">
  12. </form>
  13. <?php } ?>

This form is placed inside a loop (row 1 and 13) and will be submitted to the same script (row 2). Notice the presentation part on the rows 3 to 6. It's possible to put here more informations (images, further specs.) if in your datasource is this information available. The input element "quantity" is required and is the only input field in this form (row 7). The rows 8 to 10 holds other informations which are used by the class. At last there is a sbumit button at row 11.

Further you need to create an new instance of the class: $myCart = new db_cart($_SESSION['custom_num']); The session variable is need to use it inside the current order.

If the user add a product to the card the next code is used:

  1. if (isset($_POST['quantity']) && intval($_POST['quantity']) > 0) {
  2.     $myCart->handle_cart_row($_POST['art_no'], $_POST['product'], $_POST['quantity'], $_POST['price']);
  3. }

If the value of the field "quantity" is not empty (row 1) the method handle_cart_row() is called (row 2). Not the $_POST values from the form which are used as attributes for this method.

At the end of the script is a link named "checkout", if this link is used the following code is executed:

  1. if (isset($_GET['action']) && $_GET['action'] == "checkout") {
  2.     if ($myCart->get_number_of_records() > 0) {
  3.         header("Location: ".CHECKOUT);
  4.     } else {
  5.         $myCart->error = "Your cart is currently empty!";
  6.     }
  7. }

First there is a check if there are products in the cart, if yes the user is redirected to the checkout page (row 3) and if not a message is set (row 5).

To show all messages use this code: <?php echo $myCart->error; ?>

The method $myCart->show_ordered_rows() is called for the table with ordered products at the end of the file. How to show order rows is described together with the checkout file next.

db_cart_checkout_example.php
This example file is used for several tasks:

To show dynamicly the orderrows in your own page its possible to store the data in array. This is done while calling this method: $myCheckout->show_ordered_rows() The data is showed with this code:

  1. <table>
  2.   ...// table header here
  3.   <?php foreach ($myCheckout->order_array as $val) { ?>
  4.   <tr>
  5.     <td><?php echo $val['product_id']; ?></td>
  6.     <td><?php echo $val['product_name']; ?></td>
  7.     <td align="right"><?php echo $myCheckout->format_value($val['price']); ?></td>
  8.     <td align="right"><?php echo $myCheckout->format_value($val['price'] * $val['quantity']); ?></td>
  9.     <td>
  10.       <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
  11.         <input type="hidden" name="row_id" value="<?php echo $val['id']; ?>">
  12.         <input type="text" name="quantity" size="5" value="<?php echo $val['quantity']; ?>">
  13.         <input type="submit" name="add" value="Update">
  14.       </form>
  15.     </td>
  16.   </tr>
  17.   <?php } ?>
  18. </table>

The table row with data is placed in a foreach loop (row 3) and all order row values are available in the $myCheckout->order_array. Notice the key names which are used to show the different values (row 5 to 12). Inside the last table cel is a small form with an input field and a submit button. This form is used to update each order row (row 12 and 13). If the new quantity value for a row is null, the row is automaticly deleted.

If the update button is clicked the method $myCheckout->update_row($_POST['row_id'], $_POST['quantity']) is used to update the order row.

If the user clicks on the "Cancel all" links the method $myCheckout->cancel_order() is called and all order rows, the order and the shipement data is removed. The user will be redirected to the product list.

This script shows an example how to obtain customer/shipment data from a third party data source, here from the example table "db_cart_example_customer".

  1. if (!$myCheckout->check_return_shipment()) {
  2.     $cust_conn = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD);
  3.     mysql_select_db(DB_NAME, $cust_conn);
  4.     $cust_sql = sprintf("SELECT name, address, postal_code, place, country FROM db_cart_example_customer WHERE cust_no = %d", $cust_no);
  5.     $cust_result = mysql_query($cust_sql) or die(mysql_error());
  6.     $cust_obj = mysql_fetch_object($cust_result);
  7.     $myCheckout->ship_name = $cust_obj->name;
  8.     $myCheckout->ship_address = $cust_obj->address;
  9.     $myCheckout->ship_pc = $cust_obj->postal_code;
  10.     $myCheckout->ship_city = $cust_obj->place;
  11.     $myCheckout->ship_country = $cust_obj->country;
  12.     mysql_free_result($cust_result);
  13.     $myCheckout->insert_new_shipment();
  14. } else {
  15.     $myCheckout->set_shipment_data();
  16. }

Inside the script there is a check if a shipment record already exists. If not the the example table is used to get this data which will be stored in a record set (row 2 to 5). In the next rows the class variables are filled with the data from the recordset (row 7 to 11). At last the new data will be stored in the shipment table with the method $myCheckout->insert_new_shipment(). If a shipment record already exists the method $myCheckout->set_shipment_data() is called.

If you click on "Order now" or "Continue shopping" the shipmment data is automaticly saved while calling this code:

  1. if (isset($_POST['submit'])) {
  2.     $myCheckout->update_shipment($_POST['name'], $_POST['address'], $_POST['postal_code'], $_POST['place'], $_POST['country'], $_POST['message']);
  3.     if ($_POST['submit'] == "Order now!") {
  4.         $myCheckout->check_out($cust_email);
  5.     } else {
  6.         header("Location: ".PROD_IDX);
  7.     }
  8. }

If the submit value is "Order now!" the order is processed by calling the method $myCheckout->check_out($cust_email) (use an additional form field for the $cust_email variable or use it from the external customer table) otherwise the user is redirected to the product list.

There are two functions at the top of the script, this functions are used to create dynamicly formlelements. If you need more information about this functions check this url: http://www.finalwebsites.com/snippets.php

The values of the total amount and the VAT amount are showed by calling the methods $myCheckout->format_value($myCheckout->show_total_value()) and $myCheckout->format_value($myCheckout->create_total_VAT()). Both values are formatted by the method "format_value()".

db_cart_confirm.php
This file is only used to show the data after the order is mailed to the user and the admin. Use this kind of pages to give more information to the user about what is happen.

Example files "on stock" extension

These example files are extended versions from the examples above. Only the new additional functions and code is explained here. If you need to know somthing about the other code check the parent examples above. First you have to use for all you application files this inluded file:

require($_SERVER['DOCUMENT_ROOT']."/classes/db_cart/db_cart_4stock.php");

Further you have to create objects like this:

$myCart = new db_stock_cart($_SESSION['custom_num']);

db_cart_stock_example.php
The code to obtain the example product data is a little bit different since we need the stock value: $query_new = "SELECT art_no, amount AS on_stock, price, art_descr FROM db_cart_stock_article_example ORDER BY art_no";

The next important code is used to the new stock and price value to test them against existing open order rows for the current customer.

  1. if (!$_SESSION['checked_cart']) {
  2.   $search_in = $myCart->get_order_num_string();
  3.   $query_stock = sprintf("SELECT art_no, amount AS on_stock, price FROM db_cart_stock_article_example WHERE art_no IN (%s) ORDER BY art_no", $search_in);
  4. if ($res_stock = mysql_query($query_stock)) {
  5.     $i = 0;
  6.     while ($stock = mysql_fetch_assoc($res_stock)) {
  7.       $stock_array[$i]['new_stock'] = $stock['on_stock'];
  8.       $stock_array[$i]['new_price'] = $stock['price'];
  9.       $stock_array[$i]['prod_id'] = $stock['art_no'];
  10.       $i++;
  11.     }
  12.     $myCart->update_stock_values($stock_array);
  13.     mysql_free_result($res_stock);
  14.   }
  15. }

In the first row is a check against a session if the check was already done before. If not then the script will buid a string with the article numbers from the existing order rows (row 2). This string uis used to query the product example tabel. Inside the loop (row 6 to 11) the array $stock_array is filled with values from database, don't change the key names of this array!. On row 12 is the method update_stock_values called. This is used to update the existing order rows. Notice the additional code later in the html table will show the stock value from the result set <?php echo $row_new['on_stock']; ?>

db_cart_checkout_stock_example.php
In this field is not so much changed because there is only a check to prevent orders with amount bigger then the stock value and the stock value is listed in the table.

  1. if (isset($_POST['add']) && $_POST['add'] == "Update") {
  2.   if ($myCheckout->check_against_stock($_POST['stock'], $_POST['quantity'])) {
  3.     $myCheckout->update_row($_POST['row_id'], $_POST['quantity']);
  4.   }
  5. }

Notice the addional row no. 2, there is test for the current order amount and the stock value.

Next some code to get the stock values from the example table:

  1. $myCheckout->show_ordered_rows();
  2. $search_in = $myCheckout->get_order_num_string();
  3. $query_stock = sprintf("SELECT art_no, amount AS on_stock FROM db_cart_stock_article_example WHERE art_no IN (%s) ORDER BY art_no", $search_in);
  4. $res_stock = mysql_query($query_stock);
  5. while ($stock = mysql_fetch_assoc($res_stock)) {
  6.   $stock_array[$stock['art_no']] = $stock['on_stock'];
  7. }

There is again the function called to build the string with the article number to search the records from the product example table. Inside the loop is an array build with the article number as key and the stock value as value.

This stock value is placed in order table like this: $stock_array[$val['product_id']];

db_cart_stock_confirm.php
In this file is an additional function to update the stock values after the order is processed:

  1. $myConfirm->set_shipment_data();
  2. $sql_errors = 0;
  3. foreach ($myConfirm->order_array as $val) {
  4.   $update_stock = sprintf("UPDATE db_cart_stock_article_example SET amount = amount - %d, last_buy = NOW() WHERE art_no = '%s'", $val['quantity'], $val['product_id']);
  5.   if (!mysql_query($update_stock)) {
  6.     $sql_errors++;
  7.   }
  8. }
  9. if ($sql_errors == 0) {
  10.   $myConfirm->close_order();
  11. } else {
  12.   $myConfirm->error = $myConfirm->messages(1);
  13. }

First get the shipment information for this order (row 1) then loop through the array with order rows to update the related product row with the new quantity of the product.