OsCommerce

This article is a stub. You can help HypertWiki by expanding it.

Editing is currently in progress on this article. Although editing is incomplete, the author or editor has saved their work to prevent loss. Please check back later by reloading the page, and do not edit while this message is still showing. Thank you.

Techniques: osCommerce

osCommerce is open source software for managing an online store. It is written in PHP and uses the MySQL database engine.

The following documentation is from notes I took while evaluating osCommerce for use at vbz.net. I ended up deciding that it would not be suitable, but could provide useful examples for how to do things using PHP and MySQL.

Tables

address_book

address_format

banners

banners_id# -- integer - not null, auto inc
banners_title -- varchar(64) - not null
banners_url -- varchar(255) - not null
banners_image -- varchar(64) - not null
banners_group -- varchar(10) - not null
banners_html_text -- text
expires_impressions -- int(7) - default 0
expires_date -- datetime
date_scheduled -- datetime
date_added -- datetime - not null, default 0000-00-00 00:00:00
date_status_change -- datetime
status -- int(1) - not null, default 1

banners_history

banners_history_id# -- integer - not null, auto inc
banners_id -- integer - not null, default 0
banners_shown -- int(5) - not null, default 0
banners_clicked -- int(5) - not null, default 0
banners_history_date -- datetime - not null, default 0000-00-00 00:00:00

categories_description

categories_id# -- integer - not null, default 0
language_id# -- integer - not null, default 1
categories_name -- varchar(32) - not null

configuration

configuration_id# -- integer - not null, auto inc
configuration_title -- varchar(64) - not null
configuration_key -- varchar(64) - not null
configuration_value -- varchar(255) - not null
configuration_description -- varchar(255) - not null
configuration_group_id -- integer - not null, default 0
sort_order -- int(5)
last_modified -- datetime
date_added -- datetime - not null, default 0000-00-00 00:00:00
use_function -- varchar(255)
set_function -- varchar(255)

configuration_group

configuration_group_id# -- integer - not null, auto inc
configuration_group_title -- varchar(64) - not null
configuration_group_description -- varchar(255) - not null
sort_order -- int(5)
visible -- int(1) - default 1

counter

startdate -- char(8)
counter -- int(12)

counter_history

month -- char(8)
counter -- int(12)

countries

countries_id# -- integer - not null, auto inc
countries_name -- varchar(64) - not null
countries_iso_code_2 -- char(2)
countries_iso_code_3 -- char(3)
address_format_id -- integer - not null, default 0

currencies

currencies_id# -- integer - not null, auto inc
title -- varchar(32) - not null
code -- char(3) - not null
symbol_left -- varchar(12)
symbol_right -- varchar(12)
decimal_point -- char(1)
thousands_point -- char(1)
decimal_places -- char(1)
value -- float(13,8)
last_updated -- datetime

customers

customers_id# -- integer - not null, auto inc
customers_gender -- char(1) - not null
customers_firstname -- varchar(32) - not null
customers_lastname -- varchar(32) - not null
customers_dob -- datetime - not null, default 0000-00-00 00:00:00
customers_email_address -- varchar(96) - not null
customers_default_address_id -- integer - not null, default 0
customers_telephone -- varchar(32) - not null
customers_fax -- varchar(32)
customers_password -- varchar(40) - not null
customers_newsletter -- char(1)

customers_basket

customers_basket_id# -- integer - not null, auto inc
customers_id -- integer - not null, default 0
products_id -- tinytext - not null
customers_basket_quantity -- int(2) - not null, default 0
final_price -- decimal(15,4) - not null, default 0.0000
customers_basket_date_added -- varchar(8)

customers_basket_attributes

customers_basket_attributes_id# -- integer - not null, auto inc
customers_id -- integer - not null, default 0
products_id -- tinytext - not null
products_options_id -- integer - not null, default 0
products_options_value_id -- integer - not null, default 0

customers_info

customers_info_id# -- integer - not null, default 0
customers_info_date_of_last_logon -- datetime
customers_info_number_of_logons -- int(5)
customers_info_date_account_created -- datetime
customers_info_date_account_last_modified -- datetime
global_product_notifications -- int(1) - default 0

geo_zones

geo_zone_id# -- integer - not null, auto inc
geo_zone_name -- varchar(32) - not null
geo_zone_description -- varchar(255) - not null
last_modified -- datetime
date_added -- datetime - not null

