downfall

ok, how exactly can I construct my query so it does not display a product if ALL sizes have no stock, in relation to my database?

are you sure it can't be done the way it's set up? here is an example of a product entered into the database with 3 sizes: stock id             product_id             size                  stock_level 1                             1                  small                       0 2                             1                  medium                    2 3                             1                  large                        2 As product_id is the same for all sizes, yet each size has there own stock_id, are you sure it can't be done?

Would that work if I had a product with mutliple sizes in the prod_stock table, such as small, medium and large in the sizes field? For example, if I had 0 small, but 2 medium and 2 large, wouldn't what you suggested not display the product, even thought I would still have some medium and large stock? It really needs to be if all product sizes are 0 for stock level, then the product is not listed? But, if what you have said DOES do that, could you just confirm that? Thanks

What I would like my online store to do, is to be able to not display a product from my database if there is no stock left for any sizes. This is my php code which displays the products on my website: [code] $cat = $_GET['id']; $sql = "SELECT p.product_name, p.product_price, b.band_name, c.category_name, p.product_id         FROM products p         INNER JOIN band_products bp ON p.product_id = bp.product_id         INNER JOIN bands b ON bp.band_id = b.band_id         INNER JOIN categories c ON c.category_id = p.category_id         WHERE p.category_id = '$cat'         ORDER BY p.product_name, b.band_name"; $result = mysql_query($sql) or die (mysql_error()); while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){ echo " <table width=\"180\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\" align=\"center\">                             <tr>                               <td>{$product['product_name']}</td>                             </tr>                             <tr>                               <td>{$product['category_name']}</td>                             </tr>                             <tr>                               <td>{$product['band_name']}</td>                             </tr>                             <tr>                               <td>{$product['product_price']}</td>                             </tr>                             <tr>                               <td>&nbsp;</td>                             </tr>                             <tr>                               <td>&nbsp;</td>                             </tr>                           </table> <br> <FORM METHOD=\"POST\" ACTION=\"http://ww6.aitsafe.com/cf/add.cfm\">     <INPUT TYPE=\"HIDDEN\" NAME=\"userid\" VALUE=\"88166920\">     <INPUT TYPE=\"HIDDEN\" NAME=\"price\" VALUE=\"{$product['product_price']}\">     <INPUT TYPE=\"HIDDEN\" NAME=\"units\" VALUE=\"0.3\">     <INPUT TYPE=\"HIDDEN\" NAME=\"return\" VALUE=\"http://www.spiral-scandinavia.com/return_mals.asp?doc=http://www.spiral-scandinavia.com/catalog/tshirts/index.asp\">     <SELECT NAME=\"product\" SIZE=\"1\">"; $size_sql = "SELECT size, stock_level FROM products LEFT JOIN prod_size ON products.product_id = prod_size.product_id WHERE products.product_id = '".$product['product_id']."' AND stock_level > 0";   $res = mysql_query($size_sql) or die (mysql_error());     while($r = mysql_fetch_array($res)){     echo "<option VALUE=\"{$product['band_name']}&nbsp;-&nbsp;{$product['product_name']}&nbsp;-&nbsp;".$r['size']."&nbsp;(Category: {$product['category_name']})\">".$r['size']."</option>";     } ?>         </SELECT><br><br>     <input TYPE="submit" value="Buy Now" border="0" NAME="Order">     </form> <? } ?> [/code] Here is my database, with 5 tables: [b]categories    [/b]                   [b]products[/b]                    [b]band_products [/b]              [b]bands  [/b]                  [b]prod_size[/b]  category_id (pk)               product_id (pk)          bp_id (pk)                  band_id (pk)         stock_id (pk) category_name                product_name             product_id (fk)            band_name          product_id (fk)                                    product_small_image         band_id (fk)                                        size                                      product_large_image                                                              stock level                                       product_price                                    category_id (fk)                                                                                                                               Can anyone alter the php code from my product listing code above so a product does not display if there is no stock for any sizes? Any help would be much apreciated!!!

