unistake Posted July 31, 2010 Share Posted July 31, 2010 Hi all, I have a html form list box that needs to show all the types of products that are for sale and have a number in the list box saying how many of that type are for sale. eg. If the mysql database looks like this productType | productDetails chair | outdoor large chair table | 2m x 1m chair | indoor chair chair | childs chair I would want the form list box to show the values inside as: chair (3) table (1) could someone give me the heads up on how to do this Thanks Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted July 31, 2010 Share Posted July 31, 2010 You'd first use an SQL query like this SELECT productType, COUNT(productType) as productQuantity FROM products_table GROUP BY productType That will select the product type and count how many there are, eg 3 chairs, 1 table Now in PHP you'd do something like this $sql = 'SELECT productType as type, COUNT(productType) AS quantity FROM products_table GROUP BY productType'; $result = mysql_query($sql); if(mysq_num_rows($result) > 0) { echo '<select name="product">'; while($product = mysql_fetch_assoc($result)) { echo sprintf('<option value="%1$s">%1$s (%2$d)</option>', $product['type'] , $product['quantity']); } echo '</select>'; } else { echo 'No products to list'; } Quote Link to comment Share on other sites More sharing options...
unistake Posted July 31, 2010 Author Share Posted July 31, 2010 thanks so much for the starting point. will figure it out now Quote Link to comment Share on other sites More sharing options...
unistake Posted July 31, 2010 Author Share Posted July 31, 2010 Works great but the quantity is always showing '0'. Do you know why that would be, there are more than one type of 'manufactuer' with the same name in the database. Must be something wrong with $manufactuer['quantity'] but looks fine to me. <?php $sql = "SELECT manufacturer as type, COUNT(manufacturer) AS quantity FROM sales GROUP BY manufacturer"; $result = mysqli_query($cxn,$sql) or die ("I just cant do it capt'n!"); if(mysqli_num_rows($result) > 0) { echo '<select name="manufacturer">'; while($manufacturer = mysqli_fetch_assoc($result)) { echo sprintf('<option value="%1$s">%1$s (%d)</option>', $manufacturer['type'] , $manufacturer['quantity']); } echo '</select>'; ?> Quote Link to comment Share on other sites More sharing options...
unistake Posted July 31, 2010 Author Share Posted July 31, 2010 anybody? Quote Link to comment Share on other sites More sharing options...
wildteen88 Posted July 31, 2010 Share Posted July 31, 2010 %d should be %2$d Quote Link to comment Share on other sites More sharing options...
unistake Posted July 31, 2010 Author Share Posted July 31, 2010 thanks, works great Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.