Jump to content
Forums are read-only ×
Larry Ullman's Book Forums

Dropdown Menus Of Available Sizes And Colors With Php/mysql

Jane39
 Share

Recommended Posts

Jane39 Newbie

Jane39

Posted
Posted

Hi everyone,

 

I'm building my own shopping website. I haven't figured out how to populate two dropdown lists with available sizes and colors of a product_id from database. 

 

Here is the result I've got from querying database.

 

id     product_name     style      size    color      price        image     sku          stock

1      Neck Top            104       S        White     $30.00     104.jpg    104-1           5

2      Neck Top            104       S        Black     $30.00     104.jpg    104-2            5

3      Neck Top            104       M       Red        $30.00     104.jpg    104-3          10

4      Neck Top            104       L        Blue       $30.00     104.jpg    104-4          10

5      Neck Top            104       XL      Black      $30.00     104.jpg    104-5           5

 

And here are my tables:

 

tblProduct                      tblSizes      tblColors       tblSpecific_Products

id                                     id                 id                     id        

product_name                 size              color                product_id                                                          

style                                                                            size_id

price                                                                            color_id

image                                                                          stock

category (tops,dresses,bottoms,...)

subcategory (bodycon,work,evening,cocktail)

                

Is there any way I can have it done without duplicating the values in each dropdown list? Also for each product_id, how can I store 3 different images (front view,back view and side view) for each color? Does it mean I have to create another attribute table for those images and store their names in database? I'm confused. 

Link to comment
Share on other sites
HartleySan Newbie

HartleySan

Posted
Posted

Hello Jane, and welcome to the forums.

 

I think there are a number of ways you can approach your problem, but first off, I need some help clarifying the problem:

Do you first want users to select a size, and then based off the selected size, dynamically display the available colors for that size?

 

As for the product images, I would create a second table for just the images, yes, and then link the images to specific products via the product IDs.

 

That all make sense?

Link to comment
Share on other sites
Jane39 Newbie

Jane39

Posted
  • Author
Posted

Hi HartleySan,

 

Thank you for your clarification, I have a better understanding of how it's like now.

 

Regarding your question, I want users to select a color first (if color options are > 1), then they will click on a dropdown list to select a size available for that color. While unavailable sizes are seen by users, they aren't clickable.

 

As for product images, I will create one separate table for them as you suggested. 

 

Here are my php codes for fetching records from database and echoing them to the page. 

 

       $sql = mysqli_query($con, "SELECT sp.id, p.product_name,p.style,s.size,c.color,p.price,p.image,CONCAT(p.style,'-',sp.id) AS sku, sp.stock FROM specific_products as sp INNER JOIN sizes AS s ON s.id=sp.size_id INNER JOIN colors AS c ON c.id=sp.color_id INNER JOIN products AS p ON p.id=sp.product_id WHERE p.id='$id' AND stock>0 ORDER BY p.date_created ASC");

      $product_id_count = mysqli_num_rows($sql); 
       if ($product_id_count > 0) {
               while($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){ 
                       $product_name = $row["product_name"];
                       $style = $row["style"];
                       $image = $row["image"];
                       $price = $row["price"];
                       $stock = $row["stock"];
                       $size .= "<option value='{$row['sku']}'"; 
                                 if ($sku == $row['sku'])
                                        $size .= "selected='selected'";
                                        $size .=">{$row['size']}</option>";
                                 if ($sku == $row['sku'])
                                        $color .= "selected='selected'";
                                        $color .=">{$row['color']}</option>";
                }
        } 
 
I've got two errors as follows:
Notice: Undefined variable: size in D:\XAMPP\htdocs\ECommerceSite\product.php on line 28

Notice: Undefined variable: color in D:\XAMPP\htdocs\ECommerceSite\product.php on line 32

 

I know my codes look terrible but I'm trying to learn more. Thanks.

Link to comment
Share on other sites
HartleySan Newbie

HartleySan

Posted
Posted

Jane, thanks for your response.

 

I'm not going to critique your query or code, but what I would recommend is doing the following:

  1. On page load, get all the available colors.
  2. On color selection (be it an Ajax call or a form submission), get the selected color, and pull all the available sizes for that color.
  3. When displaying the sizes drop-down, use the "disabled" attribute for options that are not available.

 

That should get you what you want.

  • Upvote 1
Link to comment
Share on other sites
Jane39 Newbie

Jane39

Posted
  • Author
Posted

Hi Hartley,

 

Regarding step 1 as you mentioned, I tried but still couldn't get all available colors to display on the product page. 

 

