List Menu, retrieve multiple selections

[tonynoriega]

dont know if this is the right forum area.....

anyway, i have a registration form that my agents enter in leads. they can also retrieve and modify the data of the registrant...it has about 50 fields.

I want to use a List Menu for what "price range" of homes they are looking for.

For instance, 100k-125k and 200k-250k.

It submits to the table fine, i can see both entries, but when i retrieve the record, only 200k-250k comes back into the list menu...

is is even possible to have it populate both selections when i retrieve the record?

i hope that made sense...

[rocknbil]

<select name="price_range">
<option value="0:125,000">Under 125k</option>
<option value="125,000:250,000">125-250k</option>
<option value="250,000:300,000">125-250k</option>
</select>

Then you split the value of price range on ":"

($low,$high) = split(/:/,$price_range);

select * from records where price >= '$low' and price <= '$high' order by price asc;

If you're doing a select multiple, it's the same concept, you just need an or:

select * from records where (price >= '$low1' and price <= '$high1') or (price >= '$low2' and price <= '$high2') order by price asc;

[tonynoriega]

Ok, so originally i had this:

<select name="price_range" id="price_range"><option value="N/A" selected>N/A</option><option value="under 150k">Below $150k</option><option value="150k-175k">$150k-$175k</option>

which should now be this for instance:

<option value="150k:175k">$150k-$175k</option>

Can i leave the 'k' or should it actually become 150,000:175000?

Then i originally had the fields re-populated basically calling the ID number, and selecting all fields:

if ($rec_id = $_GET['rec_id']){
$result=mysql_query("SELECT * FROM registration_table WHERE rec_id=$rec_id");
$num=mysql_num_rows($result);
$i=0;
while ($i < $num) {
//more fields here
$price_range=mysql_result($result,$i,"price_range");
//more fields here

So, im not sure how this section would change according to your recommendations...? :(

if that makes sense...

[rocknbil]

Well, it depends on how you store the data in the database. I'm guessing you don't store "price range" as "125k." "K" is not an automatic indicator of "hundred thousand."

It looks like you're making a query then grabbing record ID based on that query's results. You should be able to do it with one query.

$result=mysql_query("SELECT * FROM registration_table WHERE (price_range >= '$low') and (price_range >= '$high');");

[tonynoriega]

ok, so i will store the values as actual hundred thousands, no problem..... the K will be for the user to see.

Now what if i also had say, a multiple selection set up for:

Beds

values = 1,2,3,4,5

and say they select 3 values, (3,4,5) because the lead was interested in all homes with 3,4, or 5 bedrooms....

[rocknbil]

Well, in this case you would want some form of multiple selection in the form, but with my experience in real estate sites they are most likely going to pick a range or a set number for bedrooms, so a radio button will work. When you do your select, build it accordingly. Since this creates an increasingly complex select, I suggest you start building the WHERE and select statements as variables. My code below is specific to perl, you will need to convert it for PHP.

## Price? 
if ($qs{'price_range')!= '') { 
 ## If you have previously added to the where, you will need an and 
 if ($where) { $where .= ' and'; } 
 ($low,$high) = split (/:/,$qs{'price_range'}); 
 $where .= qq¦(price_range >= '$low') and (price_range >= '$high')¦; 
} 

## Bedrooms? This presumes you have named the radio buttons as: 
## <input type="radio" name="bedrooms" value="1"> 
## Make sure you create one radio with the value "any" 
if ($qs{'bedrooms'} ne 'any') { 
 if ($where) { $where .= ' and'; } 
 $where .= qq¦ bedrooms = '$qs{'bedrooms'}'¦; 
} 

## Now put it all together 
$select = "select * from registration_table"; 
if ($where) { $select .= " where $where"; } 
$result=mysql_query("$select;");  

If the values are selected, what this should give you is

select * from registration_table where (price_range >= '$low') and (price_range >= '$high') and bedrooms='$qs{'bedrooms'}';

NOTE: although it APPEARS all data is directly input from the form, this data must be cleansed on input to prevent mysql injection.

[tonynoriega]

Well this is in a secure portion of my site so the public does not have access to it... (unless hacked)...

in any case, this form is for employees only, once they come in contact with a lead, they use this form to enter the criteria....

thank you i will work with that...