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

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