Returning Specific Results after Search

SparkleD · August 30, 2007

Hi All,

I'm new to PHP. I have a properties database (MYSQL). When I click on a specific link, the results show all the properties in the database, however what I need the results to show is not all the properties but ones that meet a certain criteria. I want it to show the ones that are listed as "Rentals" only.

Any help is appreciated.

GingerRobot · August 30, 2007

Well, without any code or database structure, its going to be hard to help you. However, if i assume that you have a field in your table(which im going to assume is called properties) called 'type', then its a relatively simply case of modifying your query to something along the lines of:

$sql = "SELECT * FROM properties WHERE type='rentals'";
$result = mysql_query($sql) or die(mysql_error());

SparkleD · August 30, 2007

sorry about that (and yes there is a property type field)...here is the code:

### DEBUG

$debugP = 0 ;

### END DEBUG

#Fetch database connection

require_once('Connections/myconn.php');

mysql_select_db($database_myconn,$myconn);

require_once('myfunctions.php');

$mysql = new mysql();

$mysql->conn = $myconn;

if($_REQUEST['types'] != ""){

$sql = "select * from `types` where types.stitle = '".mysql_real_escape_string($_REQUEST['rentals'],$myconn)."'";

$result = mysql_query($sql,$myconn) or die(mysql_error());

if(mysql_num_rows($result)>0){

$stateInfo = mysql_fetch_assoc($result);

$siteTitle = sprintf("%s Houses for Sale",$stateInfo['title']);

$siteDescription = sprintf("Real Estate for sale in %s.",$stateInfo['title']);

$siteKeywords = sprintf("%s real estate, home, homes, property, agent",$stateInfo['title']);

}

#Fetch Header

include('header.php');

#### BUILD SEARCH SQL BASED ON SEARCH TYPE ####

#defauts

$maxRows_p = 10;

$pageNum_p = 0;

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

$pageNum_p = $_GET['pageNum_p'];

}

$startRow_p = $pageNum_p * $maxRows_p;

$limit = ' LIMIT '.$startRow_p.', '.$maxRows_p;

if($_REQUEST['zip'] == '')

$zip = '-1';

else

$zip = $_REQUEST['zip'];

//Zip code radius search

if(isset($_REQUEST['fromZip']) && ($_REQUEST['fromZip'] != '')){

require_once('xmlparse.ini.php');

$str = "?q=".urlencode($_REQUEST['zip'].', USA');

$str.= "&key=".$map_key;

$str.= "&output=xml";

$result = XMLRPC_request('maps.google.com', '/maps/geo', 'geocode', $str );

$cord = $result['kml']['Response']['Placemark']['Point']['coordinates'];

// var_dump($cord);

if(strlen($cord) > 0){

$temp = @explode(',',$cord);

$address['long'] = $temp[0];

$address['lat'] = $temp[1];

}

if($address['lat'] != ""){

$showMap = true;

//Fetch assoc array of results

$zipInfo['LATITUDE'] = $address['lat'];

$zipInfo['LONGITUDE'] = $address['long'];

//Use lat and long found in query to build new sql to search properties

$sql = '(SIN(RADIANS('.$zipInfo['LATITUDE'].')) * SIN(RADIANS(`lat`)) + COS(RADIANS('.$zipInfo['LATITUDE'].')) * COS(RADIANS(`lat`)) * COS(RADIANS((('.$zipInfo['LONGITUDE'].') - (`long`)))))';

$sql = 'ACOS'.$sql;

$sql = 'DEGREES('.$sql.')';

$sql = '('.$sql.' * 60 * 1.1515)';

//copy current sql to $whereMiles

$whereMiles = $sql;

//continue to build sql

$sql = 'SELECT *,'.$sql;

$sql = $sql . ' as `Miles` ';

$sql = $sql . 'FROM items WHERE (`expires` > NOW()) and active = "Yes" ';

// $sql = $sql . ' and ( state = "'.$zipInfo['STATE'].'" )';

$sql = $sql . ' and (('.$whereMiles.' <= '.intval($_REQUEST['fromZip']).') OR (zip LIKE '.intval($_REQUEST['zip']).'))';

//BUILD Addition Search Conditions

//Add Price From

if(isset($_REQUEST['pfrom']) && ($_REQUEST['pfrom'] != '-1'))

$search = ' and price >= '.$_REQUEST['pfrom'];

//Add Max Price

if(isset($_REQUEST['pto']) && ($_REQUEST['pto'] != '-1'))

$search .= ' and price <= '.$_REQUEST['pto'];

//Add Min Bed

if($_REQUEST['bed'] != "")

$search .= ' and bed >= "'.$_REQUEST['bed'].'"';

//Add Min Bath

if($_REQUEST['bath'] != "")

$search .= ' and bath >= "'.$_REQUEST['bath'].'"';

//Add Search Sql to zip radius search

$sql .= $search;

## DEBUG

if($debugP) echo 'Zip Code Radius SQL<hr>'.$sql;

//Add column sorting

if($_REQUEST['sort'] != '')

$sort = ' order by miles asc ';

else

$sort = $_REQUEST['sort'];

$error['Search'] = 'Sorry no properties found within distance selected.';

}

//Else zip code not found

else {

if($debugP) echo 'Zip Code Not Found<hr>'.$sql;

//Push error message on array

$error['Zip'] = 'Sorry zip code was not found.';

}

}//End of if GET fromZip

