Web development tutorial

Free eCommerce MySQL Database

Tutorial created by Team WebAssist

Categories: MySQL

rating

This tutorial includes a free MySQL database with all the necessary database tables and columns for a typical eCommerce website. This is provided for any web developer - novice or advanced - to leverage as a starting point in their production process.

This tutorial also includes steps to import this database (or any .sql file) locally or remotely.

arrow downDownload the database

To accommodate for various features that you might want in an online store, this database comes with a number of pre-configured relationships, shown in the relational tables diagram below. This database contains many tables or columns that may not be necessary in your situation. You only need to use the columns and tables that suit your needs.

Step 1: Download the .sql database file contained in the zip file below to get started.

wa_ecommerce_db-8072013.zip

arrow downSet up your testing server

Before you proceed any further, you will need to have access to a MySQL database server. Most likely, you will want to set up a PHP/MySQL testing environment on your local machine. For more information on setting up a local testing server, view our Windows and Mac tutorials.

arrow downOption 1: Import with phpMyAdmin

phpMyAdmin is commonly used for managing MySQL databases. If you set up your testing server in the previous set of steps, you can use phpMyAdmin to run this script on your local database. If your hosting provider provides phpMyAdmin, you follow these steps for your remote (live) database as well.

Should you want to use another MySQL administrator, skip to the next section.

These steps can actually be used to import any .sql database file, as long as your .sql file has a CREATE TABLE command and you have created an empty MySQL database. If you have an existing MySQL database, just ensure none of the tables in your .sql file are named the same as tables in your existing database.

Step 1: Copy the .sql script

  1. On your computer, navigate to the .sql file you intend to import to your database.
  2. Open the .sql file in a regular text editor such as Notepad or TextEdit.
  3. Select all the content by pressing Ctrl A (Windows) or Cmd A (Mac).
  4. Copy the content by pressing Ctrl C (Windows) or Cmd A (Mac).
  5. Close your text editor application.

Step 2: Execute the .sql script

  1. In phpMyAdmin, choose the database you intend to work with from the Database menu list.
  2. Choose the SQL tab.
  3. Paste the SQL script you copied by pressing Ctrl V (Windows) or Cmd V (Mac).
  4. Choose Go.
  5. Confirm that your new table has been imported by clicking the Structure tab.

arrow downOption 2: Import with another MySQL tool

MySQL Query Browser is part of the MySQL GUI Tools available at http://mysql.com/. These steps can also be used for most other MySQL administrator tools.

Step 1: Run the .sql script

  1. Using the MySQL Query Browser, connect to your MySQL Server.
  2. From the Schemata panel, select the database you intend to add the new database table to.
  3. Choose File > Open Script.
  4. Navigate to the .sql file you wish to import.
  5. Click Open.
  6. Select Execute.
  7. Confirm that the new database table appears in the Schemata panel.

arrow downReviews and comments

Comments will be sent to the author of this tutorial and may not be answered immediately. For general help from WebAssist, please visit technical support.

Sign in to add comments
rating

: 1 Year, 3 Months, 5 Days, 46 Minutes ago

excellent job... it worked a lot...

rating

shootersolutions: 11 Months, 3 Weeks, 2 Hours, 51 Minutes ago

For my test, I have Windows. download the zip file, I unpacked the zip file (no need to, you can stick it on your desktop as-is) and moved the free .sql file as-is.
I use XAMP as I can run UPS Windows server at the same time without rebooting for ecommerce orders going out. (I run a business too.)
XAMP phpAdmin is compatible with this SQL as-is with no modifications.
For simplicity, AFTER starting all three services, I started up "Admin" for "MySQL" (phpMyAdmin on default browser at "localhost")
Click on first column "Databases" and Create a new empty database I called it "test"
Then in the empty database I selected Import file and browsed to the desktop and selected the file I put there.
Then every table in the database is populated into the test database which can be directly edited in phpMyAdmin on my machine.
But the SQL is compatible with my XAMP phpMyAdmin version I am using. 5 Stars for the database for me! Thanks guys!

rating

shootersolutions: 11 Months, 3 Weeks, 1 Hour, 58 Minutes ago

With XAMP, modify the step 2 directions with, in a newly created database, then import the database under browse for files, and then all the tables will be imported into the blank database. I created a blank database for wa_ecommerce name and also one for "test".
If you try to import the file into above the databases, you will get errors. I think if the database already has any tables named the same thing as tables in the database, the server tables win. (the drop command, so the term "create a database name with no tables yet" works too, as all the tables are imported as-is with all inter-relations intact: if you import it into an existing database, then only tables with no name conflicts will be imported meaning you might have problems.)
I imported an older modified physical_goods .sql into wa_commerce and only the non-conflicting, but all of them tables were imported, the result being 14 tables between the two, or 6 tables imported intact from the physical_goods sql.

: 10 Months, 3 Weeks, 1 Day, 12 Hours, 13 Minutes ago

Suppose sir I have multiple category products like Laptop, mobile, Pendrive, Sunglasses, Shoes, etc. then how to design table for it ?
Because each subcategory or subcategory products have different attributes or features.
Suppose I have subcategory laptop and it's product is HP XXX model then it will have RAM,HDD,Processor,Screen Size, Battery.
and on other hand I have subcategory named Shoes and product is Nike XXX model then it will have attribute shoe size, color, material, warranty etc.
So How to design table for that ?

Any idea sir ?

Team WebAssist: 10 Months, 3 Weeks, 1 Day, 6 Hours, 38 Minutes ago

I think the "Populate your product catalog" section of this tutorial will help you: http://www.webassist.com/tutorials/Add-dynamic-product-options-I-eCommerce-Series

mema: 3 Months, 4 Weeks, 4 Hours, 2 Minutes ago

Step 1: Run the .sql script this action is not working at all, not sure what to do, some lines errors

CREATE TABLE IF NOT EXISTS `optiongroups` ( `OptionGroupID` int(11) NOT NULL AUTO_INCREMENT, `OptionGroupName` varchar(50) COLLATE latin1_german2_ci DEFAULT NULL, PRIMARY KEY (`OptionGroupID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=4

CREATE TABLE IF NOT EXISTS `sbc` ( `OptionGroupID` int(11) NOT NULL AUTO_INCREMENT, `OptionGroupName` varchar(50) COLLATE latin1_german2_ci DEFAULT NULL, PRIMARY KEY (`OptionGroupID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=4

kenny.belize: 1 Month, 6 Minutes ago

love it! Many thanks and hours saved!!! :) i downloaded the sql file, saved it to my desktop and imported it into a blank database in the phpmyadmin panel. not even a minute to set up. :)

rating

: 2 Weeks, 1 Day, 2 Hours, 30 Minutes ago

Okay, this was tedious and more difficult then I thought :/

rating

: 1 Week, 1 Day, 1 Hour, 53 Minutes ago

you know what would be nice?
If you guys cut to the chase and did a nice simple "non confusing" step by step procedure from beginning to end starting with step 1 and continuing onto step 100 or however many it takes. Choice 1 Dynamic website, Choice 2 Static website, Choice 3 Dynamic website with shopping cart and choice 4 Static Website with shopping cart. Within each choice is your down to Earth step by step from start to end. "Period"
You guys need to distinguish one thing "every person has different abilities some of us are noobs and others advanced" regardless of the ability if you follow my easy instructions above, none of us would be confused. A beginer like myself would simply start at the beginning "which I really don't mind" and advanced people can easily comb to where they want to be. But right now, you guys have these tutorials as such that no one knows really where to start and where we are going LMAO!!! and it's very annoying!

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.