Solved

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

Posted on 2008-10-28
10
229 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to dynamically set the form action using jQuery.
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…

737 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