Page 1 of 1

Advanced search help

Posted: Fri Oct 27, 2006 4:25 am
by DevBob
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I have a series of drop-down menus which make up an advanced search form. I am having major problems understanding how to make the results page work though - I tried researching and testing stuff but I'm having no luck whatsoever. 

The search form code is straightforward enough:
[syntax="html"]
<form action="multiresults.php" method="post" name="frmSearch" target="_self" id="frmSearch">
<p>District<br />
<select name="district" id="district" style="width:160px;">
<option value="" selected="selected">All districts</option>
<option value="Nicosia">Nicosia</option>
<option value="Limassol">Limassol</option>
<option value="Larnaca">Larnaca</option>
<option value="Paphos">Paphos</option>
<option value="Famagusta">Famagusta</option>
</select>
</p>
<p>Type<br />
<select name="type" id="type" style="width:160px;">
<option value="" selected>All types</option>
<option value="house">House</option>
<option value="apartment">Apartment</option>
<option value="plot">Plot</option>
<option value="land">Land</option>
<option value="bungalow">Bungalows</option>
<option value="detached">Detached</option>
<option value="semi">Semi-detached</option>
<option value="villa">Villa</option>
<option value="terraced">Terraced</option>
<option value="penthouse">Penthouse</option>
<option value="duplex">Duplex</option>
<option value="maisonette">Maisonette</option>
<option value="groundfloor">Ground floor apartment</option>
<option value="agricultural">Agricultural</option>
<option value="commercial">Commercial</option>
<option value="industrial">Industrial</option>
<option value="mixedzone">Mixed zone</option>
<option value="residential">Residential</option>
</select>
</p>
<p>Bedrooms<br />
<select name="beds" id="beds" style="width:160px;">
<option value="" selected>Any beds</option>
<option value="1">0</option>
<option value="2">1</option>
<option value="3">2</option>
<option value="4">3</option>
<option value="5">4</option>
<option value="6">5</option>
<option value="7">6+</option>
<option value="8">studio</option>
</select></p>
<p>Price from<br />
<select name="price1" id="price1" style="width:160px;">
<option value="" selected>Any price</option>
<option value="10000">10,000</option>
<option value="20000">20,000</option>
<option value="30000">30,000</option>
<option value="40000">40,000</option>
<option value="50000">50,000</option>
<option value="60000">60,000</option>
<option value="70000">70,000</option>
<option value="80000">80,000</option>
<option value="90000">90,000</option>
<option value="100000">100,000</option>
<option value="150000">150,000</option>
<option value="200000">200,000</option>
<option value="250000">250,000</option>
<option value="300000">300,000</option>
<option value="350000">350,000</option>
<option value="400000">400,000</option>
<option value="450000">450,000</option>
<option value="500000">500,000</option>
<option value="550000">550,000</option>
<option value="600000">600,000</option>
<option value="700000">700,000</option>
<option value="800000">800,000</option>
<option value="900000">900,000</option>
<option value="1000000">1,000,000</option>
</select>
</p>
<p>Price to<br />
<select name="price2" id="price2" style="width:160px;">
<option value="" selected>Any price</option>
<option value="10000">10,000</option>
<option value="20000">20,000</option>
<option value="30000">30,000</option>
<option value="40000">40,000</option>
<option value="50000">50,000</option>
<option value="60000">60,000</option>
<option value="70000">70,000</option>
<option value="80000">80,000</option>
<option value="90000">90,000</option>
<option value="100000">100,000</option>
<option value="150000">150,000</option>
<option value="200000">200,000</option>
<option value="250000">250,000</option>
<option value="300000">300,000</option>
<option value="350000">350,000</option>
<option value="400000">400,000</option>
<option value="450000">450,000</option>
<option value="500000">500,000</option>
<option value="550000">550,000</option>
<option value="600000">600,000</option>
<option value="700000">700,000</option>
<option value="800000">800,000</option>
<option value="900000">900,000</option>
<option value="1000000">1,000,000</option>
<option value="1000000+">1,000,000+</option>
</select>
</p>
<p>
<input name="submit" type="image" id="submit" src="images/search.jpg" width="64" height="21" border="0" alt="Search" title="Search" />
</p>
</form>
For what it's worth the results page is this at the moment - it was made using Dreamweaver, with repeat region, but it's mainly the SQL that I can't figure out.[/syntax]

Code: Select all

<?php
$maxRows_rcsResult = 15;
$pageNum_rcsResult = 0;
if (isset($_GET['pageNum_rcsResult'])) {
  $pageNum_rcsResult = $_GET['pageNum_rcsResult'];
}
$startRow_rcsResult = $pageNum_rcsResult * $maxRows_rcsResult;

