How can I select certain sizes to populate a dropdown

[The_FedEx_Guy]

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

[bregent]

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

[The_FedEx_Guy]

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>

:'(

[bregent]

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

[The_FedEx_Guy]

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?

[bregent]

>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);

[The_FedEx_Guy]

That looks promising.

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

[The_FedEx_Guy]

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