CODEWITHSUNDEEP
phpsql
sanoj lawrence
sanoj lawrence

Reputation: 993

SQL BETWEEN doesn't SELECT given price range from 2 input filed

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

enter image description here

Upvotes: 0

Views: 50

Answers (1)

Dai
Dai

Reputation: 155145

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 as int 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).

  • 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 in BETWEEN is different to the AND logical operator which means you need to carefully format your code so it's readable so people don't confuse the semantics of AND, especially when using a non-trivial expression for y or z.
    • 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 separate IS 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.

Upvotes: 1

Related Questions