$colname_rcsResult = "1";
if (isset($_REQUEST['district'])) {
  $colname_rcsResult = (get_magic_quotes_gpc()) ? $_REQUEST['district'] : addslashes($_REQUEST['district']);
}
$colname_rcsResult2 = "1";
if (isset($_REQUEST['type'])) {
  $colname2_rcsResult = (get_magic_quotes_gpc()) ? $_REQUEST['type'] : addslashes($_REQUEST['type']);
}
mysql_select_db($database_connW, $connW);
$query_rcsResult = sprintf("SELECT * FROM realestate WHERE district = '%s' AND type = '%s'", $colname_rcsResult,$colname_rcsResult2);
$query_limit_rcsResult = sprintf("%s LIMIT %d, %d", $query_rcsResult, $startRow_rcsResult, $maxRows_rcsResult);
$rcsResult = mysql_query($query_limit_rcsResult, $connW) or die(mysql_error());
$row_rcsResult = mysql_fetch_assoc($rcsResult);

if (isset($_GET['totalRows_rcsResult'])) {
  $totalRows_rcsResult = $_GET['totalRows_rcsResult'];
} else {
  $all_rcsResult = mysql_query($query_rcsResult);
  $totalRows_rcsResult = mysql_num_rows($all_rcsResult);
}
$totalPages_rcsResult = ceil($totalRows_rcsResult/$maxRows_rcsResult)-1;

$queryString_rcsResult = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_rcsResult") == false && 
        stristr($param, "totalRows_rcsResult") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_rcsResult = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_rcsResult = sprintf("&totalRows_rcsResult=%d%s", $totalRows_rcsResult, $queryString_rcsResult);
?>
Any ideas anyone?
Thanks.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Fri Oct 27, 2006 4:31 am
by volka
What does it do? What is it supposed to do? Please be more specific.

Posted: Fri Oct 27, 2006 4:39 am
by Rovas
From what I read you want to make a search form in which the user selects the search parameters so you use a SQL statement that contains LIKE AND (or you can use OR, XOR depending on your needs).

Code: Select all

//after validating the inputed data
ypur_query= your_query . "WHERE columnX LIKE %" .$location  . "%  AND columnY LIKE %" .$typeOfHouse ."%"
I think you got the ideea.

Posted: Fri Oct 27, 2006 9:22 am
by DevBob
Hi Rovas

Yes, you are right, it's fairly straightforward in terms of it being a form with multiple choice dropdowns, and then a table is searched for any matches, or (LIKE). E.g. a property in Nicosia, with X bedrooms, price from, price to, type.

Refering to your code example, once I've captured the form data, do I assign them to variables (i.e. $location, $typeofhouse, etc.)? Then construct the SQL QUERY statement...

If the value captured is "" (empty) - it will search all. Is that correct?

I'll try this and let you know how I get on.
Thanks

Posted: Fri Oct 27, 2006 2:48 pm
by DevBob
My apologies feyd.

Posted: Fri Oct 27, 2006 5:24 pm
by RobertGonzalez
How comfortable are you with PHP? I only ask because you are going to want to take the user input from the $_POST array and check it to make sure it is appropriate (you know, like not empty, not letters where numbers should, not any potentially harmful SQL injection characters, etc). The is usually near the time the $_POST array vars are assigned to singular variables that are used throughout the script. Then those singular vars are used in your query, but only after they pass validation.

Almost there, but for one problem...

Posted: Fri Oct 27, 2006 7:31 pm
by DevBob
...ok, I think I've got it working now!

Here's the HTML form:

Code: Select all

