• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

How do I create a select statement with a select box?

How do I create a select statement with a select box?  
Database columns are Category and SubCategory

I cannot understand what I am doingwrong.  Any help would be appreciated.

<div id="intcategories">
           
            <div id="subcats"><form method="get" action="index.php"><select name="category">
                    <?php

                    $sql = "SELECT category FROM ddcart_products WHERE category = '$data'";
                    $result = mysql_query($sql);
                    echo '<option value="">- Pick a Category -</option>s';
                    while($data = mysql_fetch_assoc($result)) {
                            echo '<option value="'.$data['category'].'">'.$data['category'].'</option>';
                            //show children
                            $sql2 = "SELECT subcategory FROM ddcart_products WHERE parent = '".$data['category']."'";
                            $result2 = mysql_query($sql2);
                            while($data2 = mysql_fetch_assoc($result2)) {
                                    echo '<option value="'.$data2['subcategory'].'"> - '.$data2['subcategory'].'</option>';
                            }
                    }
            ?>
            </select> <input type="submit" value="Go"></form></div>
            <div class="clear" style="height: 1px;"></div>
        </div>
0
rlb1
Asked:
rlb1
  • 4
  • 4
  • 2
1 Solution
 
raminhosCommented:
Try
<div id="intcategories">
  <div id="subcats"><form method="get" action="index.php">
    <select name="category">
<?php
 
$sql = "SELECT category FROM ddcart_products WHERE category = '$data'";
$result = mysql_query($sql);
echo '<option value="">- Pick a Category -</option>s';
while($data = mysql_fetch_assoc($result)){
  extract($data);
  $res .= "<option value = '$category'>$category</option>";
  $sql2 = "SELECT subcategory FROM ddcart_products WHERE parent = '".$data['category']."'";
  $result2 = mysql_query($sql2);
    while($data2 = mysql_fetch_assoc($result2)) {
      extract($data2);
      $res .= "<option value = '$subcategory'>$subcategory</option>";
    }
  }
  echo $res;
?>
</select> <input type="submit" value="Go"></form></div>
<div class="clear" style="height: 1px;"></div>
</div>

Open in new window

0
 
Roger BaklundCommented:
The initial sql statement seems a bit odd...

 $sql = "SELECT category FROM ddcart_products WHERE category = '$data'";

Why do you need to ask the database for the category value that you allready know?

Are you trying to make a "normal" select box, or one with two levels: categories and sub categories?

Can you show the definition of the ddcart_products table? Execute the following SQL statement and post the result:
SHOW CREATE TABLE ddcart_products

Open in new window

0
 
rlb1Author Commented:
Thanks for your help!!  The solution unfortunately did not work.  I cannot figure out what is missing.

The URL is http://www.directforless.net/index-works.php

Here is the entire code:

<?php

        //session_id($sid);

        session_start();
        if(!isset($_SESSION['ddc']['cartqty'])) $_SESSION['ddc']['cartqty'] = 0;
        if(!isset($_SESSION['ddc']['price'])) $_SESSION['ddc']['price'] = 0.00;

        include('includes/config.php');
        include('includes/functions.php');
        include('includes/header.php');

        //set category
        if(isset($_GET['cat'])) {
                $cat = $_GET['cat'];
        } else {
                $cat = 0;
        }

?>

        <div id="productlisting">

        <div id="intcategories">
  <div id="subcats">

  <form method="get" action="index.php">
    <select name="category">
<?php

$sql = "SELECT category FROM ddcart_products WHERE category = '$data'";
$result = mysql_query($sql);
echo '<option value="">- Pick a Category -</option>s';
while($data = mysql_fetch_assoc($result)){
  extract($data);
  $res .= "<option value = '$category'>$category</option>";
  $sql2 = "SELECT subcategory FROM ddcart_products WHERE parent = '".$data['category']."'";
  $result2 = mysql_query($sql2);
    while($data2 = mysql_fetch_assoc($result2)) {
      extract($data2);
      $res .= "<option value = '$subcategory'>$subcategory</option>";
    }
  }
  echo $res;