//Advanced Search is requested

else{

## Start building sql for GET varables for advanced search

//Add Property ID

if(isset($_REQUEST['id']) && ($_REQUEST['id'] != ''))

$search[] = ' id = '.$_REQUEST['id'];

//Add Price From

if(isset($_REQUEST['pfrom']) && ($_REQUEST['pfrom'] != '-1'))

$search[] = ' price >= '.$_REQUEST['pfrom'];

//Add Max Price

if(isset($_REQUEST['pto']) && ($_REQUEST['pto'] != '-1'))

$search[] = ' price <= '.$_REQUEST['pto'];

//Add Property Type

if(isset($_REQUEST['category']) && ($_REQUEST['category'] != ''))

$search[] = ' cid = '.$_REQUEST['category'];

//Add Property Style Type

if(isset($_REQUEST['style']) && ($_REQUEST['style'] != ''))

$search[] = ' styleID = '.$_REQUEST['style'];

//Add Min Bed

if(isset($_REQUEST['bed']) && ($_REQUEST['bed'] != ''))

$search[] =' bed >= "'.$_REQUEST['bed'].'"';

//Add Min Bath

if(isset($_REQUEST['bath']) && ($_REQUEST['bath'] != ''))

$search[] = ' bath >= "'.$_REQUEST['bath'].'"';

//Add city

if(isset($_REQUEST['city']) && ($_REQUEST['city'] != ''))

$search[] = ' city = "'.$_REQUEST['city'].'"';

//Add State

if(isset($_REQUEST['state']) && ($_REQUEST['state'] != ''))

$search[] = ' state = "'.$_REQUEST['state'].'"';

//Add min square feet

if(isset($_REQUEST['sqft']) && ($_REQUEST['sqft'] != ''))

$search[] = ' sqft >= '.$_REQUEST['sqft'];

//Add Garage

if(isset($_REQUEST['garage']) && ($_REQUEST['garage'] != ''))

$search[] = ' garage = "'.$_REQUEST['garage'].'"';

//Add lot size

if(isset($_REQUEST['lot_size']) && ($_REQUEST['lot_size'] != ''))

$search[] = ' lot_size >= '.$_REQUEST['lot_size'];

//Add County ID

if(isset($_REQUEST['countyID']) && ($_REQUEST['countyID'] != ''))

$search[] = ' countyID = '.$_REQUEST['countyID'];

//Add Keyword Search

if(isset($_REQUEST['keyword']) && ($_REQUEST['keyword'] != ''))

$search[] = ' `description` LIKE "%'.$_REQUEST['keyword'].'%" ';

//Add Zip Search

if(isset($_REQUEST['zip']) && ($_REQUEST['zip'] != ""))

$search[] = ' `zip` = "'.$_REQUEST['zip'].'" ';

$search[] = ' active = "Yes" ';

//implode to search string on ' and ';

$searchStr = @implode(' and ',$search);

$sql = 'select * FROM items WHERE (expires > NOW()) and ';

$sql .= $searchStr;

//Add column sorting

if($_REQUEST['sort'] != '')

$sort = ' order by price asc ';

else

$sort = $_REQUEST['sort'];

### DEBUG

if($debugP) echo 'Advanced Search Sql<hr>'.$sql;

$error['Results'] = 'No results found, please search again';

}

