Web development tutorial

Tutorial created by Ray Borduin, WebAssist

Categories: MySQL

39 ratings | Sign in to rate this tutorial | View comments

Print tutorial
Share this tutorial

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.

next section

Download 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

next section

Set 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.

next section

Option 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

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

Step 2: Execute the .sql script

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

Option 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

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

Reviews 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.

39 ratings | Sign in to rate this tutorial

: 11 Years, 5 Months, 1 Week, 1 Day, 12 Hours, 26 Minutes ago

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

shootersolutions: 11 Years, 2 Months, 14 Hours, 31 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!

shootersolutions: 11 Years, 2 Months, 13 Hours, 38 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.

: 11 Years, 1 Month, 1 Day, 23 Hours, 53 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: 11 Years, 1 Month, 1 Day, 18 Hours, 18 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: 10 Years, 6 Months, 15 Hours, 42 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: 10 Years, 3 Months, 3 Days, 11 Hours, 46 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. :)

: 10 Years, 2 Months, 2 Weeks, 4 Days, 14 Hours, 10 Minutes ago

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

: 10 Years, 2 Months, 1 Week, 4 Days, 13 Hours, 33 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!

Roland Rogers: 10 Years, 2 Weeks, 4 Days, 19 Hours, 42 Minutes ago

This is a fantastic resource you are offering for free! Thank you. I don't know how anyone can find this difficult! You make it so clear and simple that my kids could figure this out. If folks have never seen their cpanel or phpMyadmin then I can see them having issues, but if that is the case then they should not be tinkering with a database in the first place!

Skyrunner428939: 10 Years, 2 Weeks, 2 Days, 17 Hours, 15 Minutes ago

May I inform you about a small mistake in this tutorial:
Step 1:Copy the content by pressing Ctrl C (Windows) or Cmd A (Mac). It must be Cmd C(Mac).

Team WebAssist: 10 Years, 2 Weeks, 1 Day, 14 Hours, 35 Minutes ago

Skyrunner428939 - Thank you. Fixed it!

sfranzken73972: 10 Years, 2 Days, 22 Hours, 10 Minutes ago

Is there a sample php pages to go with the store?

Team WebAssist: 10 Years, 1 Day, 16 Hours, 51 Minutes ago

sfranzken73972, You can try this tutorial instead if you want to pair a database and some sample pages: http://www.webassist.com/tutorials/Getting-started-with-MySQL-and-dynamic-websites

Or you can continue to use this one, which is intended to be used as you create PHP pages with our Dreamweaver extensions. There are many, many tutorials on using our extensions.

: 9 Years, 6 Months, 1 Week, 6 Days, 20 Hours, 1 Minute ago

Great work!

: 9 Years, 2 Months, 3 Weeks, 6 Days, 15 Hours, 11 Minutes ago

Greatest. ALL the tables and groups were easily imported. But remember your database should have no table, otherwise you will get error

: 9 Years, 2 Months, 2 Days, 13 Hours, 39 Minutes ago

excellent work !!!

: 9 Years, 2 Months, 1 Day, 7 Hours, 59 Minutes ago

Thank you for all that you do!!

: 9 Years, 1 Month, 4 Days, 2 Hours, 59 Minutes ago

i need a sample data inside the database. because i want to check my server performance. for that i need to add 1000 products

: 9 Years, 3 Weeks, 2 Days, 51 Minutes ago

thank you so much :-)

: 8 Years, 11 Months, 2 Days, 23 Hours, 48 Minutes ago

tank you

: 8 Years, 10 Months, 3 Weeks, 3 Days, 13 Hours, 45 Minutes ago

Thank you very much it much help full

: 8 Years, 10 Months, 1 Day, 23 Hours, 26 Minutes ago

how do one get the download

Ray Borduin: 8 Years, 10 Months, 1 Day, 22 Hours, 16 Minutes ago

There is a link to the download in step 1... just click on it and you will download the .zip file.

: 8 Years, 9 Months, 2 Weeks, 2 Days, 12 Hours, 38 Minutes ago

works perfectly with import in godaddy

: 8 Years, 8 Months, 2 Weeks, 4 Days, 23 Hours ago

Good work See also mine work,

i have create complete and simple Online Shop Database, i have create its system which is working fine http://www.codingcabinet.com/question/how-to-create-a-data-base-for-online-shop-system/

: 8 Years, 7 Months, 4 Weeks, 1 Day, 2 Hours, 7 Minutes ago

Thank you so much,

: 8 Years, 5 Months, 3 Weeks, 1 Day, 23 Hours, 3 Minutes ago

good after noon sir....i m designing a database for online jewellery Store...
and I have 2-3 different size of few products so should i make a diffrent table for size or It(Size) will come in ProductDetail table.
eg. productname=necklace
Size=Free size,4.0,5.8

: 8 Years, 5 Months, 4 Days, 18 Hours, 5 Minutes ago

Thanks a lot. It works perfectly fine. :)

: 8 Years, 2 Weeks, 6 Days, 14 Hours, 12 Minutes ago

very good

: 8 Years, 6 Days, 2 Hours, 35 Minutes ago

Thanks for share...

: 7 Years, 11 Months, 4 Days, 53 Minutes ago

Hello , may i know what is difference between categoryID and productID

: 7 Years, 3 Months, 1 Week, 6 Days, 14 Hours, 36 Minutes ago

For Urdu speaker follow the link for e-commerce tutorials in Urdu and Hindi https://youtu.be/m9IEUcJABIg

: 6 Years, 10 Months, 1 Week, 2 Days, 2 Hours, 45 Minutes ago

Hello Sir,

Where i have to store cart data of the guest user so that if user go off from my website when again user come how i show there added product in cart.

: 6 Years, 8 Months, 1 Week, 2 Days, 23 Hours, 57 Minutes ago

thanks, good work

: 6 Years, 1 Month, 4 Weeks, 2 Days, 4 Hours, 57 Minutes ago

excellent job.thanks for sharing

: 6 Years, 3 Days, 21 Hours, 32 Minutes ago

i want to upload my all data through excel only. can anybody give or tell me what i need to do to upload data in the same database for products. i can upload data in product table but facing issue to upload it in productoptions in one go.. Thanks in advance.

: 5 Years, 10 Months, 3 Weeks, 2 Days, 17 Hours, 19 Minutes ago

Thank you

: 5 Years, 2 Months, 2 Weeks, 2 Days, 13 Hours, 11 Minutes ago

hello i'm trying to convert this data to SQL management studio but the file has many red errors, what can I do to fix this in order to submit the query?

: 2 Years, 5 Months, 1 Day, 11 Hours, 51 Minutes ago

Well done. Thank you so much.

Free eCommerce MySQL Database