Solved

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

Posted on 2008-10-28
10
201 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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
xpath sql query 2008 8 41
Alter an update query which rounds 7 29
SQL Statement to Update Email Domain 2 19
Getting same value for every field in SQL 2 10
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

930 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now