<form action="multiresults.php" method="post" name="frmSearch" target="_self" id="frmSearch">
<p>District<br />
<select name="district" id="district" style="width:160px;">
<option value="ALL" selected="selected">All districts</option>
<option value="Nicosia">Nicosia</option>
<option value="Limassol">Limassol</option>
<option value="Larnaca">Larnaca</option>
<option value="Paphos">Paphos</option>
<option value="Famagusta">Famagusta</option>
</select>
</p>
<p>Type<br />
<select name="type" id="type" style="width:160px;">
<option value="ALL" selected>All types</option>
<option value="plot">Plot</option>
<option value="land">Land</option>
<option value="bungalow">Bungalows</option>
<option value="detached">Detached</option>
<option value="semi">Semi-detached</option>
<option value="villa">Villa</option>
<option value="terraced">Terraced</option>
<option value="penthouse">Penthouse</option>
<option value="duplex">Duplex</option>
<option value="maisonette">Maisonette</option>
<option value="groundfloor">Ground floor apartment</option>
<option value="agricultural">Agricultural</option>
<option value="commercial">Commercial</option>
<option value="industrial">Industrial</option>
<option value="mixedzone">Mixed zone</option>
<option value="residential">Residential</option>
</select>
</p>
<p>Bedrooms<br />
<select name="beds" id="beds" style="width:160px;">
<option value="ANY" selected>Any beds</option>
<option value="0">0</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6+</option>
<option value="7">studio</option>
</select></p>
<p>Price from<br />
<select name="price1" id="price1" style="width:160px;">
<option value="ANY" selected>Any price</option>
<option value="10000">10,000</option>
<option value="20000">20,000</option>
<option value="30000">30,000</option>
<option value="40000">40,000</option>
<option value="50000">50,000</option>
<option value="60000">60,000</option>
<option value="70000">70,000</option>
<option value="80000">80,000</option>
<option value="90000">90,000</option>
<option value="100000">100,000</option>
<option value="150000">150,000</option>
<option value="200000">200,000</option>
<option value="250000">250,000</option>
<option value="300000">300,000</option>
<option value="350000">350,000</option>
<option value="400000">400,000</option>
<option value="450000">450,000</option>
<option value="500000">500,000</option>
<option value="550000">550,000</option>
<option value="600000">600,000</option>
<option value="700000">700,000</option>
<option value="800000">800,000</option>
<option value="900000">900,000</option>
<option value="1000000">1,000,000</option>
</select>
</p>
<p>Price to<br />
<select name="price2" id="price2" style="width:160px;">
<option value="ANY" selected>Any price</option>
<option value="10000">10,000</option>
<option value="20000">20,000</option>
<option value="30000">30,000</option>
<option value="40000">40,000</option>
<option value="50000">50,000</option>
<option value="60000">60,000</option>
<option value="70000">70,000</option>
<option value="80000">80,000</option>
<option value="90000">90,000</option>
<option value="100000">100,000</option>
<option value="150000">150,000</option>
<option value="200000">200,000</option>
<option value="250000">250,000</option>
<option value="300000">300,000</option>
<option value="350000">350,000</option>
<option value="400000">400,000</option>
<option value="450000">450,000</option>
<option value="500000">500,000</option>
<option value="550000">550,000</option>
<option value="600000">600,000</option>
<option value="700000">700,000</option>
<option value="800000">800,000</option>
<option value="900000">900,000</option>
<option value="1000000">1,000,000</option>
<option value="1000001">1,000,000+</option>
</select>
</p>
<p>
<input name="submit" type="image" id="submit" src="images/search.jpg" width="64" height="21" border="0" alt="Search" title="Search" /></p></form>
And here is the results page PHP:

Code: Select all

<?php
if (isset($_REQUEST['district'])) {
		if ($_REQUEST['district'] == "ALL") {
			$district = "%";
		} else {
			$district = (get_magic_quotes_gpc()) ? $_REQUEST['district'] : addslashes($_REQUEST['district']);
		}
}

if (isset($_REQUEST['type'])) {
		if ($_REQUEST['type'] == "ALL") {
			$type = "%";
		} else {
  $type = (get_magic_quotes_gpc()) ? $_REQUEST['type'] : addslashes($_REQUEST['type']);
		}
}

if (isset($_REQUEST['beds'])) {
		if ($_REQUEST['beds'] == "ANY") {
			$beds = "%";
		} else {
  $beds = (get_magic_quotes_gpc()) ? $_REQUEST['beds'] : addslashes($_REQUEST['beds']);
		}
}

if (isset($_REQUEST['price1'])) {
		if ($_REQUEST['price1'] == "ANY") {
			$price1 = "0";
		} else {
			$price1 = (get_magic_quotes_gpc()) ? $_REQUEST['price1'] : addslashes($_REQUEST['price1']);
		}
}

if (isset($_REQUEST['price2'])) {
		if ($_REQUEST['price2'] == "ANY") {
			$price2 = "20000000";
		} else {
			$price2 = (get_magic_quotes_gpc()) ? $_REQUEST['price2'] : addslashes($_REQUEST['price2']);
		}
}
?>
<?php
$currentPage = $_SERVER["PHP_SELF"];
?>
<?php
$maxRows_rcsResult = 15;
$pageNum_rcsResult = 0;
if (isset($_GET['pageNum_rcsResult'])) {
  $pageNum_rcsResult = $_GET['pageNum_rcsResult'];
}
$startRow_rcsResult = $pageNum_rcsResult * $maxRows_rcsResult;

