unable to SELECT db
row from price between entered field, i tried with below code when i entered price range for 5000 to 8800
it selects all db
row from price with first no.5 and 8.
can some one explain whats wrong or do i need any change in code.
SQL
`prs` varchar(255) NOT NULL DEFAULT '',
HTML
<input type="text" id="minimum_price" class="form-control"> <input type="text" id="maximum_price" class="form-control"> <input type="submit" class="filter_all"> $(document).ready(function () { filter_data(); function filter_data() { $('.filter_data'); var action = 'fetch_data'; var minimum_price = $('#minimum_price').val(); var maximum_price = $('#maximum_price').val(); $.ajax({ url: "fetch.php", method: "POST", data: { action: action, minimum_price: minimum_price, maximum_price: maximum_price }, success: function (data) { $('.filter_data').html(data); } }); } function get_filter(class_name) { var filter = []; $('.' + class_name + ':checked').each(function () { filter.push($(this).val()); }); return filter; } $('.filter_all').click(function () { filter_data(); }); });
PHP
if (isset($_POST["action"])) { $query = "SELECT * FROM allpostdata WHERE sts = '1' "; if (isset($_POST["minimum_price"], $_POST["maximum_price"]) && !empty($_POST["minimum_price"]) && !empty($_POST["maximum_price"])) { $query .= " AND prs BETWEEN '" . $_POST["minimum_price"] . "' AND '" . $_POST["maximum_price"] . "' "; }
Result
Advertisement
Answer
Your column prs
is a varchar
column (i.e. it stores text), and is not a numeric column (e.g. int
or money
). So the database software is performing a lexicographical comparison, not a numeric ordering.
- Change your database design to store money/currency values using
money
or asint
cents/pennies.- Do not use floating-point types (like
real
,float
,double
, etc) to store monetary values because they are imprecise (read up on how IEEE-754 works).
- Do not use floating-point types (like
Also, as stated, your code is wide-open to SQL Injection attacks. Read here: What is SQL injection? and How does the SQL injection from the “Bobby Tables” XKCD comic work?
Also, I recommend avoiding the
x BETWEEN y AND z
operator because of these reasons:- The meaning of
AND
inBETWEEN
is different to theAND
logical operator which means you need to carefully format your code so it’s readable so people don’t confuse the semantics ofAND
, especially when using a non-trivial expression fory
orz
. - It’s not immediately obvious if the
BETWEEN
operator is using inclusive or exclusive bounds. - If any of the 3 values can be
NULL
then you need to use separateIS NULL
comparisons anyway, which results in a hideous query. - So always using explicit
y <= x AND x < z
is almost always a better idea IMO.
- The meaning of