### Finished Building search sql and execting #####

$sql_with_limit = $sql . $sort . $limit;

if($debugP)

echo "<hr>Property Search with Limit SQL: $sql_with_limit";

//Perform search

$searchResults = $mysql->exSql($sql_with_limit);

### BUILD OUTPUT ####

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

$totalRows_p = $_GET['totalRows_p'];

} else {

if($debugP)

echo "<hr>Property with out limit SQL: $sql $sort";

$all_p = mysql_query($sql.$sort);

if($all_p)

$totalRows_p = mysql_num_rows($all_p);

else

$totalRow_p = 0;

if($debugP)

echo "<br>Result Rows $totalRows_p";

}

$totalPages_p = ceil($totalRows_p/$maxRows_p)-1;

if($debugP)

echo "<hr>Builting Query String for Limit: ";

//Build query string

foreach($_GET as $name => $value){

if($name != "pageNum_p")

$queryString_p .= "&$name=$value";

}

if($debugP)

echo $queryString_p;

?>

</font>

<tr>

<td>

<font color="#FFFFFF">Home</font></a><font color="#FFFFFF"> <span class="locArrow"> > </span> Search Results</font></font></div>

</td>

<td>

</td>

</tr>

</table>

<?

if( $showMap && (mysql_num_rows($searchResults)>0) ){

echo "<iframe src='mapping/sr_mapping.php?zip=".intval($_REQUEST['zip'])."' width='100%' scrolling='no' frameborder='0' height='350px'></iframe>";

echo '<hr size="1" color="#666666">';

}

?>

</font>

<tr>

<?php if ($pageNum_p > 0) { // Show if not first page ?>

</font>

<a href="<?php printf("%s?pageNum_p=%d%s", $currentPage, 0, $queryString_p); ?>" class="pageLink">

<font color="#6699FF">First</font></a><font color="#6699FF"> |

<?php } // Show if not first page ?>

<?php if ($pageNum_p > 0) { // Show if not first page ?>

</font>

<a href="<?php printf("%s?pageNum_p=%d%s", $currentPage, max(0, $pageNum_p - 1), $queryString_p); ?>" class="pageLink">

<font color="#6699FF">Previous</font></a><font color="#6699FF"> |

<?php } // Show if not first page ?>

<?php if ($pageNum_p < $totalPages_p) { // Show if not last page ?>

</font>

<a href="<?php printf("%s?pageNum_p=%d%s", $currentPage, min($totalPages_p, $pageNum_p + 1), $queryString_p); ?>" class="pageLink">

<font color="#6699FF">Next</font></a><font color="#6699FF"> |

<?php } // Show if not last page ?>

<?php if ($pageNum_p < $totalPages_p) { // Show if not last page ?>

</font>

<a href="<?php printf("%s?pageNum_p=%d%s", $currentPage, $totalPages_p, $queryString_p); ?>" class="pageLink">

<?php } // Show if not last page ?>

</font></font>

</td>

</tr>

</table>

<tr>

<tr>

<td class="pageText" ><font color="#ffffff" face="Verdana" size="2">Showing: <strong><?php echo ($startRow_p + 1) ?> to <?php echo min($startRow_p + $maxRows_p, $totalRows_p) ?> of <?php echo $totalRows_p ?></strong> Listings</font></td>

</tr>

</table></td>

</tr>

<tr>

</tr>

<tr>

<tr>

<td class="colText"><font face="Verdana" size="2" color="#000000">Address</font></td>

<?

if(isset($_REQUEST['fromZip']) && ($_REQUEST['fromZip'] != ''))