?>
</select> <input type="submit" value="Go"></form></div>
<div class="clear" style="height: 1px;"></div>
</div>


        <div class="clear" style="height: 1px;"></div>

    <?php

            //number of items per page
            $numitems = getOptionValue('itemsperpage');

            if(isset($_GET['page'])) {
                    $page = $_GET['page'];
                } else {
                        $page = 0;
                }
            //determine offset
            $offset = $page*$numitems;

                $sql = "SELECT * FROM ddcart_products WHERE cat = '".$cat."' LIMIT ".$offset.", ".$numitems;
                $result = mysql_query($sql);
                $i = 1;
                while($data = mysql_fetch_assoc($result)) {
                        ?>
            <div class="productblock draggablediv" product="<?php echo $data['id'].'|'.$data['name'].'|'.$data['price']; ?>">
                    <div class="pbimage">  <img width=150 src="<?php echo $data['photourl']; ?>">   <!--<?php echo displayProductImage($data['id'], 'thumb'); ?>--></div>
                <div class="pbinfo"><a href="productdetail.php?product=<?php echo $data['id']; ?>" class="detailslink">View Details</a></div>
                <div class="pbtitle"><?php echo stripslashes($data['name']); ?></div>
                <div class="pbprice"><?php echo "$".number_format($data['price'], 2, '.', ','); ?></div>
            </div>
            <?php

            if(($i % 4) == 0) echo '<div class="clear"></div>';

            $i++;


                }

                echo '<div class="clear"></div><div id="pagenums" style="text-align: center;">';

                //total number of pages
                $sql = "SELECT * FROM ddcart_products WHERE cat = '".$cat."'";
                $result = mysql_query($sql);
                $numpages = floor(mysql_num_rows($result)/$numitems);

                for($i = 1; $i <= $numpages; $i++) {
                        if($page == $i) {
                                echo '<strong><span style="font-size: 12px; text-decoration: underline;"><a href="index.php?cat='.$cat.'&page='.$i.'">'.$i.'</a></strong>';
                        } else {
                                echo '<a href="index.php?cat='.$cat.'&page='.$i.'">'.$i.'</a>';
                        }

                }

                echo '</div>';

        ?>

            <div class="clear"></div>
    </div>

    <div id="shoppingcart" class="drop">

                <img src="assets/images/shoppingcart.gif" id="shoppingcarticon" />

        <div style="margin-top: 5px;" id="parentlist">
          <b class="greyrounded">
          <b class="greyrounded1"><b></b></b>
          <b class="greyrounded2"><b></b></b>
          <b class="greyrounded3"></b>
          <b class="greyrounded4"></b>
          <b class="greyrounded5"></b></b>

          <div class="greyroundedfg" id="list">

                        <div id="toplistrow">
                    <div id="cell0"></div>
                    <div id="cell1">Product</div>
                    <div id="cell2">Qty</div>
                    <div id="cell3">Price</div>
                    <div class="clear"></div>
            </div>

            <div id="listitems">

                <?php echo displayProducts($_SESSION['ddc']['productsincart']); ?>

            </div>

          </div>

          <b class="greyrounded">
          <b class="greyrounded5"></b>
          <b class="greyrounded4"></b>
          <b class="greyrounded3"></b>
          <b class="greyrounded2"><b></b></b>
          <b class="greyrounded1"><b></b></b></b>
        </div>

        <div style="margin-top: 5px; text-align: left;">
          <b class="greyrounded">
          <b class="greyrounded1"><b></b></b>
          <b class="greyrounded2"><b></b></b>
          <b class="greyrounded3"></b>
          <b class="greyrounded4"></b>
          <b class="greyrounded5"></b></b>

          <div class="greyroundedfg" style="padding-left: 5px;">
                        <strong>Products In Cart:</strong> <span id="cartqty"><?php echo $_SESSION['ddc']['cartqty']; ?></span>
          </div>

          <b class="greyrounded">
          <b class="greyrounded5"></b>
          <b class="greyrounded4"></b>
          <b class="greyrounded3"></b>
          <b class="greyrounded2"><b></b></b>
          <b class="greyrounded1"><b></b></b></b>
        </div>

        <div style="margin-top: 5px; text-align: left;">
          <b class="greyrounded">
          <b class="greyrounded1"><b></b></b>
          <b class="greyrounded2"><b></b></b>
          <b class="greyrounded3"></b>
          <b class="greyrounded4"></b>
          <b class="greyrounded5"></b></b>

          <div class="greyroundedfg" style="padding-left: 5px;">
                        <strong>Cart Subtotal:</strong> $<span id="carttotal"><?php echo $_SESSION['ddc']['price']; ?></span>
          </div>

          <b class="greyrounded">
          <b class="greyrounded5"></b>
          <b class="greyrounded4"></b>
          <b class="greyrounded3"></b>
          <b class="greyrounded2"><b></b></b>
          <b class="greyrounded1"><b></b></b></b>
        </div>

        <a href="checkout.php"><img src="assets/images/checkout.gif" border="0" style="margin-top: 5px;" onmouseover="this.src='assets/images/checkout_on.gif'" onmouseout="this.src='assets/images/checkout.gif'" /></a>

    </div>