xsist10 - just me being stupid! It works fine! ;)

I don't understand!! Still getting nothing, not even the space the table rows give appear on the site!! And nothing it echoed, no text despite getting no errors!

Could you insert the html table into the whole PHP code? It needs to go inplace of [code] {$product['product_name']} <br> {$product['category_name']} <br> {$product['band_name']} <br> {$product['product_price']} [/code] What you have done works but it doesn't when I try and merge it together with all the other PHP code!! I've been trying for 2 hours now and gotten nowhere!!

Hi, I'm getting errors trying to put my PHP code into my HTML. Can anyone help? Here is the PHP: [code]$cat = $_GET['id']; $sql = "SELECT p.product_name, p.product_price, b.band_name, c.category_name, p.product_id         FROM products p         INNER JOIN band_products bp ON p.product_id = bp.product_id         INNER JOIN bands b ON bp.band_id = b.band_id         INNER JOIN categories c ON c.category_id = p.category_id         WHERE p.category_id = '$cat'         ORDER BY p.product_name, b.band_name"; $result = mysql_query($sql) or die (mysql_error()); while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){ echo " {$product['product_name']} <br> {$product['category_name']} <br> {$product['band_name']} <br> {$product['product_price']} <br> <FORM METHOD=\"POST\" ACTION=\"http://ww6.aitsafe.com/cf/add.cfm\">     <INPUT TYPE=\"HIDDEN\" NAME=\"userid\" VALUE=\"88166920\">     <INPUT TYPE=\"HIDDEN\" NAME=\"price\" VALUE=\"{$product['product_price']}\">     <INPUT TYPE=\"HIDDEN\" NAME=\"units\" VALUE=\"0.3\">     <INPUT TYPE=\"HIDDEN\" NAME=\"return\" VALUE=\"http://www.spiral-scandinavia.com/return_mals.asp?doc=http://www.spiral-scandinavia.com/catalog/tshirts/index.asp\">     <SELECT NAME=\"product\" SIZE=\"1\">"; $size_sql = "SELECT size, stock_level FROM products LEFT JOIN prod_size ON products.product_id = prod_size.product_id WHERE products.product_id = '".$product['product_id']."' AND stock_level > 0";   $res = mysql_query($size_sql) or die (mysql_error());     while($r = mysql_fetch_array($res)){     echo "<option VALUE=\"{$product['band_name']}&nbsp;-&nbsp;{$product['product_name']}&nbsp;-&nbsp;".$r['size']."&nbsp;(Category: {$product['category_name']})\">".$r['size']."</option>";     } ?>         </SELECT><br><br>     <input TYPE="submit" value="Buy Now" border="0" NAME="Order">     </form> <? } ?>[/code] And I would like to merge it into this simple bit of HTML I've taken from my webpage: [code]<table width="180" border="0" cellspacing="0" cellpadding="0" align="center">                             <tr>                               <td>product name here</td>                             </tr>                             <tr>                               <td>band name here</td>                             </tr>                             <tr>                               <td>category name here</td>                             </tr>                             <tr>                               <td>&nbsp;</td>                             </tr>                             <tr>                               <td>size dropdown menu</td>                             </tr>                             <tr>                               <td>submit/buy button</td>                             </tr>                           </table>                         [/code] In the HTML I've put in the <td> tags where I want the PHP to be echoed. Can anyone help me with this?

Thanks for the quick replies, I used <br/> and works great!

Hi, At the moment, when my webpage retrives data from the database, the results list themselves side by side, like this: T-Shirts Posters Calenders As this is for the navigation, I need them to be listed downwards, like this: T-Shirts Posters Calenders Can anyone tell me how to do this? This is my code for the php I'm inserting: [code]<? $sql = "SELECT category_id, category_name         FROM categories         ORDER BY category_name"; $res = mysql_query($sql) or die(mysql_error()); while (list($id, $cat) = mysql_fetch_row($res)) {     echo "<a href='details2.php?id=$id'>$cat</a>\n"; } echo <<<_HTML $list _HTML; ?>[/code]