print '<td class="colText">Miles</td>';

?>

<td class="colText"><font face="Verdana" size="2" color="#000000">Price</font></td>

<td class="colText"><font face="Verdana" size="2" color="#000000">Baths</font></td>

</tr>

<? while($row_p = @mysql_fetch_assoc($searchResults)) { ?>

<td class="rowText" style="color: #000000" bgcolor="#063060"><a href="detail.php?id=<? echo $row_p['id']; ?>" class="rowLinkBold" >

<?

if(isset($_REQUEST['fromZip']) && ($_REQUEST['fromZip'] != ''))

print '<td class="rowText">'.number_format($row_p['Miles'],2,'.',',').'</td>';

?>

</tr>

<? //fetch photo from database

$sql = "select * from photos where ptid = ". $row_p['id']." and porder = '1' ";

//echo $sql; //debug sql output

$pRS = mysql_query($sql,$myconn) or die(mysql_error());

//fetch assoc array

$row_photo = mysql_fetch_assoc($pRS);

// if image exist

if ( mysql_num_rows($pRS) > 0 )

$imageSRC = $row_photo['location'];

else //no photo

$imageSRC = "noimage.jpg";

?>

<td class="descText"><a title="<? echo $row_photo['caption']; ?>" href="detail.php?id=<? echo $row_p['id']; ?>">

<font color="#000000" face="Verdana" size="2"><img src="admin/photos/uploads/small_thumbs/tn_<? echo $imageSRC; ?>" border="0" alt="<? echo $row_photo['caption']; ?>"></font></a></td>

<font color="#000000"><? echo substr($row_p['description'],0,150); ?>...</font><a href="detail.php?id=<? echo $row_p['id']; ?>" class="rowLink"><font color="#000000">more..</font></a><a class="rowLink" href="saveListing.php?id=<? echo $row_p['id']; ?>" title="Click to save listing"><font color="#fe401c"><br>

save listing</font></a></font></td>

</tr>

</table></td>

</tr>

<? } ?>

</table></td>

</tr>

<tr>

</tr>

<tr>

<tr>

</tr>

</table></td>

</tr>

</table>

<tr>

<td align="center"><font face="Verdana" size="2"><font color="#6699FF"><?php if ($pageNum_p > 0) { // Show if not first page ?>

</font>

<a href="<?php printf("%s?pageNum_p=%d%s", $currentPage, 0, $queryString_p); ?>" class="pageLink">

<font color="#6699FF">First</font></a><font color="#6699FF"> |

<?php } // Show if not first page ?>

<?php if ($pageNum_p > 0) { // Show if not first page ?>

</font>

<a href="<?php printf("%s?pageNum_p=%d%s", $currentPage, max(0, $pageNum_p - 1), $queryString_p); ?>" class="pageLink">

<font color="#6699FF">Previous</font></a><font color="#6699FF"> |

<?php } // Show if not first page ?>

<?php if ($pageNum_p < $totalPages_p) { // Show if not last page ?>

</font>

<a href="<?php printf("%s?pageNum_p=%d%s", $currentPage, min($totalPages_p, $pageNum_p + 1), $queryString_p); ?>" class="pageLink">

<font color="#6699FF">Next</font></a><font color="#6699FF"> |

<?php } // Show if not last page ?>

<?php if ($pageNum_p < $totalPages_p) { // Show if not last page ?>

</font>

<a href="<?php printf("%s?pageNum_p=%d%s", $currentPage, $totalPages_p, $queryString_p); ?>" class="pageLink">

<?php } // Show if not last page ?>

</font></font>

</td>

</tr>

</table>

<?

## if no results where found

if(@mysql_num_rows($searchResults)<=0){

foreach($error as $name => $value)

print '<div align=center class="error">'.$name . ': ' . $value.'</div>';

}

##Fetch Footer

include('footer.php');

?>

document.getElementById('loading').style.display = 'none';

</script>

Sign In

Returning Specific Results after Search

Recommended Posts

SparkleD

Link to comment

Share on other sites

GingerRobot

Link to comment

Share on other sites

SparkleD

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information