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]

That would be great, any code to help me achieve this would be much appreciated!

Hi, I've successfully made a dropdown menu with pure HTML for the sizes of my t-shirts, so the dropdown contains "Small", "Medium", "Large", and "Extra Large" on a product page where the user chooses their size before they buy. This is the HTML used for the sizes dropdown menu on my product page. The first "option VALUE" is the small size selection, the one below is medium, etc: [code] <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> [/code] What I would like to do is have a link / relationship between the small, medium, large and extra large dropdown options with my database stock, so the small option value in the dropdown would only show in the dropdown if the stock for it is greater then 0 in my database. This is my database with my product table and stock table: product                        prod_size ----------------            -------------- product_id          --+        stock_id  (pk) product_name        +---  product_id (fk) product_small_image        size product_large_image        stock_level product_price                category_id (fk) Can anyone help me?

anyone?? or barrand, could you give me more help with the coding you done with the dropdown menu with the added AJAX you mentioned??

Ok, I' thought about how I should be able to get this to work, in theory, It is essential that the buy now link contains the size the customer chooses, along with product name, band, price etc. that I already have in it. This is because with Mal's cart, all the information about what they order must be in that buy now link, as that is exactly is what emailed to me when the order is compelted, so I need to know the size in that along with the other info so I know exactly what that to ship to them! So this is how I think I can do this, replacing the dropdown menu sizes for direct link sizes. I need to premake  a Buy Now link like I showed in a prev post with each size included, so i have 4 buy now buttons, one for each size like this: <a href="http://ww6.aitsafe.com/cf/add.cfm?userid=88166920&product={$product['band_name']} - {$product['product_name']} ({$product['category_name']}) Small &price={$product['product_price']}">Small - Buy Now</a> <a href="http://ww6.aitsafe.com/cf/add.cfm?userid=88166920&product={$product['band_name']} - {$product['product_name']} ({$product['category_name']}) Medium &price={$product['product_price']}">Medium - Buy Now</a> <a href="http://ww6.aitsafe.com/cf/add.cfm?userid=88166920&product={$product['band_name']} - {$product['product_name']} ({$product['category_name']}) Large &price={$product['product_price']}">Large - Buy Now</a> <a href="http://ww6.aitsafe.com/cf/add.cfm?userid=88166920&product={$product['band_name']} - {$product['product_name']} ({$product['category_name']}) Extra Large &price={$product['product_price']}">Extra Large - Buy Now</a> Obviosuly the above requires no php and is a very basic way of doing it. Now if like before, certain buy now links dissapear when I size has a value of 0 in the prod_stock table, that would be fantastic!! For example: this is my prod_stock table: stock_id_________product_id__________size_________stock Ok.. for example, something like this would be great: IF product_id has size "small" in the "size" field, then display Small - Buy Now link IF it has "stock" greater then 0: <a href="http://ww6.aitsafe.com/cf/add.cfm?userid=88166920&product={$product['band_name']} - {$product['product_name']} ({$product['category_name']}) Small &price={$product['product_price']}">Small - Buy Now</a> OR if it has size "small" in the "size" field but with 0 stock then show instead: Currently Out Of Stock And just loop it all so it checks if theres a medium, large, and extra large size in the size field and either output the buy now link or the out of stock link depending if stock is greater then 0 BUT, if there is no size in the "size" field, which will happen when a product only has one fit (eg. hat) or not nessessary (calender/poster), then this buy now link with no size in the link should appear (if stock greater then 0): <a href="http://ww6.aitsafe.com/cf/add.cfm?userid=88166920&product={$product['band_name']} - {$product['product_name']} ({$product['category_name']})&price={$product['product_price']}">Buy Now</a> and again, if no stock, then this message: Currently Out Of Stock ------------- That probably sounds complicated but hopefully isn't too hard!! Can anyone help me to do the above?

How would I go about with doing that.. AJAX is something I haven't heard of. Otherise, I'm just going to need to think how I can get round this. Either way, what you've done for me is great!

Brilliant!! One last thing, you may notice at the bottom of my code this "buy now" link which goes to my checkout: <a href="http://ww6.aitsafe.com/cf/add.cfm?userid=88166920&product={$product['band_name']} - {$product['product_name']} ({$product['category_name']})&price={$product['product_price']}">Buy Now</a> If you go to that link you can see it nicely echos the band product, band name, prod cat and product price. Is there a way by whatever is selected i the size dropdown gets echoed, so I can add the echoed size into the link above?

thanks barand, getting better and no errors, but isn't showing the dropdown menu? I definately have put some stock in the database. Is there something wrong with the code?

I'm getting this error: Fatal error: Call to undefined function: getsizeselect() in /home/rockrag/public_html/details4.php on line 36 which is this line:   $dropdown = getSizeSelect($product['product_id']);        ## get the text for the dropdown any ideas how to fix this?

Bit confused still! I think what I'm having trouble is intergrating the new sql query with my stock with my old sql query when asking for my product table data etc.. Could you tell me how i merge the 2 queries together - the existing one with the product/categorie tables and the new stock table?

I'm a big hopeless at this :/ How can I intergrate the above code you gave barand with my existing code: [code] $cat = $_GET['id']; $sql = "SELECT p.product_name, p.product_price, b.band_name, c.category_name          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   <table>     <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><a href="http://ww6.aitsafe.com/cf/add.cfm?userid=88166920&product={$product['band_name']} - {$product['product_name']} ({$product['category_name']}) $size &price={$product['product_price']}">Buy Now</a></td>     </tr>   </table>   <br><br> HTML; } [/code] When I do it I get the select box come up with nothing in and visible php code! Again, any help is much appeciated. ;)