Pretty please? Craygo has done a great job with only sizes appearing in the sizes dropdown that are in stock in my database, but would just like to carry this foward by having the Buy Now button change to an Out Of Stock button/text if ALL the sizes for that product are at 0. I'd Really, really appreciate any help to chieve this!!

Anyone?

Thanks printf for spotting that! craygo, the code is working perfect! I'm very pleased! At the moment when I change all sizes of a product to a stock level of 0 (in the database) the dropdown menu is emtpy, which is just how I wanted it, but I've realised it still contains the Buy Now button. Is their a way to expand on your code to say if ALL sizes in the "size" feild have 0 stock in the "stock_level" field, then replace the Buy Now submit button with some general text such as "Out Of Stock"?

hi, thanks for the code ;) one problem tho, im getting this error: Parse error: syntax error, unexpected T_VARIABLE, expecting ']' in /home/rockrag/public_html/details6.php on line 32 Line 32 is:         WHERE p.category_id = '$cat' Any idea why the parse error? Oh, the list of value options contains the product name, price etc and fowards this information to the shopping cart once submitted.

That would be fantastic if you could insert it into my page!! [code] <html> <head> <title>Product Details</title> <body> <?php #set variables For Database $host = "********"; $user = "********"; $password = "**********"; $conn = mysql_connect ($host, $user, $password) or die("could not connect"); #select specific database $rs = mysql_select_db("**********", $conn) or die("could not select database"); $cat = $_GET['id']; $sql = "SELECT p.product_name, p.product_price, b.band_name, c.category_name, p.product_id          FROM products p         INNER JOIN band_products bp ON p.product_id = bp.product_id         INNER JOIN bands b ON bp.band_id = b.band_id         INNER JOIN categories c ON c.category_id = p.category_id         WHERE p.category_id = '$cat'         ORDER BY p.product_name, b.band_name"; $result = mysql_query($sql) or die (mysql_error()); while ($product = mysql_fetch_array($result, MYSQL_ASSOC)){ echo <<<HTML {$product['product_name']} <br> {$product['category_name']} <br> {$product['band_name']} <br> {$product['product_price']} <br> <FORM METHOD="POST" ACTION="http://ww6.aitsafe.com/cf/add.cfm">        <INPUT TYPE="HIDDEN" NAME="userid" VALUE="88166920">     <INPUT TYPE="HIDDEN" NAME="price" VALUE="{$product['product_price']}">     <INPUT TYPE="HIDDEN" NAME="units" VALUE="0.3">     <INPUT TYPE="HIDDEN" NAME="return" VALUE="http://www.spiral-scandinavia.com/return_mals.asp?doc=http://www.spiral-scandinavia.com/catalog/tshirts/index.asp"> <SELECT NAME="product" SIZE="1">     <option VALUE="{$product['band_name']}&nbsp;-&nbsp;{$product['product_name']}&nbsp;-&nbsp;Small&nbsp;(Category: {$product['category_name']})">Small <br>     <option VALUE="{$product['band_name']}&nbsp;-&nbsp;{$product['product_name']}&nbsp;-&nbsp;Medium&nbsp;(Category: {$product['category_name']})">Medium <br>     <option VALUE="{$product['band_name']}&nbsp;-&nbsp;{$product['product_name']}&nbsp;-&nbsp;Large&nbsp;(Category: {$product['category_name']})">Large <br>     <option VALUE="{$product['band_name']}&nbsp;-&nbsp;{$product['product_name']}&nbsp;-&nbsp;Extra Large&nbsp;(Category: {$product['category_name']})">Extra Large<br>           </SELECT><br><br>     <input TYPE="submit" value="Buy Now" border="0" NAME="Order">     </form> HTML; } ?> </body> </html> [/code]