languages

languages_id# -- integer - not null, auto inc
name -- varchar(32) - not null
code -- char(2) - not null
image -- varchar(64)
directory -- varchar(32)
sort_order -- int(3)

manufacturers

manufacturers_id# -- integer - not null, auto inc
manufacturers_name -- varchar(32) - not null
manufacturers_image -- varchar(64)
date_added -- datetime
last_modified -- datetime

manufacturers_info

manufacturers_id# -- integer - not null, default 0
languages_id# -- integer - not null, default 0
manufacturers_url -- varchar(255) - not null
url_clicked -- int(5) - not null, default 0
date_last_click -- datetime

newsletters

newsletters_id# -- integer - not null, auto inc
title -- varchar(255) - not null
content -- text - not null
module -- varchar(255) - not null
date_added -- datetime - not null, default 0000-00-00 00:00:00
date_sent -- datetime
status -- int(1)
locked -- int(1) - default 0

orders

orders_id# -- integer - not null, auto inc
customers_id -- integer - not null, default 0
customers_name -- varchar(64) - not null
customers_company -- varchar(32)
customers_street_address -- varchar(64) - not null
customers_suburb -- varchar(32)
customers_city -- varchar(32) - not null
customers_postcode -- varchar(10) - not null
customers_state -- varchar(32)
customers_country -- varchar(32) - not null
customers_telephone -- varchar(32) - not null
customers_email_address -- varchar(96) - not null
customers_address_format_id -- int(5) - not null, default 0
delivery_name -- varchar(64) - not null
delivery_company -- varchar(32)
delivery_street_address -- varchar(64) - not null
delivery_suburb -- varchar(32)
delivery_city -- varchar(32) - not null
delivery_postcode -- varchar(10) - not null
delivery_state -- varchar(32)
delivery_country -- varchar(32) - not null
delivery_address_format_id -- int(5) - not null, default 0
billing_name -- varchar(64) - not null
billing_company -- varchar(32)
billing_street_address -- varchar(64) - not null
billing_suburb -- varchar(32)
billing_city -- varchar(32) - not null
billing_postcode -- varchar(10) - not null
billing_state -- varchar(32)
billing_country -- varchar(32) - not null
billing_address_format_id -- int(5) - not null, default 0
payment_method -- varchar(32) - not null
cc_type -- varchar(20)
cc_owner -- varchar(64)
cc_number -- varchar(32)
cc_expires -- varchar(4)
last_modified -- datetime
date_purchased -- datetime
orders_status -- int(5) - not null
orders_date_finished -- datetime
currency -- char(3)
currency_value -- decimal(14,6)

orders_products

orders_products_id# -- integer - not null, auto inc
orders_id -- integer - not null, default 0
products_id -- integer - not null, default 0
products_model -- varchar(12)
products_name -- varchar(64) - not null
products_price -- decimal(15,4) - not null, default 0.0000
final_price -- decimal(15,4) - not null, default 0.0000
products_tax -- decimal(7,4) - not null, default 0.0000
products_quantity -- int(2) - not null, default 0

orders_products_attributes

orders_products_attributes_id# -- integer - not null, auto inc
orders_id -- integer - not null, default 0
orders_products_id -- integer - not null, default 0
products_options -- varchar(32) - not null
products_options_values -- varchar(32) - not null
options_values_price -- decimal(15,4) - not null, default 0.0000
price_prefix -- char(1) - not null

orders_products_download

orders_products_download_id# -- integer - not null, auto inc
orders_id -- integer - not null, default 0
orders_products_id -- integer - not null, default 0
orders_products_filename -- varchar(255) - not null
download_maxdays -- int(2) - not null, default 0
download_count -- int(2) - not null, default 0

orders_status

orders_status_id# -- integer - not null, default 0
language_id -- integer - not null, default 1
orders_status_name -- varchar(32) - not null

orders_status_history

orders_status_history_id# -- integer - not null, auto inc
orders_id -- integer - not null, default 0
orders_status_id -- int(5) - not null, default 0
date_added -- datetime - not null, default 0000-00-00 00:00:00
customer_notified -- int(1) - default 0
comments -- text

orders_total

orders_total_id# -- integer unsigned - not null, auto inc
orders_id -- integer - not null, default 0
title -- varchar(255) - not null
text -- varchar(255) - not null
value -- decimal(15,4) - not null, default 0.0000
class -- varchar(32) - not null
sort_order -- integer - not null, default 0

