showing records between a selected min & max price.

[ebanbury]

Hi
I'm having problems with a query bringing back records where the price is between 2 different min and max options.
My field in the db = prop_price and it is currently an integer (as I thought part of the problem might be the comma in the prices)

I've pasted here the 2 form fields - prop_pricemin and prop_pricemax.

<td align="left"><label for="prop_pricemin"></label>
                    <select name="prop_pricemin" id="prop_pricemin">
                       <option value="<?php echo $_POST['prop_pricemin']; ?>" selected="selected"><?php echo $_POST['prop_pricemin']; ?></option>
                      <option value="Any">Any</option>
                      <option value="100000">100000</option>
                      <option value="500000">500000</option>
                      <option value="1000000">1000000</option>
                      <option value="1500000">1500000</option>
                      <option value="1500000">1500000</option>
                    </select>                  
                    </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td align="left"><label for="prop_pricemax"></label>
                    <select name="prop_pricemax" id="prop_pricemax">
                      <option value="<?php echo $_POST['prop_pricemax']; ?>" selected="selected"><?php echo $_POST['prop_pricemax']; ?></option>
                      <option value="Any">Any</option>
                      <option value="1000000">1000000</option>
                      <option value="1500000">1500000</option>
                      <option value="2000000">2000000</option>
                      <option value="3000000">3000000</option>
                      <option value="3000000+">3000000+</option>
                    </select>

This is what I have in my sql query :

WHERE property_details.prop_price <= '$_POST[prop_pricemin]'
AND property_details.prop_price >= '$_POST[prop_pricemax]'

I feel reasonably sure about the query and something is telling me that it doesn't like the way I have the prices in the database?

I would also really like the prices to have the comma but dont'know if that is stopping the math function working?

Any help would be great
Many thanks

[blocblue]

Hi

WHERE property_details.prop_price <= '$_POST[prop_pricemin]'
AND property_details.prop_price >= '$_POST[prop_pricemax]'

You have you min and max parameters in the wrong way around:

WHERE property_details.prop_price <= 'prop_pricemax'
AND property_details.prop_price >= 'prop_pricemin'

Also, remember to escape anything coming from the browser before using in your SQL queries to avoid SQL injection attacks.

$sql = sprintf("... WHERE property_details.prop_price >= %d AND property_details.prop_price <= %d", (int)$_POST['prop_pricemin'], (int)$_POST['prop_pricemax']);

R.

[ebanbury]

Hi R
Many thanks for getting back to me. I really do appreciate the help.

I still think I'm missing something (please see full query below), as you can see I probably have a bracket missing somewhere as I'm getting a line error where I put your code...

But I have a couple more questions if you don't mind. I'm a bit unsure about the database side (mysql db) My prop_price field is currently an int and I put the price records in without any commas - i.e. 100000 instead of 100,000.

Of course I would like to put in the prices directly to the database with the commas already in. Is it best to change the field to a varchar?

Secondly would it be easier if the user just put in a min price and max price into a textfield rather than selecting from an options list?

many thanks

