Solved

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

Posted on 2008-10-28
10
222 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What's wrong with this PDO query? 5 28
Ajax and PHP 4 31
Giant ibd file for our biggest table on mysql 2 18
Generate PDF from MySQL using PHP 3 26
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. …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

820 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