phpMyAdmin Exercise 4

E-commerce mySQL Database Creation

 

  1. First, you will need to decide what you plan to sell in your e-commerce website.  I will sell CD’s in my sample e-commerce website.  For my CD store, I will need the following fields in the mySQL table.

 

    1. Item number (which is unique)
    2. Artist
    3. CD Name
    4. Price
    5. Quantity in stock
    6. Genre
    7. Product description

 

  1. To add a new table on the mySQL database, go to http://DR-WU-ASSIGNED-IP-ADDRESS/phpMyAdmin-2.5.1 or http://localhost/phpAdmin2.5.1 and login with your username and password.  You will be directed to the following page:

 

Figure 5.9

 

  1. Click on your database listed in the left side of the screen.  In my example, Figure 5.9, my database was named bobby.
  2. For my example CD store, I will create a table called cd_store with 7 fields (listed in step 1).  Figure 5.10 shows exactly how to create the database with different field types.  Pay special attention to the type column, extra column, and unique column.

 

Figure 5.10

 

  1. After this, you can begin adding records into your database by clicking the Insert tab on the screen that comes up after you hit the save button shown in Figure 5.10.
  2. You will also need to create a table that stores customers information when they enter their information at purchase time.  We will need to keep track of the following information

 

    1. Customer ID number
    2. First name
    3. Last name
    4. Address 1
    5. Address 2
    6. City
    7. State
    8. Zip
    9. Telephone number
    10. Credit Card #
    11. Credit Card Type
    12. Credit Card Expiration Date

 

  1. Repeat steps 3-4, except this time you will create a table with 12 fields.  The field description screen is shown below in Figure 5.11.  I named this table customers.

 

Figure 5.11

 

  1. You will also need to keep track of an orders database.  This is where you will insert information after a customer confirms his purchase.  You will need to keep track of

 

    1. Unique Order Number (will be decided in PHP code by last name, date, & time.)
    2. Customer_Id to ship order
    3. Item Ordered
    4. Quantity
    5. Date Ordered

 

  1. In this example, when a customer purchases more than 1 item, there will be multiple records with the same Order Number.  This number will be assigned in our php code.  Each row will contain a different Item Ordered field that corresponds to an item ID number from our cd_store database.

10.   Repeat steps 3-4 again with 5 fields.  For my example, I named the table orders.  Figure 5.12 shows us entering the structure in the creation process.

 

Figure 5.12

 

11.   Now, you will need to enter records into the cd_store & customers tables.  You can make up any data that you want to use in the database.