<?php include('includes/footer.php'); ?>
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
raminhosCommented:
Try change mysql_fetch_assoc to mysql_fetch_array
0
 
rlb1Author Commented:
Thanks for your help!!!

cxr,  I have 5000 products in several different categories.  This is a way to search by category and/or subcategory.  The category and subcategory data is in the same table as the product data.

CREATE TABLE `ddcart_products` (
 `id` int(11) NOT NULL auto_increment,
 `name` varchar(255) NOT NULL,
 `sku` varchar(255) NOT NULL default '',
 `price` decimal(5,2) NOT NULL default '0.00',
 `description` text NOT NULL,
 `cat` int(11) NOT NULL default '0',
 `active` int(11) NOT NULL default '0',
 `stock` int(11) NOT NULL default '0',
 `weight` decimal(5,2) NOT NULL default '0.00',
 `height` decimal(4,2) NOT NULL default '0.00',
 `width` decimal(4,2) NOT NULL default '0.00',
 `length` decimal(4,2) NOT NULL default '0.00',
 `ogroup` int(11) NOT NULL default '0',
 `photourl` varchar(255) NOT NULL,
 `category` varchar(75) NOT NULL,
 `subcategory` varchar(75) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=75 DEFAULT CHARSET=latin1

raminhos,  mysql_fetch_array did not work.
0
 
Roger BaklundCommented:
I think this should do it:
$sql = "SELECT DISTINCT category FROM ddcart_products";
$result = mysql_query($sql) or die('Error: '.mysql_error());
echo '<option value="">- Pick a Category -</option>s';
while($data = mysql_fetch_assoc($result)) {
  echo '<option value="'.$data['category'].'">'.$category.'</option>';
  $sql2 = "SELECT DISTINCT subcategory FROM ddcart_products 
           WHERE category = '{$data['category']}'";
  $result2 = mysql_query($sql2) or die('Error: '.mysql_error());
  if(mysql_num_rows($result2)>0) {
    echo '<optgroup>';
    while($data2 = mysql_fetch_assoc($result2)) 
      echo '<option value=".$data2['subcategory'].'">'.
            $data2['subcategory'].'</option>';
    }
    echo '</optgroup>';
  }
}

Open in new window

0
 
Roger BaklundCommented:
Oops, a couple of bugs there... trying again:
$sql = "SELECT DISTINCT category FROM ddcart_products";
$result = mysql_query($sql) or die('Error: '.mysql_error());
echo '<option value="">- Pick a Category -</option>s';
while($data = mysql_fetch_assoc($result)) {
  echo '<option value="'.$data['category'].'">'.$category.'</option>';
  $sql2 = "SELECT DISTINCT subcategory FROM ddcart_products 
           WHERE category = '{$data['category']}'";
  $result2 = mysql_query($sql2) or die('Error: '.mysql_error());
  if(mysql_num_rows($result2)>0) {
    echo '<optgroup>';
    while($data2 = mysql_fetch_assoc($result2)) 
      echo '<option value="'.$data2['subcategory'].'">'.
            $data2['subcategory'].'</option>';
    echo '</optgroup>';
  }
}

Open in new window

0
 
rlb1Author Commented:
cxr,
Thank You!!  It does not work quite yet...  We are close.  Please see http://www.directforless.net/index-works.php.
I really appreciate your help!!
0
 
Roger BaklundCommented:
Try this:
$sql = "SELECT DISTINCT category FROM ddcart_products";
$result = mysql_query($sql) or die('Error: '.mysql_error());
echo '<option value="">- Pick a Category -</option>';
while($data = mysql_fetch_assoc($result)) {
  echo '<option value="'.$data['category'].'">'.$data['category'].'</option>';
  $sql2 = "SELECT DISTINCT subcategory FROM ddcart_products 
           WHERE category = '{$data['category']}'";
  $result2 = mysql_query($sql2) or die('Error: '.mysql_error());
  if(mysql_num_rows($result2)>0) {
    echo '<optgroup>';
    while($data2 = mysql_fetch_assoc($result2)) 
      echo '<option value="'.$data2['subcategory'].'">'.
            $data2['subcategory'].'</option>';
    echo '</optgroup>';
  }
}

Open in new window

0
 
rlb1Author Commented:
Thanks!!  I appreciate your help!!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now