Jump to content

Archived

This topic is now archived and is closed to further replies.

downfall

Need help with my online store!!

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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}

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.