mysql_select_db($database_connW, $connW);
$query_rcsResult = "SELECT * FROM realestate WHERE district LIKE '%".$district."%' AND type LIKE '%".$type."%' AND beds LIKE '%".$beds."%' AND price BETWEEN " .$price1." AND ".$price2."";
$query_limit_rcsResult = sprintf("%s LIMIT %d, %d", $query_rcsResult, $startRow_rcsResult, $maxRows_rcsResult);
$rcsResult = mysql_query($query_limit_rcsResult, $connW) or die(mysql_error());
$row_rcsResult = mysql_fetch_assoc($rcsResult);

if (isset($_GET['totalRows_rcsResult'])) {
  $totalRows_rcsResult = $_GET['totalRows_rcsResult'];
} else {
  $all_rcsResult = mysql_query($query_rcsResult);
  $totalRows_rcsResult = mysql_num_rows($all_rcsResult);
}
$totalPages_rcsResult = ceil($totalRows_rcsResult/$maxRows_rcsResult)-1;

$queryString_rcsResult = "";
if (!empty($_SERVER['QUERY_STRING'])) {
  $params = explode("&", $_SERVER['QUERY_STRING']);
  $newParams = array();
  foreach ($params as $param) {
    if (stristr($param, "pageNum_rcsResult") == false && 
        stristr($param, "totalRows_rcsResult") == false) {
      array_push($newParams, $param);
    }
  }
  if (count($newParams) != 0) {
    $queryString_rcsResult = "&" . htmlentities(implode("&", $newParams));
  }
}
$queryString_rcsResult = sprintf("&totalRows_rcsResult=%d%s", $totalRows_rcsResult, $queryString_rcsResult);
?>
The problem now is, there is also some PHP code in the body which generates the navigation for the results - ala "1-15 | 16-30", and so on. Here's the code:

Code: Select all

<?php
for ($i=0; $i <= $totalPages_rcsResult; $i++) {
  $TFM_counter = ($i) * $maxRows_rcsResult + 1;
  $TFM_endCounter = min($totalRows_rcsResult,$TFM_counter + $maxRows_rcsResult - 1);
  if($i != $pageNum_rcsResult) {
    printf('<a href="'."%s?pageNum_rcsResult=%d%s", $currentPage, $i, $queryString_rcsResult.'">');
    echo "$TFM_counter-$TFM_endCounter</a>";
  }else{
    echo "<strong>$TFM_counter-$TFM_endCounter</strong>";
  }
  if($i != $totalPages_rcsResult) echo " | ";
}
?>
However, when I click to see the next batch of results, this error message is displayed in the browser:

Code: Select all

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND LIMIT 15, 15' at line 1

Posted: Fri Oct 27, 2006 8:47 pm
by feyd
remove the "AND"

Posted: Sat Oct 28, 2006 9:53 am
by DevBob
Thanks feyd, but looking at the code, where is the 'AND' I need to remove? The ones I can see are in the SQL query...do you mean here? Or in the results navigation code? Sorry if it sounds like a dumb question!

Posted: Sat Oct 28, 2006 9:59 am
by feyd
The one that's immediately appearing before LIMIT. I would suspect that $price2 is empty/null/false.

Posted: Sat Oct 28, 2006 10:21 am
by DevBob
Gotcha - if you see the PHP code preceeding the sql query, you can see that I've created an IF ELSE statement for each of the form options. In the case of 'price2' this statement assigns a value of '20000000' if 'ANY' is selected, otherwise it uses the value selected, like so:

Code: Select all

if (isset($_REQUEST['price2'])) {
 if ($_REQUEST['price2'] == "ANY") {
 $price2 = "20000000";
 } else {
 $price2 = (get_magic_quotes_gpc()) ? $_REQUEST['price2'] : addslashes($_REQUEST['price2']);
}
I've also echo'd the search values so I can debug the results. If I leave all dropdowns as default (ANY, ALL etc.) the values that are set are: % % % 0 20000000 (district | type | beds | price1 | price2 - in that order). Which means the value of price2 is never empty/null/false. Is this correct, or do you think I've done this wrong?

Many thanks for your help, I really appreciate this.

Posted: Tue Oct 31, 2006 4:40 pm
by DevBob
Ok, this is driving me insane! :) The pagination still doesn't work with the SQL on the results page.

If I hard code the $price1 and $price2 var values, there is no problem with the results or the pagination. But left as is it doesn't work. Yes, I could remove the pagination altogether, but then, this should be simple as is so I am determined to see it fixed.

I know this has something to do with the 2 variables I've mentioned but I don't know how - I tried removing the get_magic_quotes and all that stuff from the 2 variables, but the same error message comes up:

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND LIMIT 15, 15' at line 1"

Sure, I coded the numeric values as strings, but that doesn't seem to matter to the execution of the search script. I also tried not using strings, same error on the pagination link.

Perhaps someone out there can see why this is not working.
Thanks.