Query Help - Drop down menus

[spectsteve7]

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.

[Orio]

[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.

[Anidazen]

If I understood you correctly, you might need to modify what Orio said slightly, to select where height < x or > x.

[craygo]

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

[spectsteve7]

How could I do this in the drop down?

Less than 1"
Less than 2" more than 1"
Less than 3" more than 2"

And so on. And can the less number be dynamic too?

[Anidazen]

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.

[doni49]

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;
}
?>