Skip navigation
The_FedEx_Guy
The_FedEx_Guy
Currently Being Moderated

How can I select certain sizes to populate a dropdown

Sep 4, 2012 12:40 PM

Tags: #problem #php #dreamweaver #mysql

Hi all,

I created an "online store" in a rush about a year ago and the client now wants to change something which I cannot figure out how to do.

 

In my database I have one table: products

 

CREATE TABLE IF NOT EXISTS `products` (

  `prod_id` int(8) NOT NULL AUTO_INCREMENT,

  `prod_name` varchar(255) DEFAULT NULL,

  `prod_description` text,

  `prod_size` varchar(10) DEFAULT NULL,

  `prod_colour` varchar(20) DEFAULT NULL,

  `prod_price` varchar(15) DEFAULT NULL,

  `prod_category` varchar(50) DEFAULT NULL,

  `prod_type` varchar(50) DEFAULT NULL,

  `list_image` varchar(255) DEFAULT NULL,

  `prod_image` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`prod_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

 

The client would like the size and colour that is displayed to be input into the backend control panel so that users can only select what they have in stock

 

So if they only do M and XL in a sweatshirt the dropdown only displays M and XL

 

Currently the options are as below(Everything) :

 

                        <select class="item_Size">
                          <option>Size</option>
                          <option value="Small">Small</option>
                          <option value="Medium">Medium</option>
                          <option value="Large">Large</option>
                          <option value="XLarge">X-Large</option>
                          </select>
                        <select class="item_colour" id="item_colour">
                          <option>Colour</option>
                          <option value="Brown" class="brown">Brown</option>
                          <option value="Purple" class="purple">Purple</option>
                          <option value="Orange" class="orange">Orange</option>
                          <option value="Baby Blue" class="babyblue">Baby Blue</option>
                          <option value="Yellow" class="yellow">Yellow</option>
                          <option value="Pink" class="pink">Pink</option>
                          <option value="Green" class="green">Green</option>
                          <option value="Blue" class="blue">Blue</option>
                          <option value="Red" class="red">Red</option>
                          <option value="Black" class="black">Black</option>
                          <option value="Charcoal" class="charcoal">Charcoal</option>
                          <option value="Grey" class="grey">Grey</option>
                          <option value="White" class="white">White</option>
                          </select>

 

Also is there a better way to display colours in selects apart from using my stylesheet method, I want them to be able to add the colours on themselves.

 

Thanks for any help.

 

Using PHP / MySQL

592 Views   7 Replies   Latest reply: The_FedEx_Guy, Sep 9, 2012 10:08 AM
 
Replies
  • bregent
    Currently Being Moderated
    1. bregent,
    Sep 4, 2012 1:58 PM   in reply to The_FedEx_Guy
    Report

    The SQL to populate the dropdown boxes would need to include a condition in the WHERE clause to only include items in stock - but I do not see such a field to indicate that in your table. How do you know what is in stock?

     

    You would use something like this:

    SELECT DISTINCT Colour, Size WHERE prod_id = '12345' and items_on_hand > 0

     
    |
    Mark as:
  • The_FedEx_Guy
    Currently Being Moderated
    2. The_FedEx_Guy,
    Sep 4, 2012 2:26 PM   in reply to bregent
    Report

    Hi

    Thanks for your reply.

    I do not have a stock field.

    I would use prod_size to list the sizes, or have another table.

     

    But if I use prod_size I would want the dropdown in the HTML to list the sizes individually.

    prod_size field would = S/M/L/XL

    This should expand/break apart to:

    S

    M

    L

    XL

     

    I remember seeing a method years ago to something similar but cannot find it.

    The method used semi colons ";" to separate the fields entered into a textbox so they would display into a dropdown

     

    e.g:

     

    Field: inputString1

    Content: S;S;M;M;L;L;XL;XL

     

    This would then somehow create a dropdown like this:

    <select>

    <option value="S">S</option>

    <option value="M">M</option>

    <option value="L">L</option>

    <option value="XL">XL</option>

    </select>

     

    :'(

     
    |
    Mark as:
  • bregent
    Currently Being Moderated
    3. bregent,
    Sep 4, 2012 2:58 PM   in reply to The_FedEx_Guy
    Report

    >I do not have a stock field.

     

    OK, your original question stated " The client would like the size and colour that is displayed to be input into the backend control panel so that users can only select what they have in stock".  So how do they know what is actually in stock?

     

    >prod_size field would = S/M/L/XL

     

    That's a bad database design. For many reasons, the sizes/colors should be in a separate table from the main product table.

     

    >Content: S;S;M;M;L;L;XL;XL

    >

    >This would then somehow create a dropdown like this:

     

    That's simply a matter of parsing the string into an array on its separator character and looping through an array to create the drop down items. You don't mention what scripting language you are using.

     
    |
    Mark as:
  • The_FedEx_Guy
    Currently Being Moderated
    4. The_FedEx_Guy,
    Sep 4, 2012 11:02 PM   in reply to bregent
    Report

    They do not manage stock levels as such. They just want to remove sizes and colours not available

     

    I stated I was using PHP & MySQL

     

    Can you give me an example of parsing a string into an array?

     
    |
    Mark as:
  • bregent
    Currently Being Moderated
    5. bregent,
    Sep 5, 2012 9:39 AM   in reply to The_FedEx_Guy
    Report

    >I stated I was using PHP & MySQL

     

    Ah, sorry, I missed that. I'm a ASP guy - I don't really know PHP, but I think you could use the explode function:

     

    http://www.php.net/manual/en/function.explode.php

     

    Assuming the data is stored in the string like "S/M/L/XL"

     

    $sizes= "S/M/L/XL";
    $size = explode("/", $sizes);

     
    |
    Mark as:
  • The_FedEx_Guy
    Currently Being Moderated
    6. The_FedEx_Guy,
    Sep 5, 2012 10:30 PM   in reply to bregent
    Report

    That looks promising.

    The same cannot work for colour as I need the colour displayed as a background colour

     
    |
    Mark as:
  • The_FedEx_Guy
    Currently Being Moderated
    7. The_FedEx_Guy,
    Sep 9, 2012 10:08 AM   in reply to The_FedEx_Guy
    Report

    Thanks, after your pointer I have been able to create what I required.

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points