if ($_POST[district_zone] == "All") { $lcl_district_zone = "%"; } else { $lcl_district_zone = $_POST[district_zone]; } 
if ($_POST[prop_bedroom] == "Any") { $lcl_prop_bedroom = "%"; } else { $lcl_prop_bedroom = $_POST[prop_bedroom]; } 
if ($_POST[prop_bathroom] == "Any") { $lcl_prop_bathroom = "%"; } else { $lcl_prop_bathroom = $_POST[prop_bathroom]; } 
$query_rs_search = sprintf(
"SELECT 
property_details.prop_id,  
property_details.add_house, 
property_details.prop_saletype,  
property_details.prop_bedroom,  
property_details.prop_bathroom,  
property_details.prop_type,  
property_details.prop_cat,  
property_details.prop_price,
address_images.image_main,  
property_districts.district_zone 
FROM property_districts, property_details, address_images 
WHERE property_districts.district_zone = property_details.add_zone 
AND property_details.prop_id = address_images.prop_id  
AND property_details.prop_cat = 'Sell'

AND property_details.prop_price >= %d AND property_details.prop_price <= %d", (int) $_POST['prop_pricemin'], (int) $_POST['prop_pricemax']

AND property_details.prop_bedroom LIKE '$lcl_prop_bedroom'  
AND property_details.prop_bathroom LIKE '$lcl_prop_bathroom'    
AND property_districts.district_zone LIKE '$lcl_district_zone' 
AND
(
(property_details.prop_type = '$_POST[prop_type]'  AND  property_details.prop_saletype = '$_POST[prop_saletype1]' )  
OR (property_details.prop_type = '$_POST[prop_type]'  AND  property_details.prop_saletype = '$_POST[prop_saletype2]' )  
OR (property_details.prop_type = '$_POST[prop_type]'  AND  property_details.prop_saletype = '$_POST[prop_saletype3]' )  
OR (property_details.prop_type = '$_POST[prop_type]'  AND  property_details.prop_saletype = '$_POST[prop_saletype4]' )  
OR (property_details.prop_type = '$_POST[prop_type]'  AND  property_details.prop_saletype = '$_POST[prop_saletype5]' )
)
" );

[blocblue]

Hi

Parameters need to be passed to the sprintf function in the order they're declared. Hence your code should read:

$query_rs_search = sprintf(
"SELECT 
property_details.prop_id,  
property_details.add_house, 
property_details.prop_saletype,  
property_details.prop_bedroom,  
property_details.prop_bathroom,  
property_details.prop_type,  
property_details.prop_cat,  
property_details.prop_price,
address_images.image_main,  
property_districts.district_zone 
FROM property_districts, property_details, address_images 
WHERE property_districts.district_zone = property_details.add_zone 
AND property_details.prop_id = address_images.prop_id  
AND property_details.prop_cat = 'Sell'

AND property_details.prop_price >= %d AND property_details.prop_price <= %d

AND property_details.prop_bedroom LIKE '$lcl_prop_bedroom'  
AND property_details.prop_bathroom LIKE '$lcl_prop_bathroom'    
AND property_districts.district_zone LIKE '$lcl_district_zone' 
AND
(
(property_details.prop_type = '$_POST[prop_type]'  AND  property_details.prop_saletype = '$_POST[prop_saletype1]' )  
OR (property_details.prop_type = '$_POST[prop_type]'  AND  property_details.prop_saletype = '$_POST[prop_saletype2]' )  
OR (property_details.prop_type = '$_POST[prop_type]'  AND  property_details.prop_saletype = '$_POST[prop_saletype3]' )  
OR (property_details.prop_type = '$_POST[prop_type]'  AND  property_details.prop_saletype = '$_POST[prop_saletype4]' )  
OR (property_details.prop_type = '$_POST[prop_type]'  AND  property_details.prop_saletype = '$_POST[prop_saletype5]' )
)
", (int) $_POST['prop_pricemin'], (int) $_POST['prop_pricemax']);

You could move all variables out of the SQL statement, replace them with type specific placeholders and then bind the parameters afterwards to avoid SQL injection attacks.

R.

[ebanbury]

Hi thanks for this.
I'm not getting any syntax errors now, but when I run the query I get the following message:

Warning: sprintf() [function.sprintf]: Too few arguments in /home/iwalletc/public_html/realestate/propertylisting_sell.php on line 123
Query was empty

What is sql injection? I have never heard of this? Is this something that is caused by users or by the code doing something to itself by itself?

many thanks for the help as always...

[blocblue]

In that case remove the sprintf and just use:

AND property_details.prop_price >= $_POST['prop_pricemin'] AND property_details.prop_price <= $_POST['prop_pricemax']

This will however, like the rest of your query, leave you site vulnerable to hacking.

SQL injection is where a user executes SQL queries on your database through your website because you're not escaping variables retrieved from the browser: $_GET, $_POST, $_REQUEST, $_COOKIE.

Never trust input from the user and you will not get burned. For more info, refer to Google. The subject is too exhaustive to explain in a forum post.

R.

[ebanbury]

Yes no problem. I will go and look it up.
Many thanks for all the help! It has been great. L