Need help with my online store!!

[downfall]

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

[keithschm]

just join the prod_size table in your query an then add  to your where stament

and where stock_level < 0

something likek that should work

[downfall]

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

[keithschm]

not the way you have your datbase setup.  you have 1 product 1 stock level    you need

1 product ---options---\
----------------------options 1  ------stock level
----------------------options 2  ------stock level



Oscommerece has these features built in and it is open source{free}

[downfall]

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?

[keithschm]

ok yes  you just have to construct  you where query then you if when you display them

[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?

[keithschm]

can you send me a link so I may see the page