Populate select list from database

I have a table full of products and i want to display them.

Some fo the products have the same item id (i.e. T-Shirts come in different sizes)

The thing is how do i identify which products have the same itemid so they can be output into a select list?

What i need is to put any items with the same itemid into a select list. Anything else just leave to go into the form

This is my code that outputs all the products

while (list($prodid,$item,$description,$price) = $result->fetchrow()) {
		
			if ($count % NUMCOLS == 0) echo "<tr>";  # new row
			echo '<td>';
			echo '<form method="post" action="" class="jcart">
					<fieldset>
						<input type="hidden" name="jcartToken" value="'.$_SESSION['jcartToken'].'" />
						<input type="hidden" name="my-item-id" value="'.$prodid.'" />
						<input type="hidden" name="my-item-name" value="'.$item.'" />
						<input type="hidden" name="my-item-price" value="'.$price.'" />
						<input type="hidden" name="my-item-url" value="product.php?prodid='.$prodid.'" />

						<ul>
							<li><img src="images/shop/products/test.gif" /></li>
							<li><strong><a href="product.php?prodid='.$prodid.'">'.$item.'</a></strong></li>
							<li>Price: &pound;'.number_format($price, 2).'</li>
							<li>
								<label>Qty: <input type="text" name="my-item-qty" value="1" size="3" /></label>
							</li>
						</ul>
						<div class="buttonadd">
							<input type="submit" name="my-add-button" value="&nbsp;" class="addcart" />
						</div>
					</fieldset>
				</form>	';
			echo '</td>';
			$count++;
			$counter++;
		
			if ($count % NUMCOLS == 0) echo "</tr>\
";  # end row
		}

and here is how i would have my form for the select list

<form method="post" action="" class="jcart">

	<fieldset>
		<input type="hidden" name="jcartToken" value="<?php echo $_SESSION['jcartToken'];?>" />
		<input type="hidden" name="my-item-id" value="1" />
		
		<input type="hidden" name="my-item-price" value="10.00" />
		
		<ul>
			<li>
				<select name="my-item-name" id="foo">
					<option value="size-1">Size 1</option>
					<option value="size-2">Size 2</option>
					<option value="size-3">Size 3</option>
				</select>   
				
			</li>
			<li>Price: $<span class="price">10.00</span></li>
			<li>
				<label>Qty: <input type="text" name="my-item-qty" value="1" size="3" /></label>
			</li>
		</ul>
		
		<input type="submit" name="my-add-button" value="add to cart" class="button" />
	</fieldset>
</form>

Database structure

CREATE TABLE `shop` (
  `prodid` int(2) NOT NULL auto_increment,
  `itemid` int(2) NOT NULL default '0',
  `item` varchar(50) NOT NULL default '',
  `size` char(2) NOT NULL default '',
  `description` text NOT NULL,
  `image` varchar(50) NOT NULL default '',
  `price` float NOT NULL default '0',
  PRIMARY KEY  (`prodid`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

-- 
-- Dumping data for table `shop`
-- 

INSERT INTO `shop` VALUES (1, 1, 'Key Ring', '', 'Key Ring: This can be personalised', '', 2);
INSERT INTO `shop` VALUES (2, 2, 'Tableware', '', 'Tableware: Coasters and Table Mats', '', 3);
INSERT INTO `shop` VALUES (3, 3, 'Braveheart Bear', '', '7" Braveheart Bear (choice of T-Shrt)', '', 9.99);
INSERT INTO `shop` VALUES (4, 4, 'Bravehart Bear', '', '9" Braveheart Bear (choice of T-Shirt)', '', 11.99);
INSERT INTO `shop` VALUES (5, 5, 'Wristband', '', 'Operation Braveheart wristband', '', 2);
INSERT INTO `shop` VALUES (6, 6, 'Mug', '', 'Standard mug', '', 7.99);
INSERT INTO `shop` VALUES (7, 7, 'A5 jotter', '', 'Various designs', '', 0.75);
INSERT INTO `shop` VALUES (8, 8, 'Operation Braveheart T-Shirt', 'S', 'Operation Braveheart T-Shirt.  All size in army green.', '', 6.99);
INSERT INTO `shop` VALUES (9, 8, 'Operation Braveheart T-Shirt', 'M', 'Operation Braveheart T-Shirt. All size in army green.', '', 7.99);
INSERT INTO `shop` VALUES (10, 8, 'Operation Braveheart T-Shirt', 'L', 'Operation Braveheart T-Shirt. All size in army green.', '', 8.99);
INSERT INTO `shop` VALUES (11, 8, 'Operation Braveheart T-Shirt', 'XL', 'Operation Braveheart T-Shirt. All size in army green.', '', 9.99);

Well, thats certainly an interesting way of doing your database.

Normally I dont give codeblocks of this size, but it’s gonna be hard to guide you into this without it.
Sticking with your table structure, i’d do it this way.


while ($row = $result->fetchrow()) { 
         $superitem[$row['itemid']][] = $row;
}

foreach($superitem AS $subitem) {
  list($prodid,$item,$description,$price) = $subitem[0];
  //Your normal code up until the select box...
  if(count($subitem) > 1) {
    echo '<select name="my-item-name" id="foo">';
    foreach($subitem AS $subsubitem) {
       echo "<option value='".$subsubitem['size']."'>".$subsubitem['size']."</option>";
    }
    echo "</select>";
  } else {
         echo '<input type="hidden" name="my-item-name" value="'.$item.'" />' 
  }

Thanks for your reply.

I’ve tried what you said (don’t know if I got it right but it doesn’t output anything.

Update code:

echo "<table border='0' id='gallery'>";
		
		while ($row = $result->fetchrow()) { 
			$superitem[$row['itemid']][] = $row;
		}
		while (list($prodid,$item,$description,$price) = $result->fetchrow()) {
		
			if ($count % NUMCOLS == 0) echo "<tr>";  # new row
			echo '<td>';
			
			foreach($superitem AS $subitem) {
				list($prodid,$item,$description,$price) = $subitem[0];
				//Your normal code up until the select box...
				echo '<form method="post" action="" class="jcart">
                
					 <fieldset>
					 <input type="hidden" name="jcartToken" value="'.$_SESSION['jcartToken'].'" />
							<input type="hidden" name="my-item-id" value="1" />
			   
						 <input type="hidden" name="my-item-price" value="10.00" />
			   
					  <ul><li>';
				if(count($subitem) > 1) {
					echo '<select name="my-item-name" id="foo">';
					foreach($subitem AS $subsubitem) {
						echo "<option value='".$subsubitem['size']."'>".$subsubitem['size']."</option>";
					}
					echo "</select></li>";
				}
				else {
					echo '<input type="hidden" name="my-item-name" value="'.$item.'" />';
				}
				echo '</li>';
			}
			  
			echo '</td>';
			$count++;
			$counter++;
		
			if ($count % NUMCOLS == 0) echo "</tr>\
";  # end row
		}
	
		# end row if not already ended
		
		if ($count % NUMCOLS != 0) {
		   while ($count++ % NUMCOLS) echo "<td>&nbsp;</td>";
		   echo "</tr>";
		}
		echo "</table>";

I am thinking about changing the database structure but i just want to get htis working before i do that.

Any ideas what i’ve done wrong?