Help needed with database/dropdown menu!!

[downfall]

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?

[craygo]

well since php is server side you would have to have a couple if statements to pull the information out.

Example you would have to have a spot where the user pics the product he/she wants. They submit that then you could dynamically create the size dropdown from there.

Will post code for you if this will be ok. Otherwise you would have to use java or ajax to do it without refreshing the page

Ray

[downfall]

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

[craygo]

OK i put this together quite simple. Your forms and things needed for your page you will have to add. This is what I used for testing so some things will be omitted for your needs. But basically the middle part of the code is what you need. You will also have to replace GET with POST

If you want complete then post your page code and I will insert this for you

[code]<?php
    require('config.php');
    include('includes/mysql.php');
if(isset($_GET['buy'])){
  // Update query Here

  echo "sql here";
} else {
  if(isset($_GET['choose'])){
    // After the product is selected this will show the product and give the available sizes
    $prodid = $_GET['productid'];
    $sql = "SELECT * FROM products WHERE product_id = '$prodid'";
      $res = mysql_query($sql) or die (mysql_error());
      $r = mysql_fetch_assoc($res);
      print ''.$r['name'].' -- '.$r['description'].'<br>';
/************* This selects the available sizes ************************/
      $size_sql = "SELECT size, stock_level FROM products LEFT JOIN prod_size ON products.product_id = prod_size.product_id
                  WHERE STOCK_LEVEL > 0 AND products.product_id = '$prodid'";
        $sres = mysql_query($size_sql) or die (mysql_error());
          echo '<form method=get action="'.$_SERVER['PHP_SELF'].'">';
        echo "<select name=product>
              <option>--Available Sizes</option>";
        while($s = mysql_fetch_array($sres)){
          echo '<option value='.$s['size'].'>'.$s['size'].'</option>';
        }
        echo "</select><br>
              <input type=submit name=buy value=BUY>";

  } else {
    // this is the initial list to select the product the customer wants
    $sql = "SELECT * FROM products";
      $res = mysql_query($sql) or die (mysql_error());
        while($r=mysql_fetch_array($res)){
          print '<a href="'.$_SERVER['PHP_SELF'].'?productid='.$r['product_id'].'&choose=yes">'.$r['name'].'</a><br>';
        }
  }
}
?>[/code]

Ray

[downfall]

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]

[craygo]

Hope this works for you. Quick question, why all the fields in the value part for the size???
[code]<html>
<head>
<title>Product Details</title>
<body>
<?php

#set variables For Database
require('config.php');
include('includes/mysql.php');
$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>
<?
}
?>
</body>
</html>[/code]

i had to modify something please refresh


Ray

[downfall]

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.

[printf]

Error in your code where you assign [b]$cat[/b] .... (missing right side * ' *)

[code]$cat = $_GET['id'];
----------------^[/code]


me!

[downfall]

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

[downfall]

Anyone?

[downfall]

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

[craygo]

Sorry off for the weekend, but this should work for he out of stock button. Will post whole code again.
[code]<html>
<head>
<title>Product Details</title>
<body>
<?php

#set variables For 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 "

{$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());
  $stock_count = mysql_num_rows($res);
    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>";
    }
        echo "</SELECT><br><br>";
        if($stock_count > 0){
          echo '<input TYPE="submit" value="Buy Now" border="0" NAME="Order">';
        } else {
          echo '<input TYPE="submit" value="Out of Stock" border="0" NAME="Order" disabled>';
        }
    echo "</form>";
}
?>
</body>
</html>[/code]

Ray