Jump to content

Need help with my online store!!


downfall

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

Link to comment
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

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.