Tutorial created by Ray Borduin, WebAssist
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.
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.
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.
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.
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.
: 9 Years, 9 Months, 1 Week, 5 Days, 13 Hours, 1 Minute ago
excellent job... it worked a lot...
shootersolutions: 9 Years, 6 Months, 4 Days, 15 Hours, 7 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: 9 Years, 6 Months, 4 Days, 14 Hours, 14 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.
: 9 Years, 5 Months, 6 Days, 29 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: 9 Years, 5 Months, 5 Days, 18 Hours, 54 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: 8 Years, 10 Months, 4 Days, 16 Hours, 18 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: 8 Years, 7 Months, 1 Week, 12 Hours, 21 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. :)
: 8 Years, 6 Months, 3 Weeks, 1 Day, 14 Hours, 45 Minutes ago
Okay, this was tedious and more difficult then I thought :/
: 8 Years, 6 Months, 2 Weeks, 1 Day, 14 Hours, 8 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: 8 Years, 4 Months, 3 Weeks, 1 Day, 20 Hours, 17 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: 8 Years, 4 Months, 2 Weeks, 6 Days, 17 Hours, 50 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: 8 Years, 4 Months, 2 Weeks, 5 Days, 15 Hours, 11 Minutes ago
Skyrunner428939 - Thank you. Fixed it!
sfranzken73972: 8 Years, 4 Months, 6 Days, 22 Hours, 45 Minutes ago
Is there a sample php pages to go with the store?
Team WebAssist: 8 Years, 4 Months, 5 Days, 17 Hours, 26 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.
: 7 Years, 10 Months, 2 Weeks, 3 Days, 20 Hours, 37 Minutes ago
: 7 Years, 7 Months, 15 Hours, 46 Minutes ago
Greatest. ALL the tables and groups were easily imported. But remember your database should have no table, otherwise you will get error
: 7 Years, 6 Months, 6 Days, 14 Hours, 14 Minutes ago
excellent work !!!
: 7 Years, 6 Months, 5 Days, 8 Hours, 35 Minutes ago
Thank you for all that you do!!
: 7 Years, 5 Months, 1 Week, 1 Day, 3 Hours, 34 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
: 7 Years, 4 Months, 3 Weeks, 6 Days, 1 Hour, 27 Minutes ago
thank you so much :-)
: 7 Years, 3 Months, 2 Weeks, 23 Minutes ago
: 7 Years, 3 Months, 4 Days, 14 Hours, 21 Minutes ago
Thank you very much it much help full
: 7 Years, 2 Months, 1 Week, 6 Days, 2 Minutes ago
how do one get the download
Ray Borduin: 7 Years, 2 Months, 1 Week, 5 Days, 22 Hours, 52 Minutes ago
There is a link to the download in step 1... just click on it and you will download the .zip file.
: 7 Years, 1 Month, 3 Weeks, 6 Days, 13 Hours, 13 Minutes ago
works perfectly with import in godaddy
: 7 Years, 4 Weeks, 1 Day, 23 Hours, 35 Minutes 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/
: 7 Years, 1 Week, 2 Days, 2 Hours, 42 Minutes ago
Thank you so much,
: 6 Years, 9 Months, 3 Weeks, 5 Days, 23 Hours, 38 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.
: 6 Years, 9 Months, 1 Week, 1 Day, 18 Hours, 40 Minutes ago
Thanks a lot. It works perfectly fine. :)
: 6 Years, 4 Months, 3 Weeks, 3 Days, 14 Hours, 47 Minutes ago
: 6 Years, 4 Months, 1 Week, 3 Days, 3 Hours, 10 Minutes ago
Thanks for share...
: 6 Years, 3 Months, 2 Weeks, 1 Day, 1 Hour, 28 Minutes ago
Hello , may i know what is difference between categoryID and productID
: 5 Years, 7 Months, 2 Weeks, 3 Days, 15 Hours, 11 Minutes ago
For Urdu speaker follow the link for e-commerce tutorials in Urdu and Hindi https://youtu.be/m9IEUcJABIg
: 5 Years, 2 Months, 2 Weeks, 6 Days, 3 Hours, 20 Minutes ago
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.
: 5 Years, 3 Weeks, 32 Minutes ago
thanks, good work
: 4 Years, 6 Months, 3 Days, 5 Hours, 33 Minutes ago
excellent job.thanks for sharing
: 4 Years, 4 Months, 1 Week, 22 Hours, 7 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.
: 4 Years, 3 Months, 3 Days, 17 Hours, 54 Minutes ago
: 3 Years, 6 Months, 2 Weeks, 6 Days, 13 Hours, 46 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?
: 9 Months, 5 Days, 12 Hours, 26 Minutes ago
Well done. Thank you so much.
Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.
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.
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