Php realestate script

[michellephp]

Hi everyone,

I am having troubles with a realestate script.

<EDIT - cut out, see below>

I have used a modified a search page to display a drop down box containing a list of suburbs (only suburbs that have properties in them). But then I realised if there are two properties in one suburb, the suburb name will come up twice in the list (I want them to click on the suburb, and then be taken to the results page with the two properties).

See:

## Start building sql for GET varables for advanced search


//Add city

if(isset($_REQUEST['city']) && ($_REQUEST['city'] != ''))
$search[] = ' city = "'.$_REQUEST['city'].'"';
//Add county ID
if( isset($_REQUEST['countyID']) && ($_REQUEST['countyID'] != '') )
$search[] = ' countyID = '.$_REQUEST['countyID'].' ';

//implode to search string on ' and ';
$searchStr = @implode(' and ',$search);

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




}


### Finished Building search sql and execting #####
$sql .= $sort . $limit;

//Perform search
$searchResults = $mysql->exSql($sql);

### BUILD OUTPUT ####


?>


AND in BODY:

<table width="100%" border="0" cellspacing="0" cellpadding="1">
<tr>
<td class="searchBoxText" align="right">City:&nbsp;</td>
<td>
<table border="0" cellspacing="0" cellpadding="0">

<form action="properties.php" method="get" name="search" style="margin-bottom: 0;"><select name="city" id="city"><? while($psOptions = @mysql_fetch_assoc($searchResults)) { ?>
<option value="<? echo $psOptions['city']; ?>" <? if($row_city['title'] == $row_item['city']) echo 'selected'; ?>><? echo $psOptions['city']; ?></option><? echo $psOptions['city']; ?>
<?
}
?>
</td>
</tr>
</table>

Is there any way to stop the suburbs displaying more than once?

I am not sure if I am allowed to post the url of this page, but if you would like to see it please let me know :)

<edit>I have done a search, and I think what i want has to do with 'Group By' Modifiers ... is that right? Any hints?

<edit>I added

$sql = 'SELECT * FROM items GROUP BY `city`';

But now it is listing all the cities in the db, not just the search results. Am I getting closer?

<edit>So I changed:
$sql = 'select * FROM items WHERE (expires > NOW()) and active = "Yes" and ';
$sql .= $searchStr;

to:

$sql = 'select * FROM items WHERE (expires > NOW()) and active = "Yes" and GROUP BY city';
$sql .= $searchStr;

But now I just get an empty drop down box. I really can't seem to work it out.


Thank-you!

[shortj75]

try takeing the value out of the while loop and see if that helps
[code]
<form action="properties.php" method="get" name="search" style="margin-bottom: 0;"><select name="city" id="city"><? $psOptions = @mysql_fetch_assoc($searchResults);  ?>
<option value="<? echo $psOptions['city']; ?>" <? if($row_city['title'] == $row_item['city']) echo 'selected'; ?>><? echo $psOptions['city']; ?></option><? echo $psOptions['city']; ?>

[/code]
see if that helps and yes you are allowed to post the url to the page

[michellephp]

Thanks for the suggestion :)

But unfortunately it didn't work :( AFter changing what you said, the drop down box was still empty. And if I took out the "GROUP BY city" the drop down box then only contained one city.

For some reason it just doesn't seem to like the GROUP BY city
Would there be something else in their contradicting the GROUP BY?

[khendar]

What you need to do is a DISTINCT selection so that the cities are only listed once. It looks like you are only using the `city` field from the table...ie :

$sql = 'select distinct city FROM items WHERE (expires > NOW()) and active = "Yes" and ';

Then when you execute the query you should get each city only once. Then you can use your while loop to insert the <option> elements

Incidentally I noticed you are not closing the <form> tags either.

[michellephp]

Thanks so much for that! It is finally working :) :) I've spend 5 hrs playing around with that one little thing, so really thank-you!