Filtering results with select lists

[rcouser]

Hello,

I am trying to use select lists to filter results from the database, let's say all manufacturer under "JCB" if somebody just chooses manufacturer and leaves "Type" or "Condition" unselected. In other words the filter should scale down as one selects deeper, so if only "New" is selected then that bring up just "New" Results, and if one chooses "New" and "JCB" it should gather as such. If I leave any empty it also brings all entries since it's reading the url as "type=" and then gets bunch of records...

 

JavaScript

<script type="text/javascript">
//<![CDATA[
function updateTable(oForm, sFieldname, sEntireUrl)
{
  window.location.href='machines.php?' + sFieldname + '=' + oForm.value + sEntireUrl
}
//]]>
</script>

 

Select lists

<div id="machines-search">
    <form>
    <label for="status">NEW / USED:</label>
    <select id="status" name="status" onchange="updateTable(this, 'status', '')">
    <option value="ALL"> - ALL - </option><option value="New">New</option><option value="Used">Used</option>
    </select>

    <label for="status">MANUFACTURER:</label>
    <select id="manufacturer" name="manufacturer" onchange="updateTable(this, 'manufacturer', '')">
    <option value="ALL"> - ALL - </option><option value="54">Atlas Capco</option><option value="29">Belle</option><option value="8">Benford</option><option value="33">BMW</option><option value="11">Bomag</option><option value="42">Case</option><option value="44">Cat</option><option value="49">Daewoo</option><option value="21">Hamm</option><option value="35">Hanix</option><option value="4">Hitachi</option><option value="50">Ifor Williams</option><option value="31">Ingersoll Rand</option><option value="1">JCB</option><option value="10">John Deere</option><option value="5">Komatsu</option><option value="6">Kubota</option><option value="30">Lifton</option><option value="14">Manitou</option><option value="55">Nissan</option><option value="17">Pike</option><option value="34">SMC</option><option value="2">Takeuchi</option><option value="22">Terex</option><option value="16">Thomas</option><option value="7">Thwaites</option><option value="23">Volvo</option><option value="3">Yanmar</option>
    </select>

    <label for="status">TYPE:</label>
    <select id="product" name="product" onchange="updateTable(this, 'product', '')">
    <option value="ALL"> - ALL - </option><option value="12">Air Master</option><option value="23">ATV</option><option value="14">Cherrypicker</option><option value="15">Compressors</option><option value="1">Diggers</option><option value="13">Dump Trailer</option><option value="2">Dumpers</option><option value="7">Forklifts</option><option value="16">Jeep - 4x4</option><option value="24">PickUp</option><option value="4">Rollers</option><option value="8">Skidsteer</option><option value="6">Telleporters</option><option value="3">Tractors</option><option value="9">Traffic Lights</option><option value="22">Trailer</option>
    </select>
    </form>
</div>

 

Table Date

<table class="sortable-onload-1 rowstyle-alt colstyle-alt no-arrow">
                  <thead>
                    <tr>
                      <th></th>
                              <th>Manufacturer</a></th>

                              <th>Type</a></th>
                              <th>Year</a></th>
                              <th>Hrs</a></th>
                              <th>Price</a></th>
                    </tr>
                  </thead>
                  <tbody>

                                          <tr class="">
                            <td class="machine-thumb"><a href="machines.php?ref=18"><img src="machines/thumbs/2008-09-25-1702_AUGUST-11-08_013.jpg" alt="John Deere 6930"  title="John Deere 6930" width="82" height="62"></a></td>
                            <td><a href="machines.php?ref=18">Atlas Capco</a></td>
                            <td><a href="machines.php?ref=18">XAS47</a></td>
                            <td><a href="machines.php?ref=18">2005</a></td>
                            <td><a href="machines.php?ref=18">660</a></td>
                            <td class="price-text"><a href="machines.php?ref=18">£4,000.00</a></td>

                        </tr>
                                            <tr class="alt">
                            <td class="machine-thumb"><a href="machines.php?ref=19"><img src="machines/thumbs/2008-09-25-1709_april-22-2008_0181.jpg" alt="John Deere 6930"  title="John Deere 6930" width="82" height="62"></a></td>
                            <td><a href="machines.php?ref=19">Belle</a></td>
                            <td><a href="machines.php?ref=19">761</a></td>
                            <td><a href="machines.php?ref=19">1999</a></td>
                            <td><a href="machines.php?ref=19">0</a></td>

                            <td class="price-text"><a href="machines.php?ref=19">£7,000.00</a></td>
                        </tr>
                                            <tr class="">
                            <td class="machine-thumb"><a href="machines.php?ref=20"><img src="machines/thumbs/2008-09-26-1613_13-04-07_0191.jpg" alt="John Deere 6930"  title="John Deere 6930" width="82" height="62"></a></td>
                            <td><a href="machines.php?ref=20">Benford</a></td>
                            <td><a href="machines.php?ref=20">MBR71HE</a></td>
                            <td><a href="machines.php?ref=20">2002</a></td>

                            <td><a href="machines.php?ref=20">337</a></td>
                            <td class="price-text"><a href="machines.php?ref=20">£2,500.00</a></td>
                        </tr>
                                            <tr class="alt">
                            <td class="machine-thumb"><a href="machines.php?ref=21"><img src="machines/thumbs/2008-09-26-1616_26-11-07_0101.jpg" alt="John Deere 6930"  title="John Deere 6930" width="82" height="62"></a></td>
                            <td><a href="machines.php?ref=21">Benford</a></td>
                            <td><a href="machines.php?ref=21">PS3000</a></td>

                            <td><a href="machines.php?ref=21">2003</a></td>
                            <td><a href="machines.php?ref=21">1019</a></td>
                            <td class="price-text"><a href="machines.php?ref=21">£7,500.00</a></td>
                        </tr>
                                            <tr class="">
                            <td class="machine-thumb"><a href="machines.php?ref=22"><img src="machines/thumbs/2008-09-26-1618_26-11-07_010.jpg" alt="John Deere 6930"  title="John Deere 6930" width="82" height="62"></a></td>
                            <td><a href="machines.php?ref=22">Benford</a></td>

                            <td><a href="machines.php?ref=22">PS3000</a></td>
                            <td><a href="machines.php?ref=22">2003</a></td>
                            <td><a href="machines.php?ref=22">741</a></td>
                            <td class="price-text"><a href="machines.php?ref=22">£7,500.00</a></td>
                        </tr>
                                      </tbody>
                </table>

 

PHP to select machines

// select all machines
	$sql = 'SELECT machine_id, machine_model, machine_year, machine_hours, machine_price, manufacturer_name, machine_image1
		FROM bo_machines, bo_manufacturers, bo_products
		WHERE bo_machines.product_id = bo_products.product_id
		AND bo_machines.manufacturer_id = bo_manufacturers.manufacturer_id';
	$result = @ $conn->query($sql);
	if (!$result) {
	header('Location: http://localhost/test/error.php');
	exit;
	}
	$machines = $result->fetch_assoc();