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

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.

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]' )
)
" );

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.

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

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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.