Jump to content

Query Help - Drop down menus


spectsteve7

Recommended Posts

I'm have a SQL database set up and need to create a selection tool for my items and I need some help. My database consists of 6 fields (id, name, category, productnum, width, & height). I'm familiar with creating a pull down menu/jump down menu for listing all records. I want to a query to select by size. Basically have a drop down where you can select by width, Less than 1", 2", 3"... and have next to that another drop down for the height doing the same thing. Once you select both sizes you click a go button and it  displays all records based on those queries.

Hopefully someone understands what I'm looking to do. Any help or pointing to a tutorial would be appreciated.
Link to comment
Share on other sites

[code]<?php

if(!isset($_POST['submit']))
{
//Connect to db here
$result1=mysql_query("SELECT width FROM `table_name`");
$result2=mysql_query("SELECT height FROM `table_name`");

echo "<form action=\"".$_SERVER['PHP_SELF']."\" method=\"POST\">"

echo "<select name=\"w\">";
while ($row1 = mysql_fetch_array($result1))
echo "<option value=\"".$row1['width']."\">".$row1['width']."</option>
echo "</select>";

echo "<select name=\"h\">";
while ($row2 = mysql_fetch_array($result2))
echo "<option value=\"".$row2['height']."\">".$row2['height']."</option>";
echo "</select>";

echo "</form>";
}

else
{
$result = mysql_query("SELECT * FROM `table_name` WHERE height<='$_POST['h']' AND width<='$_POST['width']'");
if (mysql_num_rows($result) == 0) echo "No matching products found.";
else
{
echo "<table border='1'>";
echo "<tr><td>ID</td><td>Category</td><td>Product #</td></<td>height</td></<td>width</td></tr>";
while ($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>".$row['id']."</td>";
echo "<td>".$row['category']."</td>";
echo "<td>".$row['productnum']."</td>";
echo "<td>".$row['hieght']."</td>";
echo "<td>".$row['width']."</td>";
echo "</tr>";
}
echo "</table>";
}
}

?>[/code]

Orio.
Link to comment
Share on other sites

ok here is something quick
[code]<?php
if(isset($_POST['submit'])){
$width = $_POST['width'];
$height = $_POST['height'];
$sql = "SELECT * FROM tablename WHERE width < '$width' AND height < '$height'";
  $res = mysql_query($sql) or die (mysql_error());
    while($r=mysql_fetch_assoc($res)){
      echo "".$r['name']." -- ".$r['catagory']."<br>";
    }
} else {
$width = array(1,2,3,4,5);
$height = array(1,2,3,4,5);

echo "<form action=\"".$_SERVER['PHP_SELF']."\" method=POST>";
echo "Width:&nbsp;&nbsp;&nbsp;";
echo "<select name=width>";
foreach($width as $widthint){
  echo "<option value=$widthint>$widthint</option>";
}
echo "</select>";
echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";
echo "Height:&nbsp;&nbsp;&nbsp;";
echo "<select name=height>";
foreach($height as $heightint){
  echo "<option value=$heightint>$heightint</option>";
}
echo "</select>";
echo "<br><input type=submit name=submit value=Submit>";
echo "</form>";
}
?>[/code]

Ray
Link to comment
Share on other sites

Steve, the line that matters in doing that stuff is this one, quoted from Craygo's script:
[i]$sql = "SELECT * FROM tablename WHERE width < '$width' AND height < '$height'";[/i]

If we want it to be less than 1 then it becomes:
$sql = "SELECT * FROM tablename WHERE width < 1";

If we want it to become less than 3 and more than 2, it becomes:
$sql = "SELECT * FROM tablename WHERE width < 3 AND width > 2'";


That's the SQL statement that matters in this example.
Link to comment
Share on other sites

To make the query "dynamic", you have to "build" the query string:
<select name="width">
  <option value = "0">Less than 1"</option>
  <option value = "1">Less than 2" but greater than 1"</option>
  <option value = "2">Greater than 2"</option>
</select>
<select name="height">
  <option value = "0">Less than 1"</option>
  <option value = "1">Less than 2" but greater than 1"</option>
  <option value = "2">Greater than 2"</option>
</select>


<?php
$sql = "SELECT * FROM tablename";
switch  $_POST['width'] {
  case 0:
    $sqlw =  "width < 1";
    break;
  case 1:
    $sqlw =  "width < 2 AND width > 1";
    break;
  case 2:
    $sqlw =  "width > 2";
    break;
}
switch  $_POST['height'] {
  case 0:
    $sqlh =  "height < 1";
    break;
  case 1:
    $sqlh =  "height < 2 AND height > 1";
    break;
  case 2:
    $sqlh =  "height > 2";
    break;
}
if ($sqlh OR $sqlw){ $sql .= " WHERE ";}
if ($sqlw){
  $sql .= $sqlw;
}
if ($sqlh){
  if ($sqlh){$sql .= " AND ";}
  $sql .= $sqlh;
}
?>
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.