How do I filter a recordset?

[jmcc]

Hi

 

I wrote a small search engine for my property site. All the results are displayed fine, I would now like to give the client the option filter results based on the price. How can I filter the results from a min price max price variable?

 

Check bold portion of code.

 

Here is what I have so far:

 

<?php>

 

///////////////Generate recordset from search engine /////////////////////

mysql_select_db($database_prop, $prop);

 

if ($key != "") {

  $query_results = "SELECT * FROM pns_prop.seller WHERE seller.type LIKE '$key' OR seller.province LIKE '$key' OR seller.city LIKE '$key' OR seller.suburb LIKE '$key' OR seller.id LIKE '$key' OR seller.tell LIKE '$key' OR seller.fax LIKE '$key' OR seller.cell LIKE '$key' OR seller.email LIKE '$key' OR seller.agency_name LIKE '$key' OR seller.seller_type LIKE '$key'OR seller.des LIKE '&key'";

}

elseif ($subu != "Select One") {

  $query_results = "SELECT * FROM pns_prop.seller WHERE seller.type LIKE '$type' AND seller.province LIKE '$prov' AND seller.city LIKE '$cit' AND seller.suburb LIKE '$subu'";

}

elseif ($cit != "Select One") {

  $query_results = "SELECT * FROM pns_prop.seller WHERE seller.type LIKE '$type' AND seller.province LIKE '$prov' AND seller.city LIKE '$cit'";

}

elseif ($prov != "Select One") {

    $query_results = "SELECT * FROM pns_prop.seller WHERE seller.type LIKE '$type' AND seller.province LIKE '$prov'";

}

elseif ($type != "Select One") {

    $query_results = "SELECT * FROM seller WHERE seller.type LIKE '$type'";

}

 

///////////////Check price selected //////////////////////////

if ($minprice != "Select Price") {

  $query_results = Everything from recordset between $minprice and $maxprice; //something like this

}

 

///////////// Display only 10 records per page //////////////////

$maxRows_results = 10;

$pageNum_results = 0;

if (isset($_GET['pageNum_results'])) {

  $pageNum_results = $_GET['pageNum_results'];

}

$startRow_results = $pageNum_results * $maxRows_results;

 

///////////////////////Assign DB values to RS ///////////////////////////////

$row_results = mysql_fetch_assoc($results);

$query_limit_results = sprintf("%s LIMIT %d, %d", $query_results, $startRow_results, $maxRows_results);

$results = mysql_query($query_limit_results, $prop) or die(mysql_error());

 

<?>

[monkeytooth]

if($minprice <= $selectprice){echo $info;}

 

set the $selectprice through a $_POST/GET[''] type variable. Query the database. Put the above in a while loop.. not likely the most optimized or efficient way to do what you want but it could work in concept.

 

[jmcc]

I solved it by setting the default vaue for min price to 0 and max price to 100000000 then when you refine the search by price then the new value get assigned to the variable. not quite sure if im suppose to program like that but it works.

 

mysql_select_db($database_prop, $prop);

 

if ($key != "") {

  $query_results = "SELECT * FROM pns_prop.seller WHERE seller.type LIKE '$key' OR seller.province LIKE '$key' OR seller.city LIKE '$key' OR seller.suburb LIKE '$key' OR seller.id LIKE '$key' OR seller.tell LIKE '$key' OR seller.fax LIKE '$key' OR seller.cell LIKE '$key' OR seller.email LIKE '$key' OR seller.agency_name LIKE '$key' OR seller.seller_type LIKE '$key'OR seller.des LIKE '&key' AND seller.price BETWEEN '$minp' AND '$maxp' ORDER BY seller.price ASC";

}

elseif ($subu != "Select One") {

  $query_results = "SELECT * FROM pns_prop.seller WHERE seller.type LIKE '$type' AND seller.province LIKE '$prov' AND seller.city LIKE '$cit' AND seller.suburb LIKE '$subu' AND seller.price BETWEEN '$minp' AND '$maxp' ORDER BY seller.price ASC";

}

elseif ($cit != "Select One") {

  $query_results = "SELECT * FROM pns_prop.seller WHERE seller.type LIKE '$type' AND seller.province LIKE '$prov' AND seller.city LIKE '$cit' AND seller.price BETWEEN '$minp' AND '$maxp' ORDER BY seller.price ASC";

}

elseif ($prov != "Select One") {

    $query_results = "SELECT * FROM pns_prop.seller WHERE seller.type LIKE '$type' AND seller.province LIKE '$prov' AND seller.price BETWEEN '$minp' AND '$maxp' ORDER BY seller.price ASC";

}

elseif ($type != "Select One") {

    $query_results = "SELECT * FROM seller WHERE seller.type LIKE '$type' AND seller.price BETWEEN '$minp' AND '$maxp' ORDER BY seller.price ASC";

}