products

products_id# -- integer - not null, auto inc
products_quantity -- int(4) - not null, default 0
products_model -- varchar(12)
products_image -- varchar(64)
products_price -- decimal(15,4) - not null, default 0.0000
products_date_added -- datetime - not null, default 0000-00-00 00:00:00
products_last_modified -- datetime
products_date_available -- datetime
products_weight -- decimal(5,2) - not null, default 0.00
products_status -- tinyint(1) - not null, default 0
products_tax_class_id -- integer - not null, default 0
manufacturers_id -- integer
products_ordered - integer - not null, default 0

products_attributes

products_attributes_id# -- integer - not null, auto inc
products_id -- integer - not null, default 0
options_id -- integer - not null, default 0
options_values_id -- integer - not null, default 0
options_values_price -- decimal - not null, default 0.0000
price_prefix -- char(1) - not null

products_attributes_download

products_attributes_id# -- integer - not null, default 0
products_attributes_filename -- varchar(255) - not null
products_attributes_maxdays -- int(2) - default 0
products_attributes_maxcount -- int(2) - default 0

products_description

products_id# -- integer - not null, auto inc
language_id# -- integer - not null, default 1
products_name -- varchar(64) - not null
products_description -- text
products_url -- varchar(255)
products_viewed -- int(5) - default 0

products_notifications

products_id# -- integer - not null, default 0
customers_id# -- integer - not null, default 0
date_added -- datetime - not null, default 0000-00-00 00:00:00

products_options

products_options_id# -- integer - not null, default 0
language_id# -- integer -- not null, default 1
products_options_name -- varchar(32) - not null

products_options_values

products_options_values_id# -- integer - not null, default 0
language_id# -- integer - not null, default 1
products_options_values_name -- varchar(64) - not null

products_options_values_to_products_options

products_options_values_to_products_options_id# -- integer - not null, auto inc
products_options_id -- integer - not null, default 0
products_options_values_id -- integer - not null, default 0

products_to_categories

products_id# -- integer - not null, defaul 0
categories_id# -- integer - not null, default 0

reviews

reviews_id# -- integer - not null, auto inc
products_id -- integer - not null, default 0
customers_id -- integer
customers_name -- varchar(64) - not null
reviews_rating -- int(1)
date_added -- datetime
last_modified -- datetime
reviews_read -- int(5) - not null, default 0

reviews_description

reviews_id# -- integer - not null, default 0
languages_id# -- integer - not null, default 0
reviews_text -- text - not null

sessions

sesskey# -- varchar(32) - not null
expiry -- integer unsigned - not null, default 0
value -- text - not null

specials

specials_id# -- integer - not null, auto inc
products_id -- integer - not null, default 0
specials_new_products_price - decimal(15,4) - not null, default 0.0000
specials_date_added -- datetime
specials_last_modified -- datetime
expires_date -- datetime
date_status_change -- datetime
status -- int(1) - not null

tax_class

tax_class_id# -- integer - not null, auto inc
tax_class_title -- varchar(32) - not null
tax_class_description -- varchar(255) - not null
last_modified -- datetime
date_added -- datetime - not null, default 0000-00-00 00:00:00

tax_rates

tax_rates_id# -- integer - not null, auto inc
tax_zone_id -- integer - not null, default 0
tax_class_id -- integer - not null, default 0
tax_priority -- int(5) - default 1
tax_rate -- decimal(7,4) - not null, default 0.0000
tax_description -- varchar(255) - not null
last_modified -- datetime
date_added -- datetime - not null, default 0000-00-00 00:00:00

whos_online

customer_id -- integer
full_name -- varchar(34) - not null
session_id -- varchar(128) - not null
ip_address -- varchar(15) - not null
time_entry -- varchar(14) - not null
time_last_click -- varchar(14) - not null
last_page_url -- varchar(34) - not null

zones

zone_id# -- integer - not null, autoinc
zone_country_id -- integer - not null, default 0
zone_code -- varchar(32) - not null
zone_name -- varchar(32) - not null

zones_to_geo_zones

association_id# - integer - not null, autoinc
zone_country_id - integer - not null
zone_id - integer
geo_zone_id - integer
last_modified - datetime
date_added - datetime - not null