How do I have those colors in colors table to display on the product page? Do I have to create another attribute table for colors and products tables?  Right now I have products table, colors table, sizes table, images table and specific_products table (product_id FK, color_id FK, size_id FK, image_id FK).

 

What I've done now is I had all items displayed on the page. Each item has a default (front-view) image and a hidden (back-view) image. So when users hover their mouses over any default image, the hidden one will be showed. 

 

Thanks.

Link to comment
Share on other sites
HartleySan Newbie

HartleySan

Posted
Posted

Jane, until recently, I worked as a full-time web developer for a major international retailer, and while I didn't agree with all of their decisions when it came to DB design, there are a few universal truths for organizing clothing and the like in a DB:

  1. There should be an item/product table that contains an entry for each unique item/SKU. The table should contain a minimum amount of info. For example, it should not contain size or color info, etc.
  2. There should be a second table for all the colors available across the entire clothing line. The same holds true for sizes and all other attributes of the clothing.
  3. You should then have one or more intermediary tables to link all the attributes to the individual items. For example, if you have an item with ID 1, the size small in the sizes table is ID 2, and the color red in the colors table is ID 3, then you intermediary table would contain the following entry for that item in small red:
item_id   size_id   color_id
1         2         3

That make sense?

 

The whole point of this post is to illustrate the fact that with something as surprisingly complex as an online clothing shop, the heart of the site is the data in the DB, and if that data isn't properly organized, you're going to regret it as long as you run the site.

Hopefully once your DB is in order, you'll be able to easily handle the data in whatever way you need.

Link to comment
Share on other sites
Jane39 Newbie

Jane39

Posted
  • Author
Posted

I totally agreed with you about the DB design Hartley and I think I did the same thing. Here are how my tables look like.

 

 

tblProducts                   tblSizes      tblColors       tblImages              tblSpecific_Products

id                                     id                 id                    id                             id        

product_name                 size              color               image                      product_id  (FK)                                            

style_code                                                                                                 size_id (FK)

price                                                                                                          color_id (FK)

                                                                                                                  image_id (FK)

                                                                                                            

My question is how do I display all available colors on the general products page as you mentioned? See a sample picture of a product page for what I mean here. http://s12.postimg.org/l8s0zh4d9/product_page_sample.png.

 

Thanks Hartley.

Link to comment
Share on other sites
HartleySan Newbie

HartleySan

Posted
Posted

This is a pseudo query, but here goes:

SELECT *
FROM tblProducts AS p, tblSizes AS s, tblColors AS c, tblImages AS i, tblSpecific_Products AS x
WHERE p.id = x.product_id AND s.id = x.size_id AND c.id = x.color_id AND i.id = x.image_id AND p.id = ?;

Now, there are a few things to note:

  1. I don't think your table names are very logical. Something like products, sizes, colors, images and product_details seems more logical to me.
  2. You will probably not want to use * for the SELECT part of the query, but I put it there for simplicity.
  3. The query above will return the Cartesian product of all the joined tables. For example, if a single product has three sizes, four colors and two images, then you will get 3 x 4 x 2 (or 24) rows back for that product.
  4. If you don't mind executing more than one query, you can have one query that simply joins products with colors to get all the colors for that product.

 

Anyway, I'll leave the implementation details up to you.

  • Upvote 1
Link to comment
Share on other sites
  • 2 weeks later...
Jane39 Newbie

Jane39

Posted
  • Author
Posted

I finally managed to get all products and their available colors displayed on the page Hartley. It took me a lot of days before I figured it out, and now I feel super happy because I can move to the next step. Thanks :)

Link to comment
Share on other sites
  • 3 years later...
willynilly1 Newbie

willynilly1

Posted
Posted

Hi there , how a

 

I finally managed to get all products and their available colors displayed on the page Hartley. It took me a lot of days before I figured it out, and now I feel super happy because I can move to the next step. Thanks :)

Hi there how are you ? I'm a newbie as well and I'm stuck here too , please can you send me the codes for the database and php for the attributes so I can display them on my site as well 

Thank you

Link to comment
Share on other sites
  • 2 years later...
Sapna Newbie

Sapna

Posted
Posted
On 4/11/2014 at 8:17 PM, Jane39 said:

I finally managed to get all products and their available colors displayed on the page Hartley. It took me a lot of days before I figured it out, and now I feel super happy because I can move to the next step. Thanks :)

Hi Jane , how are you doing?
I'm a student and I'm stuck on adding product attributes(colors and sizes) to my ecommerce website
Can you please help me with the codes maybe I can use it as well ? Please help me exam is there in one month
Link to comment
Share on other sites
 Share
Go to topic listing
×
×
  • Create New...