Advanced search help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
DevBob
Forum Newbie
Posts: 7
Joined: Fri Oct 27, 2006 4:15 am

Advanced search help

Post 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]
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

What does it do? What is it supposed to do? Please be more specific.
Rovas
Forum Contributor
Posts: 272
Joined: Mon Aug 21, 2006 7:09 am
Location: Romania

Post 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.
DevBob
Forum Newbie
Posts: 7
Joined: Fri Oct 27, 2006 4:15 am

Post 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
DevBob
Forum Newbie
Posts: 7
Joined: Fri Oct 27, 2006 4:15 am

Post by DevBob »

My apologies feyd.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
DevBob
Forum Newbie
Posts: 7
Joined: Fri Oct 27, 2006 4:15 am

Almost there, but for one problem...

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

remove the "AND"
DevBob
Forum Newbie
Posts: 7
Joined: Fri Oct 27, 2006 4:15 am

Post 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!
Last edited by DevBob on Sat Oct 28, 2006 7:10 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The one that's immediately appearing before LIMIT. I would suspect that $price2 is empty/null/false.
DevBob
Forum Newbie
Posts: 7
Joined: Fri Oct 27, 2006 4:15 am

Post 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.
DevBob
Forum Newbie
Posts: 7
Joined: Fri Oct 27, 2006 4:15 am

Post 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.
Post Reply