Solved

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

Posted on 2008-10-28
10
231 Views
Last Modified: 2013-12-13
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
Comment
Question by:rlb1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 3

Expert Comment

by:raminhos
ID: 22826957
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22827003
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
 

Author Comment

by:rlb1
ID: 22827058
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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 3

Expert Comment

by:raminhos
ID: 22827274
Try change mysql_fetch_assoc to mysql_fetch_array
0
 

Author Comment

by:rlb1
ID: 22827445
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22827536
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
 
LVL 39

Expert Comment

by:Roger Baklund
ID: 22827544
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
 

Author Comment

by:rlb1
ID: 22827565
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
 
LVL 39

Accepted Solution

by:
Roger Baklund earned 500 total points
ID: 22827614
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
 

Author Closing Comment

by:rlb1
ID: 31510970
Thanks!!  I appreciate your help!!
0

Featured Post

Containers and Docker for Everyone

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question